SQL and MySQL course Β· Module 5: Modifying data (DML)
NULL and default values in DML
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. What does the value NULL in a column mean?
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):