Getting to Know NULL. How NULL is Different from 0

It's time to talk more about NULL. And this is not just an empty talk, even though our hero looks... like nothing at all. If a table was a house, NULL would be its ghost — silent, mysterious, and unpredictable. It's kinda there, but doesn't answer questions, doesn't join in calculations, and dodges comparisons. It's not just "empty" — it's "nothing is known".

In the SQL world, NULL is not zero, not an empty string, and not "no data" in the usual sense. It's a special sign of the unknown, and you gotta handle it with care. Today we'll figure out why NULL isn't just a weird guest, but a key player in any database. It literally means "the value is unknown or undefined".

How is NULL different from an empty string or the number 0?

A lot of newbies mix up NULL with other values, like the empty string '' or the number 0. Let's break down the key differences:

Real-life example: imagine you have an employees table, and you're storing their salaries. If the salary is 0, it means the person didn't get paid anything. But if the salary is NULL, you just don't know how much they're paid (or it hasn't been decided yet).

How to interpret NULL

Now that we know a bit about what NULL is, let's see how SQL deals with it. The most important thing about NULL can be summed up in one phrase: NULL is not equal to anything and not unequal to anything, not even to itself.

SELECT NULL = NULL;  -- Result: FALSE

Surprise! Why is that? Because NULL means unknown. If you have two unknown values, you can't say they're equal, and you can't say they're not equal either.

Let's break down a few examples:

Examples of operations with NULL

SELECT NULL + 1;      -- Result: NULL
SELECT NULL * 100;    -- Result: NULL
SELECT NULL = 0;      -- Result: FALSE
SELECT NULL <> 0;     -- Result: FALSE

NULL in addition, multiplication, or any other calculation always gives back NULL. Here, NULL doesn't work like 0 in math. It's like throwing something into a void and the void just eats it all up.

Practical examples

Let's imagine a students table with info about students:

Example 1: How NULL affects conditions

If we try to find all students without a birth date by using WHERE birth_date = NULL:

SELECT *
FROM students 
WHERE birth_date = NULL;

We won't get any results, because NULL = NULL returns FALSE. To check for NULL you gotta use IS NULL:

SELECT * FROM students WHERE birth_date IS NULL;

Result:

Example 2: How NULL affects calculations

If we try to calculate the average grade for all students:

SELECT AVG(grade) FROM students;

The result will be: 87.5. Why? Because NULL gets ignored in aggregate functions like AVG, SUM, MIN, MAX. But be careful! If you need to include rows with NULL, you'll need to do some extra work.

We'll dig deeper into working with NULL in the next lectures.

2

Task

SQL SELF, level 9, lesson 0

Locked

Using HAVING to Filter Average Ratings

Using HAVING to Filter Average Ratings

Open

2

Task

SQL SELF, level 9, lesson 0

Locked

Counting Customers by Region with Filtering

Counting Customers by Region with Filtering

Open