NULL values and IS NULL
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which WHERE clause correctly finds students with no house recorded (house is NULL)?
2. Which query returns only the rows where mark has a value?
3. A student's middle name was never entered, so the field holds NULL. What does NULL mean here?
4. A field holds '' (an empty string). Is it the same as NULL?
5. A price field is NULL for a new product. Is its price 0?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1SELECT firstName FROM students WHERE house IS NULL; | AlastName VARCHAR(30) NOT NULL | |
| 2SELECT COUNT(points) FROM students; | BReturns the students who have no house recorded, because their house value is missing (NULL). | |
| 3To find rows where a value is missing, you must use IS NULL, because = NULL never matches anything. | CCounts only the students whose points value is not NULL, because aggregates ignore NULL. | |
| 4A NOT NULL constraint stops a column from being left empty when a row is inserted. | D= NULL never matches, because NULL is not a value that can be compared with =. The query returns no rows. It should use WHERE house IS NULL. | |
| 5SELECT * FROM students WHERE house = NULL; | EWHERE mark IS NULL | |
| 6SELECT * FROM students WHERE house = ''; | FThis tests for a blank text value, not NULL. A missing house is NULL, which is different from an empty string, so the rows are missed. It should use WHERE house IS NULL. |
C
Definitions
IS NULL
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT firstName FROM students WHERE house
_______;
2.
SELECT * FROM products WHERE price IS ___
NULL;
3.
CREATE TABLE club (
name TEXT ________
);
4.
-- Students who have a points value
SELECT _____(points) FROM students;
5.
-- house is blank text, not NULL
SELECT * FROM students WHERE house _ '';
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT name, phone
B
FROM contacts
C
WHERE name LIKE 'A%'
D
AND phone = NULL;
Question 2
A
SELECT COUNT(mark) AS missing
B
FROM results
C
WHERE mark IS NULL;
Question 3
A
SELECT name
B
FROM staff WHERE email = ''
C
ORDER BY name;
Question 4
A
SELECT name, email
B
FROM staff
C
WHERE email != NULL
D
ORDER BY name;
Question 5
A
UPDATE staff SET email = 'NULL'
B
WHERE staffId = 7;
C
SELECT * FROM staff;
Answer Key
NULL values and IS NULL
Section A: Quick Questions
Q1
A — WHERE house IS NULL
Q2
A — WHERE mark IS NOT NULL
Q3
B — The value is missing or unknown
Q4
B — No. A blank string is a value, NULL is a missing value
Q5
A — No. The price is unknown, not zero
Section B: Match the Pairs
Q1
SELECT firstName FROM students WHERE house IS NULL; → Returns the students who have no house recorded, because their house value is missing (NULL).
Q2
SELECT COUNT(points) FROM students; → Counts only the students whose points value is not NULL, because aggregates ignore NULL.
Q3
To find rows where a value is missing, you must use IS NULL, because = NULL never matches anything. → WHERE mark IS NULL
Q4
A NOT NULL constraint stops a column from being left empty when a row is inserted. → lastName VARCHAR(30) NOT NULL
Q5
SELECT * FROM students WHERE house = NULL; → = NULL never matches, because NULL is not a value that can be compared with =. The query returns no rows. It should use WHERE house IS NULL.
Q6
SELECT * FROM students WHERE house = ''; → This tests for a blank text value, not NULL. A missing house is NULL, which is different from an empty string, so the rows are missed. It should use WHERE house IS NULL.
Section C: Definitions
Q1
IS NULL — NULL marks a missing value. Test for it with IS NULL or IS NOT NULL, because = NULL never matches. NULL is not a blank or 0, and aggregates ignore it.Section D: Fill in the Blanks
Q1
SELECT firstName FROM students WHERE house
IS NULL;Q2
SELECT * FROM products WHERE price IS NOT
NULL;Q3
CREATE TABLE club (
name TEXT NOT NULL
);Q4
-- Students who have a points value
SELECT COUNT(points) FROM students;Q5
-- house is blank text, not NULL
SELECT * FROM students WHERE house = '';Section E: Spot the Error
Q1
D — = NULL never matches, because NULL is not equal to anything, not even NULL. Use IS NULL to find missing phone numbers.
Q2
A — COUNT(mark) ignores NULL values, so on rows where mark is NULL it counts nothing and returns 0. Use COUNT(*) to count the rows.
Q3
B — A missing email is stored as NULL, which is not the same as an empty string. Comparing with '' misses those rows. Use IS NULL.
Q4
C — Comparing with != NULL never gives a true result, so no rows are returned. Use IS NOT NULL to find rows that have a value.
Q5
A — 'NULL' in quotes is just the text N-U-L-L, not a missing value. Write NULL without quotes to mark the email as missing.