'Left join a subset of TableA with TableB, but keeping all of the rows from TableA
I have two tables, TableA and TableB, where TableB has extra data about rows in TableA but only when TableA.type IN (2, 3). If I do something like
SELECT
a.id,
a.type,
b.*
FROM TableA a
LEFT JOIN TableB b
ON a.id = b.id
AND a.type IN (2, 3)
WHERE a.dt = '2022-02-03'
AND b.dt = '2022-02-03'
it would only return rows where type is 2 or 3. However, I want to return all of the rows TableA, even when the type isn't 2 or 3. I expect the columns from b.* to be NULL when the type doesn't match.
One idea I was thinking of is to UNION this query with a separate query which manually selects null for all of TableB's columns and checking if the type isn't 2 or 3, but that seems pretty tedious. Is there a cleaner way to accomplish this?
For example, TableA:
| dt | id | type |
|---|---|---|
| '2022-02-03' | 1 | 2 |
| '2022-02-03' | 2 | 3 |
| '2022-02-03' | 3 | 1 |
| '2022-02-03' | 1 | 1 |
and TableB:
| dt | id | col |
|---|---|---|
| '2022-02-03' | 1 | true |
| '2022-02-03' | 2 | false |
| '2022-02-03' | 3 | true |
My query would return a table like
| id | type | dt | col |
|---|---|---|---|
| 1 | 2 | '2022-02-03' | true |
| 2 | 3 | '2022-02-03' | false |
but I would want the rest of the rows in TableA as well
| id | type | dt | col |
|---|---|---|---|
| 1 | 2 | '2022-02-03' | true |
| 2 | 3 | '2022-02-03' | false |
| 3 | 1 | '2022-02-03' | null |
| 1 | 1 | '2022-02-03' | null |
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
