← QCE Digital Solutions Unit 2

Null values

A field with no value at all. It is not zero and not an empty string. Test for it with IS NULL, never = NULL. A primary key can never be NULL.

Part of: QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 90

Understanding Null values in QCE Digital Solutions Unit 2

What it does: NULL means no value, so it cannot be compared with =. IS NULL is the correct test. A primary key must uniquely identify each record, so SQL does not allow it to hold NULL. NULL is not equal to anything, even itself, so = NULL never matches. Use IS NULL to test for it. NOT NULL forces every row to have a value in that column. It does not add a default value by itself. Required values like the primary key should be NOT NULL. Optional details like a middle name can be NULL. Stock 0 is a known quantity. NULL would mean the stock level is unknown or not recorded.

Common mistakes: Nothing is ever equal to NULL, so = NULL never matches any row. Test for a missing value with IS NULL: WHERE house IS NULL;. An empty string '' is a value (text with no characters), not a missing value. To record that there is no phone number, set the field to NULL: SET phone=NULL.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Null values to a class →

This module's key terms are also in a printable crossword (Modules 186–228) and word search (Modules 144–228).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
A student has not handed in a task yet. Which value best shows 'no mark recorded' in the mark column?
0NULL
Matches
SELECT * FROM results WHERE mark = NULL;
↓
Uses = to test for NULL. NULL is not a value, so the comparison never matches. It should be WHERE mark IS NULL.
Applying
Meanings
Term
NULL
Meaning
A field with no value at all. It is not zero and not an empty string. Test for it with IS NULL, never = NULL. A primary key can never be NULL.
Practice
INSERT INTO students VALUES (11, 'Sam', 'Lee', 7, NULL, 0);
Testing
Gaps
;
Check Answer
Spots
2FROM students
3WHERE house = NULL;

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Fox' badge — Cunning Debugger, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. A student has not handed in a task yet. Which value best shows 'no mark recorded' in the mark column?
A. 0
B. NULL ✓
NULL means no value at all. 0 is a real mark, so it would wrongly say the student scored zero.
2. Which statement about NULL is correct?
A. NULL is the same as an empty string ''
B. NULL is not the same as an empty string '' ✓
An empty string is a real text value with no characters. NULL means there is no value stored at all.
3. Which query correctly finds students with no house recorded?
A. SELECT * FROM students WHERE house IS NULL; ✓
B. SELECT * FROM students WHERE house = NULL;
NULL is not equal to anything, even itself, so = NULL never matches. Use IS NULL to test for it.
4. Which query returns only rows where the mark has a value?
A. WHERE mark IS NOT NULL ✓
B. WHERE mark != NULL
Use IS NOT NULL to keep rows that have a value. Comparing with != NULL does not work.
5. What does the NOT NULL constraint do on a column?
A. It stops a row being saved with that field empty ✓
B. It fills the field with 0 when no value is given
NOT NULL forces every row to have a value in that column. It does not add a default value by itself.

Match the Pairs

email VARCHAR(50) NOT NULL → The email field must always have a value, so a record cannot be saved with it left empty (NULL)
SELECT * FROM students WHERE house IS NULL; → Finds the students who have no value stored in the house field
To test for a missing value in a WHERE clause, you must use IS NULL, not an equals sign → WHERE mark IS NULL
A primary key can never be NULL, because every record needs a value that identifies it → studentId INT PRIMARY KEY
SELECT * FROM results WHERE mark = NULL; → Uses = to test for NULL. NULL is not a value, so the comparison never matches. It should be WHERE mark IS NULL.
INSERT INTO students (studentId, firstName) VALUES (NULL, 'Ava'); → Tries to insert NULL into the primary key studentId. A primary key can never be NULL, so it needs a unique value such as 11.

Key Term

NULL
A field with no value at all. It is not zero and not an empty string. Test for it with IS NULL, never = NULL. A primary key can never be NULL.

Fill in the Blanks

1. SELECT firstName FROM students WHERE house IS NULL;
Hint: Type the two-word test that finds fields with no value at all.
2. name VARCHAR(30) NOT NULL,
Hint: Type the constraint that forces every row to have a value in this column.
3. SELECT COUNT(*) FROM students WHERE house IS NOT NULL;
Hint: Type the keyword that reverses the test so only filled-in fields match.
4. studentId INT PRIMARY KEY,
Hint: Type the constraint that makes a column unique and means it can never be null.
5. UPDATE students SET house = NULL WHERE studentId = 4;
Hint: Type the keyword that means no value at all, which is not zero or empty text.

Spot the Error

Question 1
A SELECT firstName
B FROM students
C WHERE house = NULL;
Line C — Nothing is ever equal to NULL, so = NULL never matches any row. Test for a missing value with IS NULL: WHERE house IS NULL;
Question 2
A UPDATE staff SET phone=''
B WHERE staffId = 3;
C SELECT * FROM staff;
Line A — An empty string '' is a value (text with no characters), not a missing value. To record that there is no phone number, set the field to NULL: SET phone=NULL
Question 3
A CREATE TABLE books (
B bookId INT PRIMARY KEY NULL,
C title TEXT NOT NULL
D );
Line B — A primary key can never be NULL because it must identify every record. Remove NULL: bookId INT PRIMARY KEY,
Question 4
A CREATE TABLE pets (
B petId INT PRIMARY KEY,
C name TEXT IS NOT NULL
D );
Line C — IS NOT NULL is used to test values in a WHERE clause. When defining a column, the constraint is written NOT NULL: name TEXT NOT NULL
Question 5
A SELECT name FROM staff
B WHERE phone = ''
C ORDER BY name;
Line B — An empty string is not NULL, so this would not find staff with no phone value recorded. Use IS NULL: WHERE phone IS NULL

Student with a class code? Join your class →