SQL Outer Join while conditioning on an extra column

join;query

I am trying to pull up an outer join between two tables, on a specifc column "Identifier", however, I would only like to carry out the outer joins on the rows where additional column "Date" matches between the tables. Example of what I am trying to achieve is, for the two tables below:

+------------+---------------+-------------------+-----------------+
| 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              |
+------------+---------------+-------------------+-----------------+

I need to end up with something that looks like this:

+------------+---------------+---------+-------+--------+-------+
| 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     |-       |-      |
+------------+---------------+---------+-------+--------+-------+

I was trying to do something like this

go
select * from
table1 full outer join (select * from table2 where table1.date=table2.date)

however, that unfortunately does not seem to be working.

Best Answer

You don't need subqueries. You only need to use both columns in the ON condition of the 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')
 ;

If we want date and identifier combined in one column each, we can use COALESCE() function.

Related Question