IS NOT DISTINCT FROM vs row-wise equality with =

2 votes

1 answer

1298 views

                          Looking at the below query,

    SELECT null IS NOT DISTINCT FROM null AS indf,
      null = null AS eq;
     indf | eq 
    ------+----
     t    | 

From this we can see the result of the IS NOT DISTINCT FROM is true, and the result of the eq is false. The following then logically follows from that,

    -- returns 1 row.
    SELECT *
    FROM ( VALUES (null) ) AS t(a)
    JOIN ( VALUES (null) ) AS g(a)
      ON t.a IS NOT DISTINCT FROM g.a;
    
    -- returns 0 rows.
    SELECT *
    FROM ( VALUES (null) ) AS t(a)
    JOIN ( VALUES (null) ) AS g(a)
      ON t.a = g.a;

Because if the condition returns null the join fails. But, this throws me off, with [*row-wise comparison*](https://www.postgresql.org/docs/current/static/functions-comparisons.html#ROW-WISE-COMPARISON) 

    -- returns 1 row.
    SELECT *
    FROM ( VALUES (null) ) AS t(a)
    JOIN ( VALUES (null) ) AS g(a)
      ON (t) IS NOT DISTINCT FROM (g);

    -- also returns one row.
    SELECT * 
    FROM ( VALUES (null) ) AS t(a)
    JOIN ( VALUES (null) ) AS g(a)
      ON (t) = (g);

Why does [*row-wise comparison*](https://www.postgresql.org/docs/current/static/functions-comparisons.html#ROW-WISE-COMPARISON)  treat nulls different than scalar comparison? And is there a point of IS NOT DISTINCT FROM in row-wise comparison?
                        

Asked by Evan Carroll (65502 rep)

Sep 11, 2018, 11:25 PM
Last activity: Dec 29, 2023, 05:44 PM

IS NOT DISTINCT FROM vs row-wise equality with =

Related Questions