Postgresql – NULL rows and IS NULL

composite-typesnullpostgresql

I've been writing some queries that look like this:

SELECT
  ...
FROM table_a a
LEFT JOIN table_b b
  ON b.id = a.id
LEFT JOIN table_c c
  ON c.id = a.id

In these queries only one of the joins can be successful. In other words, a row from table_a is matched by either a row from table_b or a row from table_c.

In the select list, there is some logic based on which join succeeds. I can test this by doing the following:

SELECT
  CASE
    WHEN b IS NULL THEN
      -- do something
    ELSE NULL
  END
FROM table_a a
LEFT JOIN table_b b
  ON b.id = a.id
LEFT JOIN table_c c
  ON c.id = a.id

When testing if a row is NULL you can use the table alias in an IS NULL expression, but it does not work as expected in an IS NOT NULL expression. If there are columns in table_b that are not NULL, the expression table_b IS NOT NULL will return FALSE.

However, the expression NOT (table_b IS NULL) will return TRUE as expected.

Why this is the case?

Best Answer

This is a sad story.

When the SQL standard committee defined the NULL-ness of composite values, they must have taken the wrong drugs.

Anyway, they ruled that a composite IS NULL if all elements are NULL, and that a composite IS NOT NULL if all elements are NOT NULL.

In other words, there are composites that are neither NULL nor NOT NULL.

PostgreSQL implements that, but treats composites as NOT NULL in most other respects. There have been discussions about this, but in the face of the standard's definitions, nobody could come up with a consistent solution.

This is a mess, and you are well advised not to think too hard about this if you want to keep your peace of mind.

Related Question