Can I write a FULL OUTER JOIN without OR IS NULL?

[emerson]

Here's some data to play with:

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

Now I'd like to write a query that returns:

One possibility is:

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

This requires me to write OR ... IS NULL for every field that I have a condition on. This becomes even more verbose if some conditions are date ranges and the like.

If this were a left join:

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

I could move the condition to the ON clause to avoid this:

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

Is there a way to do this with the full outer join as well?

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=54d72448652b233c122764c675d453a5

[rosemadder]

without OR IS NULL?

col = 'x' OR col IS NULL

Original:

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

Use https://www.postgresql.org/docs/current/functions-comparison.html#FUNCTIONS-COMPARISON-PRED-TABLE :

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;

Or 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;

col <> 'x' OR col IS NULL

The first version of the question asked for this predicate.

Original:

SELECT *
FROM a
FULL OUTER 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);

Use https://www.postgresql.org/docs/current/functions-comparison.html#FUNCTIONS-COMPARISON-PRED-TABLE :

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

Or 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;

db<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=945f8ce50619f40f2a550a083f049946 - showing all

Aside: Instead of != I use <>, which is the standard operator in SQL. (!= is an accepted alias in Postgres.)