SQL and MySQL course Β· Module 3: Filtering (WHERE)

Emptiness in the catalog: IS NULL

2 min read Β·
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. 1. What does the value NULL in a column mean?

  2. 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:

  1. QuestionWhat is NULL
  2. QuestionWhy = NULL does not work
  3. Coding taskUnreturned scrolls
  4. Coding taskReturned scrolls
  5. Put in orderAn empty return date

Useful articles