'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