SQL and MySQL course Β· Module 3: Filtering (WHERE)
Emptiness in the catalog: IS NULL
In this lesson3
Not every scroll has all its fields filled in. Sometimes an author's year of birth is unknown, and a borrowed book has not yet been returned. This emptiness - the absence of any value - is called NULL in a database.
NULL is not zero!
Important: NULL is not the same as the number 0 nor the empty text ''. NULL means "there is nothing here, the value is unknown". It is like an empty field on a scroll that nobody has filled in yet.
Why = NULL does not work
Because of this "unknown" nature, we cannot compare NULL with the ordinary = sign. This query will not work correctly:
SELECT * FROM wypozyczenia
WHERE data_zwrotu = NULL;The Library does not know whether "unknown" equals "unknown", so it will always return nothing. That is why MySQL has special operators.
IS NULL and IS NOT NULL
To check whether a value is empty, we use IS NULL:
SELECT * FROM wypozyczenia
WHERE data_zwrotu IS NULL;This means: "Loans that have not yet been returned" - because as long as the book is with the reader, data_zwrotu stays empty.
To find rows that do have a value, we use IS NOT NULL:
SELECT * FROM wypozyczenia
WHERE data_zwrotu IS NOT NULL;This means: "Loans already returned" - because data_zwrotu has been filled in.
Remember a simple rule: with NULL we never use =, only IS NULL or IS NOT NULL. This is one of the most common mistakes of beginning scribes - you will already avoid it!
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What does the value NULL in a column mean?
2. Why does the condition WHERE data_zwrotu = NULL not work correctly?
Hands-on tasks in the game
- Code editor
Write a query that retrieves all columns from the wypozyczenia table for those loans that have not yet been returned, that is, where data_zwrotu is empty (NULL).
- Code editor
Write a query that retrieves all columns from the wypozyczenia table for those loans that have already been returned, that is, where data_zwrotu is NOT empty. Use IS NOT NULL.
- Horizontal ordering
Arrange in one line the condition checking that the return date is empty: