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');

CREATE TABLE



CREATE TABLE



INSERT 0 3



INSERT 0 3


-- original: col = 'x' OR col IS NULL
SELECT *
FROM   a
FULL   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 3


-- alternative1: x <> y IS NOT TRUE
SELECT *
FROM   a
FULL   JOIN b ON a_id = b_id
WHERE  a_prop <> 'blah' IS NOT TRUE
AND    b_prop <> 'blah' IS NOT TRUE;


a_id
a_prop
b_id
b_prop


1
blah
1
blah

2
blah
null
null

null
null
3
blah



SELECT 3


-- alternative2: filter before joining
SELECT *
FROM      (SELECT * FROM a WHERE a_prop = 'blah') a
FULL JOIN (SELECT * FROM b WHERE b_prop = 'blah') b ON a_id = b_id;


a_id
a_prop
b_id
b_prop


1
blah
1
blah

2
blah
null
null

null
null
3
blah



SELECT 3


-- original: col <> 'x' OR col IS NULL
SELECT *
FROM   a
FULL   JOIN b ON a_id = b_id
WHERE (a_prop <> 'not this one' OR a_prop IS NULL)
AND   (b_prop <> 'not this one' 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 3


-- alternative1: IS DISTINCT FROM
SELECT *
FROM   a
FULL   JOIN b ON a_id = b_id
WHERE  a_prop IS DISTINCT FROM 'not this one'
AND    b_prop IS DISTINCT FROM 'not this one';


a_id
a_prop
b_id
b_prop


1
blah
1
blah

2
blah
null
null

null
null
3
blah



SELECT 3


-- alternative2: filter before joining:
SELECT *
FROM      (SELECT * FROM a WHERE a_prop <> 'not this one') a
FULL JOIN (SELECT * FROM b WHERE b_prop <> 'not this one') b ON a_id = b_id;


a_id
a_prop
b_id
b_prop


1
blah
1
blah

2
blah
null
null

null
null
3
blah



SELECT 3