Everything You Probably Never Wanted to Know about NULLs

This presentation was given by Chris Travers, Senior PostgreSQL Consultant at Vettabase, at the namesake free webinar on February 21, 2024.

Transcript

1. Chris Travers Everything You Never Probably Wanted to Know about NULL
2. About Me (Speaker Introduction)
3. Math NULLs in Databases Null Set {} Null Tuple ()
4. Null, Special Thanks To Sir Charles Anthony Hoare But things are about to get a lot worse. A lot worse. . .
5. Three value logic = anything = NULL IS TRUE/FALSE/NULL IS TRUE/FALSE/NULL IS NOT TRUE/FALSE/NULL IS NOT TRUE/FALSE/NULL Only = evaluates to NULL. IS and IS NOT never return NULL
6. Choosing the Right Operator =, <> Comparison to NULL returns NULL IS, IS NOT Never returns NULL, booleans only IS DISTINCT FROM Non-nulling not equals IS NOT DISTINCT FROM non-nulling equals
7. Advanced Fun Rowtypes and NULL
8. More Advanced Fun IS NULL values in NOT NULL fields
9. CHECK NOT NULL vs NOT NULL CREATE TYPE foo AS (id int, something text); CREATE TABLE bar (foo foo NOT NULL); INSERT INTO bar VALUES (ROW(,)::foo); ALTER TABLE bar ADD CHECK foo IS NOT NULL; fails
10. Antijoins – Bad SELECT * FROM a WHERE b˙ someval NOT IN (select someval from b); This is bad because if b˙ someval has nulls, then no rows will be returned.
11. Antijoins – Minimal Fix SELECT * FROM a WHERE b˙ someval NOT IN (select someval from b WHERE someval IS NOT NULL); This works but is error prone. Sooner or later someone will forget the WHERE clause.
12. Antijoins – Better SELECT * FROM a WHERE NOT EXISTS (select 1 FROM b WHERE a˙ someval = b˙ someval); This is better and is the usual advice: it turns the antijoin condition into an inner join and then excludes. However the planner has less freedom with this approach and this can cause performance issues.
13. Antijoins – Good SELECT a˙ * FROM a LEFT JOIN b USING (someval) WHERE b IS NULL; By treating the whole input tuple as NULL we can eliminate any matches in the outer join. This allows us to do effective antijoins which the planner can optimize, and we can use NULL to our advantage here.
14. Questions?