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



  • 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?

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



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2