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

## All we need is an easy explanation of the problem, so here it is.

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?

Fiddle

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

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);
``````
``````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);
``````
``````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 here – showing all

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

### Method 2

Another option:

``````SELECT *
FROM a
FULL JOIN b
ON a.a_id = b.b_id
WHERE EXISTS
(
SELECT 'blah'
INTERSECT
VALUES (a.a_prop), (b.b_prop)
);
``````
a_id a_prop b_id b_prop
1 blah 1 blah
2 blah null null
null null 3 blah

db<>fiddle

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0