Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE a (
    a_id   int  NOT NULL,
    a_prop text NOT NULL
);

CREATE TABLE b (
    b_id   int  NOT NULL,
    b_prop text NOT NULL
);

INSERT INTO a VALUES (1, 'blah'), (2, 'blah'), (4, 'not this one');
INSERT INTO b VALUES (1, 'blah'), (3, 'blah'), (5, 'not this one');
3 rows affected

3 rows affected

SELECT *
FROM a
FULL OUTER JOIN b ON a_id = b_id
WHERE (a_prop = 'blah' OR a_prop IS NULL)
AND (b_prop = 'blah' OR b_prop IS NULL);


a_id
a_prop
b_id
b_prop


1
blah
1
blah

2
blah
null
null

null
null
3
blah


SELECT *
FROM a
LEFT JOIN b ON a_id = b_id
WHERE a_prop = 'blah'
AND (b_prop = 'blah' OR b_prop IS NULL);


a_id
a_prop
b_id
b_prop


1
blah
1
blah

2
blah
null
null


SELECT *
FROM a
LEFT JOIN b ON a_id = b_id AND b_prop = 'blah'
WHERE a_prop = 'blah';


a_id
a_prop
b_id
b_prop


1
blah
1
blah

2
blah
null
null