sqlsql-servert-sqlselectfull-outer-join

How do I merge OUTER JOIN and UNION results?


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


Solution

  • 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