How do I merge OUTER JOIN and UNION results?

Question

I have two tables, and I want ALL the data from both. If the tables have a matching AssetID, then join them on one row. If not, then on separate rows. A full outer join sounds like the right approach but I have a problem in how to select the keys depending on which table it comes from.

TABLE A                 TABLE B
AssetID | Valuable      AssetID | Protected 
-------------------    -------------------
123     | Yes           123     | Yes   
456     | No            321     | No
653     | Yes

Goal:

TABLE C     
AssetID | Valuable | Protected
---------------------------
123 | Yes   |Yes
456 | No    |
653 | Yes   |
321 |       |No


SELECT TableA.AssetID, TableA.Valuable, TableB.Protected
FROM (
    SELECT AssetID, Valuable
    FROM TableA
) ta    
FULL OUTER JOIN (
    SELECT AssetID, Protected
    FROM TableB   
) tb ON ta.AssetID=tb.AssetID

Produces

TABLE C

AssetID | Valuable | Protected
---------------------------
123     | Yes      |Yes
456     | No       |
653     | Yes      |
        |          |No              <<<< PROBLEM
---------------------------

and I'm missing the key

Note: your query as posted has syntax errors, you alias the tables to `ta` and `tb` but continue continue to use TableA & TableB. — Dale K, Sep 13 '21 at 23:12
Yep, all good ... it's pseudo code so I wasn't looking too closely. — Maxcot, Sep 13 '21 at 23:20
But you want to make sure you have removed all errors first else people waste time looking into the wrong issue. — Dale K, Sep 13 '21 at 23:21
Yep, I agree, and thanks for the advice. Appreciate it. You guys are wizards. — Maxcot, Sep 13 '21 at 23:31

score 5 · Accepted Answer · answered Sep 13 '21 at 23:10

5

You can use coalesce to take the non-null assetID from whatever table has it:

SELECT COALESCE(TableA.AssetID, TableB.AssetID) TableA.Valuable, TableB.Protected
FROM
(
SELECT 
    AssetID,
    Valuable
FROM 
    TableA
) ta

FULL OUTER JOIN 

(SELECT 
    AssetID,
    Protected
FROM 
    TableB

) tb

ON ta.AssetID=tb.AssetID

Note: You probably don't need the sub-queries, though, and omitting them can simplify the query considerably:

SELECT COALESCE(TableA.AssetID, TableB.AssetID) TableA.Valuable, TableB.Protected
FROM TableA
FULL OUTER JOIN TableB
ON TableA.AssetID=TableB.AssetID

answered Sep 13 '21 at 23:10

Mureinik

297,002
52
306
350

OK, that's a great solution. What if there is a second criteria, like a LocationID? s it possible to coalesce on both AssetID and LocationID ? – Maxcot Sep 13 '21 at 23:18
@Maxcot you can use coalesce on any column you like... – Dale K Sep 13 '21 at 23:20
1

@Maxcot `coalesce` takes a list of arguments and returns the first not-null one. They don't have to be from the columns you joined on, or even columns at all. You could even do something like `coalesce(tablea.assetid, tableb.locationid, 123)` – Mureinik Sep 13 '21 at 23:20

score 1 · Answer 2 · answered Sep 14 '21 at 12:08

To deal with Null Values we use Coalesce function. Alternatively in place of full join we can use left join followed by right join.

SELECT COALESCE(TABLEA.AssetID,TABLEB.AssetId) AS 
AssetId,TABLEA.Valuable,TABLEB.Protected
FROM TABLEA 
LEFT JOIN 
TABLEB ON TABLEA.AssetID = TABLEB.AssetID
UNION
SELECT COALESCE(TABLEA.AssetID,T1.AssetID) AS 
AssetID,TABLEA.Valuable,T1.Protected FROM TABLEA
RIGHT JOIN 
TABLEB T1 ON TABLEA.AssetID=T1.AssetID

How do I merge OUTER JOIN and UNION results?

2 Answers2