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

Answer

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