How do I merge OUTER JOIN and UNION results?-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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