3

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:

result

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

1
  • 2
    Don't change the nature of the question after answers have been given. Next time, please start a new question instead. Apr 26 at 16:09

2 Answers 2

5

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 col <> x 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;

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 col IS DISTINCT FROM 'x':

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

1
  • @AndreKR Filtering before the join should even be most efficient. Easiest to optimize for Postgres, especially with an index on a_prop and a selective predicate. Apr 26 at 17:29
3

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.