合并两个具有一个或多个附加列的SELECT语句

I have two select statements like below

我有两个选择语句,如下所示

SELECT * FROM TABLE1

gives me

COLUMNA, COLUMNB, COLUMNC

and

SELECT * FROM TABLE2

gives me

COLUMNA, COLUMNB, COLUMND

COLUMNA, COLUMNB are identical(same number of rows and cell values) in those two SELECTs

COLUMNA,COLUMNB在这两个SELECT中是相同的(行数和单元格值相同)

How can I merge these two SELECTs so I can have four columns with one query and no extra rows

如何合并这两个SELECT,这样我可以有四个列,一个查询,没有额外的行

COLUMNA, COLUMNB, COLUMNC, COLUMND

Updating my question based on comments. Let's say I have two tables like below

根据评论更新我的问题。假设我有两张如下表

TABLE1                     TABLE2
COLUMNA COLUMNB COLUMNC    COLUMNA COLUMNB COLUMND
value1  value2  value3     value1  value2  value9
value4  null    value5     value4  null    value10
null    value6  value7     null    value6  value11
null    null    value8     null    null    value12

result should be

结果应该是

COLUMNA COLUMNB COLUMNC    COLUMND
value1  value2  value3     value9
value4  null    value5     value10
null    value6  value7     value11
null    null    value8     value12

4 个解决方案

#1

I guess you're looking for something like this.

我想你正在寻找这样的东西。

SELECT COLUMNA, COLUMNB, COLUMNC, NULL as COLUMND FROM TABLE1 

UNION 

SELECT COLUMNA, COLUMNB, NULL as COLUMNC, COLUMND FROM TABLE2

or maybe:

SELECT COLUMNA, COLUMNB, COLUMNC FROM TABLE1 

UNION 

SELECT COLUMNA, COLUMNB, COLUMND as COLUMNC FROM TABLE2

#2

This will give you 3 columns, the third being the different valued one:

这将为您提供3列,第三列是不同的值:

SELECT 
 COLUMNA, COLUMNB, COLUMNC AS [Column3]
FROM TABLE1 
UNION ALL
SELECT 
 COLUMNA, COLUMNB, COLUMND AS [Column3]
FROM TABLE2

#3

Try this:

SELECT A.COLUMNA, A.COLUMNB, A.COLUMNC, B.COLUMND 
FROM TABLE1 A
JOIN TABLE2 B ON A.COLUMNA = B.COLUMNA AND A.COLUMNB = B.COLUMNB

And if there is a row that are not identicall in columnA and columnB or not exists, and you want it on the result use:

如果在columnA和columnB中存在一行不相同的行或者不存在,并且您希望它在结果上使用:

SELECT A.COLUMNA, A.COLUMNB, A.COLUMNC, B.COLUMND 
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.COLUMNA = B.COLUMNA AND A.COLUMNB = B.COLUMNB

#4

Since you indicate the rows and values for ColumnA and ColumnB are identical, you can JOIN the two tables together if the values are the same

由于您指示ColumnA和ColumnB的行和值是相同的,因此如果值相同,则可以将两个表连接在一起

SELECT a.ColumnA
      ,a.ColumnB
      ,a.ColumnC
      ,b.ColumnD
FROM Table1 a
JOIN Table2 b
  ON a.ColumnA = b.ColumnA
  AND a.ColumnB = b.ColumnB

If you have disparity between the two tables, i.e. some records in one but not the other, you can use FULL JOIN and change the SELECT:

如果两个表之间存在差异,即一个记录而不是另一个记录,则可以使用FULL JOIN并更改SELECT:

SELECT COALESCE(a.ColumnA,b.ColumnA) AS ColumnA
      ,COALESCE(a.ColumnB,b.ColumnB) AS ColumnB
      ,a.ColumnC
      ,b.ColumnD
FROM Table1 a
FULL JOIN Table2 b
  ON a.ColumnA = b.ColumnA
  AND a.ColumnB = b.ColumnB

#1