SQL Outer Join 同時調節額外的列

我正在嘗試在特定列“標識符”上拉出兩個表之間的外連接，但是，我只想在表之間附加列“日期”匹配的行上執行外連接。我想要實現的範例是，對於下面的兩個表：

+------------+---------------+-------------------+-----------------+
| Date       | Identifier    | TableA-Attribute1 |TableA-Attribute2|
+------------+---------------+-------------------+-----------------+
| date1      |         Id1   | a                 | x               |
| date1      |         Id2   | b                 | y               |
| date1      |         Id3   | c                 | z               |
| date2      |         Id1   | d                 | x               |
| date2      |         Id2   | e                 | y               |
+------------+---------------+-------------------+-----------------+


+------------+---------------+-------------------+-----------------+
| Date       | Identifier    | TableB-Attribute1 |TableB-Attribute2|
+------------+---------------+-------------------+-----------------+
| date1      |         Id1   | aa                | xx              |
| date1      |         Id2   | bb                | yy              |
| date1      |         Id3   | cc                | zz              |
| date1      |         Id4   | dd                | xx              |
| date2      |         Id1   | ee                | yy              |
+------------+---------------+-------------------+-----------------+

我最終需要看起來像這樣的東西：

+------------+---------------+---------+-------+--------+-------+
| Date       | Identifier    | A-Attr1 |A-Attr2|B-Attr1 |B-Attr2|
+------------+---------------+---------+-------+--------+-------+
| date1      |         Id1   | a       | x     |aa      |xx     |
| date1      |         Id2   | b       | y     |bb      |yy     |
| date1      |         Id3   | c       | z     |cc      |zz     |
| date1      |         Id4   | -       | -     |dd      |xx     |
| date2      |         Id1   | d       | x     |ee      |yy     |
| date2      |         Id2   | e       | y     |-       |-      |
+------------+---------------+---------+-------+--------+-------+

我試圖做這樣的事情

go select * from table1 全外連接（select * from table2 where table1.date=table2.date）

但是，不幸的是，這似乎不起作用。

您不需要子查詢。您只需要在以下ON條件下使用兩列FULL JOIN：

SELECT
   -- t1.*,     -- adjust, keep only 
   -- t2.*,     -- needed columns
   COALESCE(t1.date, t2.date)
                 AS date, 
   COALESCE(t1.identifier, t2.identifier)
                 AS identifier, 
   t1.attribute1 AS a_attribute1,
   t1.attribute2 AS a_attribute2,
   t2.attribute1 AS b_attribute1,
   t2.attribute2 AS b_attribute2
FROM table1 AS t1 
    FULL JOIN table2 AS t2
    ON  t1. date = t2. date
    AND t1. identifier= t2. identifier
-- WHERE
--     (t1.date = '2000-01-01' OR t2.date = '2000-01-01')
;

如果我們希望將日期和標識符分別組合在一列中，我們可以使用COALESCE()函式。

引用自：https://dba.stackexchange.com/questions/229948