SQL and MySQL course Β· Module 5: Modifying data (DML)

NULL and default values in DML

2 min read Β·
In this lesson4

Not every field on a scroll must be filled. Sometimes the scribe does not yet know a value - a book is on loan but has not returned, so data_zwrotu stays empty. In the Library's language we call such emptiness NULL.

What is NULL?

NULL is the absence of a value - not zero, not empty text, but the information "there is nothing here". This is an important distinction:

  • 0 - is the specific number zero,
  • '' - is empty text (a text of length zero),
  • NULL - is the absence of any value.

Inserting NULL in INSERT

To record an absence of a value, in place of the value we write the word NULL without single quotes:

INSERT INTO wypozyczenia (ksiazka_id, czytelnik_id, data_wypozyczenia, data_zwrotu)
VALUES (5, 7, '2024-03-01', NULL);

Here we set data_zwrotu to NULL - the book has been loaned out, but there is no return date yet.

Clearing a field with UPDATE

To empty an existing field (not the whole row!), we use UPDATE ... SET field = NULL:

UPDATE wypozyczenia
SET data_zwrotu = NULL
WHERE id = 8;

Remember: this is still an UPDATE - so the WHERE applies here too! Without it you would clear data_zwrotu in all loans.

NULL versus a missing column

If you skip a column in INSERT, the Library inserts its default value (often NULL, and for id - the next number from AUTO_INCREMENT). Writing NULL explicitly has the same effect, but your intent is visible at once.

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. How do you mark in an INSERT command that a given column has no value?

Hands-on tasks in the game

  • Code editor

    Write a command that adds to the wypozyczenia table a row (columns ksiazka_id, czytelnik_id, data_wypozyczenia, data_zwrotu): 5, 7, '2024-03-01' and NULL for data_zwrotu (the book has not returned yet).

  • Vertical ordering

    Arrange the lines of a multi-row INSERT in the correct order (top to bottom):

  • Horizontal ordering

    Arrange on one line the SET fragment that sets a field to NULL (left to right):

  1. QuestionWhat is NULL
  2. QuestionInserting NULL
  3. Coding taskInsert a loan with no return date
  4. Put in orderA multi-row INSERT of categories
  5. Put in orderSetting the return date to NULL

Useful articles