Union if row is null in SQL-CodePudding

Considering the SQL query below, Person will have an Individual or Company. Keeping this in mind, I made this query to unify the data. I would like it to have only the non-null row of the TaxNumber column (From Individual or Company). But the query returns two columns, the null and the one with the value.

SELECT
    PR.Id AS PersonId,
    PR.Title AS PersonTitle,
    IA.TaxNumber AS TaxNumber,
    CP.TaxNumber AS TaxNumber
FROM
    person AS PR
    LEFT JOIN individual AS IA ON IA.PersonID = PR.Id
    LEFT JOIN company AS CP ON CP.PersonID = PR.Id

CodePudding user response：

Your terminology is a bit off which is likey part of the problem in finding a solution. UNION combines data sets {1,3,5} Union {2,4} gives us {1,2,3,4,5}. You want to "combine" columns when one is NULL. Coalesce() does this effectively.

Coalesce() will return the first non-null value in encountered in a series of objects. Coalesce() is pretty database agnostic (meaning its found in most RDBMS. However if it does not work: specify what RDBMS you're using; there's likely a similar function.

SELECT
    PR.Id AS PersonId,
    PR.Title AS PersonTitle,
    Coalesce(IA.TaxNumber, CP.TaxNumber) AS TaxNumber
FROM
    person AS PR
    LEFT JOIN individual AS IA ON IA.PersonID = PR.Id
    LEFT JOIN company AS CP ON CP.PersonID = PR.Id

Examples:

 --------------- --------------- --------- 
| IA Tax number | CP Tax number | Returns |
 --------------- --------------- --------- 
|           123 |        <NULL> |     123 |
|        <NULL> |           456 |     456 |
|           987 |           654 |     987 |
|        <NULL> |        <NULL> |  <NULL> |
 --------------- --------------- ---------

So if you want CP tax number to take precedence just switch the order in the coalesce. Coalesce(CP.TaxNumber, IA.TaxNumber,'MISSING' )

|           987 |           654 |      654 |
|        <NULL> |        <NULL> |  MISSING |

Notice I added a 3rd option if we wanted to call out data was 'MISSING' from both sides. but <NULL> result would mean the same thing.