SQL查询，连接两个表

Question

我需要连接来自两个不同表的数据，如下所示。仅使用一个sql查询就可以做到这一点吗？如果“key”和“name”在两个表上，那么它们是相同的。表1中的“状态”始终为“-”，表2中的“T”。此外，两个表上的“名称”和“comp”都匹配（例如：Name3-C和Name4-B）<我尝试过“联合”和“加入”，但还没有解决办法！

表1

t1.name     t1.time   t1.comp   t1.key   t1.status
name1        1          B         106        -
name2        2          B         -          -
name3        1          C         102        -
name4        3          B         103        -
name7        1          C         104        -

表2

t2.name     t2.time   t2.comp   t2.key    t2.status
name5        6          B         100        T
name6        5          B         -          T
name3        7          C         102        T
name4        9          B         103        T

结果应该是。。。

name      time1    time2   t.comp   t.key   t.status
name1        1       -       B       106       -  
name2        2       -       B        -        -
name3        1       7       C       102       T
name4        3       9       B       103       T
name5        -       6       C       100       T
name6        -       5       B        -        T
name7        1       -       C       104       -

提前谢谢！

Answer 1

SELECT 
  T1.time AS Time1,
  T2.Time AS Time2,
  ISNULL(T1.COMP,T2.Comp) AS Comp,
  ISNULL(T1.Key,T2.Key)
FROM TABLE1 T1
FULL OUTER JOIN TABLE2 T2
ON T1.key = T2.key

Answer 2

Select * from table1
union all 
Select * from table2

假设表的模式相同。如果它们不只是将字段从一个表添加到另一个表。

Select field1,field2,NULL,NULL from table1
union all 
Select NULL,NULL,field3,field4 from table2

Answer 3

如果我理解正确，您需要一个完整的外部连接：

select coalesce(t1.name, t2.name) as name,
       t1.time as time1, t2.time as time2,
       coalesce(t1.comp, t2.comp) as comp,
       coalesce(t1.key, t2.key) as key,
       t2.status
from table1 t1 full outer join
     table2 t2
     on t1.key = t2.key and t1.name = t2.name;