In this lecture, we're gonna get to know our mysterious buddy NULL even better. Of course, your own mistakes with it are still ahead, but... being warned means being ready. Let's break down a few classic mistakes related to NULL.
Mistake 1: Using the regular = operator to check for NULL
Probably the most popular mistake among SQL newbies is trying to use the = operator to check if a value is NULL.
What's going on?
SELECT *
FROM students
WHERE age = NULL;
Naively thinking this will show all students with an unknown age, you'll be disappointed: this query returns nothing. Why? The thing is, NULL isn't a value, so regular comparison operators just don't work with it. As the magic SQL book says: "You can't compare NULL directly to anything."
How should it be?
To check if a value is NULL, use IS NULL:
SELECT *
FROM students
WHERE age IS NULL;
Now you'll get all students whose age isn't specified.
Mistake 2: Aggregate functions ignore NULL (except COUNT(*))
When you run queries with aggregate functions, NULL gets automatically left out of the calculations. This can lead to some unexpected results.
What's going on?
SELECT AVG(salary) AS avg_salary
FROM employees;
If the salary column has NULLs, those rows just get ignored, and the average salary is calculated without them. This can give you a false idea of the average salary.
How to avoid it?
Before you aggregate, make sure you properly replace NULL with a default value. For example, use COALESCE():
SELECT AVG(COALESCE(salary, 0)) AS avg_salary
FROM employees;
Now NULL values will be replaced with 0 before calculating.
Mistake 3: Comparing NULL to itself
In a database, NULL literally isn't equal to anything, not even another NULL. This can be a surprise.
What's going on?
SELECT *
FROM students
WHERE NULL = NULL;
This query also returns nothing. Why? Because SQL thinks that the absence of one value can't be "equal" to the absence of another. Yeah, SQL is a philosophical language.
How should it be?
If you need to check if two things are both NULL, use special constructs like IS NULL. For example:
SELECT *
FROM students
WHERE first_name IS NULL AND last_name IS NULL;
Mistake 4: Dividing by NULL
Dividing by NULL isn't just a mistake, it's kind of a mathematical crime, and SQL punishes you with a meaningless result — NULL.
What's going on?
SELECT 10 / NULL AS result;
The result? NULL. SQL doesn't even try to figure out what you want from it.
How to avoid it?
To keep your queries safe from this kind of weirdness, use COALESCE() or NULLIF():
SELECT 10 / COALESCE(divisor, 1) AS result
FROM calculations;
In this query, if divisor is NULL, instead of dividing by NULL you'll divide by 1.
Mistake 5: Logical operators not working right with NULL
NULL breaks logic as soon as it shows up in expressions. For example, the condition TRUE AND NULL returns NULL, not TRUE or FALSE.
What's going on?
SELECT *
FROM students
WHERE age > 18 OR age = NULL;
Here, even if age > 18 is true for some rows, some rows with NULL in the age column might get left out. Why? Because the part age = NULL will return NULL, not TRUE.
How should it be?
Always handle NULL values explicitly in logical conditions:
SELECT *
FROM students
WHERE age > 18 OR age IS NULL;
Mistake 6: Implicit behavior when sorting NULL (the "heaviest" mistake)
If you use ORDER BY in a query, the way NULL behaves might surprise you. By default, PostgreSQL puts rows with NULL values at the end when sorting ascending, and at the start when sorting descending.
What's going on?
SELECT product_name, price
FROM products
ORDER BY price;
If price has NULL, those rows will show up at the end of the list.
How to avoid surprises?
You can explicitly set how NULL is sorted using NULLS FIRST or NULLS LAST:
SELECT product_name, price
FROM products
ORDER BY price NULLS FIRST;
Mistake 7: Wrong handling of foreign keys and NULL
NULL values in columns with foreign keys can sometimes lead to unexpected behavior.
What's going on?
If you added foreign keys to a table and try to insert a row leaving the foreign key field empty, PostgreSQL won't complain at all. That's because NULL values aren't checked for matches in the related tables.
How to do it right?
Use NOT NULL constraints if you want to prevent NULL in those fields. Or just keep in mind that NULL values are "orphans" that don't belong to any related table.
You'll learn more about related tables and foreign keys in the next lecture :P
GO TO FULL VERSION