← WA Computer Science ATAR Unit 2

NULL values and 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.

Part of: WA Computer Science ATAR Year 11 — Data Management — Module 67

Understanding NULL values and IS NULL in WA Computer Science ATAR Unit 2

What it does: IS NULL is the only test that matches a missing value. Writing = NULL gives no matches. NULL means no value has been stored. It is different from a blank text value or the number 0. Aggregates like AVG, SUM, MIN and MAX ignore NULL. AVG is (80 + 60) / 2 = 70, not divided by 3. COUNT(field) counts only non-NULL values. COUNT(*) would count all 10 rows. NULL means unknown or missing, so comparing it with = is never true. IS NULL is needed.

Common mistakes: = NULL never matches, because NULL is not equal to anything, not even NULL. Use IS NULL to find missing phone numbers. COUNT(mark) ignores NULL values, so on rows where mark is NULL it counts nothing and returns 0. Use COUNT(*) to count the rows.

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 and IS NULL to a class →

This module's key terms are also in a printable crossword (Modules 28–122) and word search (Modules 28–266).

Take this worksheet further — free with Cloodit

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

Learning

Duo

Which WHERE clause correctly finds students with no house recorded (house is NULL)?

WHERE house IS NULLWHERE house = NULL

Matches

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.

Applying

Meanings

Term

IS NULL

Meaning

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.

Practice

UPDATE students SET house = NULL WHERE studentId = 10;

Testing

Gaps

;

Check Answer

Spots

3WHERE name LIKE 'A%'

4AND phone = NULL;

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

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. Which WHERE clause correctly finds students with no house recorded (house is NULL)?

A. WHERE house IS NULL ✓

B. WHERE house = NULL

= NULL never matches, because NULL is not equal to anything, not even itself. Use IS NULL to test for a missing value.

2. Which query returns only the rows where mark has a value?

A. WHERE mark IS NOT NULL ✓

B. WHERE mark != NULL

!= NULL never matches either. Use IS NOT NULL to keep rows that have a value.

3. A student's middle name was never entered, so the field holds NULL. What does NULL mean here?

A. The value is an empty string or 0

B. The value is missing or unknown ✓

NULL means no value has been stored. It is different from a blank text value or the number 0.

4. A field holds '' (an empty string). Is it the same as NULL?

A. Yes. Blank and NULL are the same thing

B. No. A blank string is a value, NULL is a missing value ✓

An empty string is a real text value with length 0. NULL means nothing has been stored, so WHERE field IS NULL will not match ''.

5. A price field is NULL for a new product. Is its price 0?

A. No. The price is unknown, not zero ✓

B. Yes. NULL is stored as the number 0

0 is a real number and could be used in calculations. NULL means the price is not known yet.

Match the Pairs

SELECT firstName FROM students WHERE house IS NULL; → Returns the students who have no house recorded, because their house value is missing (NULL).

SELECT COUNT(points) FROM students; → Counts only the students whose points value is not NULL, because aggregates ignore NULL.

To find rows where a value is missing, you must use IS NULL, because = NULL never matches anything. → WHERE mark IS NULL

A NOT NULL constraint stops a column from being left empty when a row is inserted. → lastName VARCHAR(30) NOT NULL

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.

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.

Key Term

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.

Fill in the Blanks

1. SELECT firstName FROM students WHERE house IS NULL;

Hint: Type the two-word test that finds rows where the field has a missing value.

2. SELECT * FROM products WHERE price IS NOT NULL;

Hint: Type the keyword that reverses the test so only rows with a value are returned.

3. CREATE TABLE club ( name TEXT NOT NULL );

Hint: Type the constraint that stops a missing value from being stored in this column.

4. -- Students who have a points value SELECT COUNT(points) FROM students;

Hint: Type the aggregate function that counts values and skips NULL entries.

5. -- house is blank text, not NULL SELECT * FROM students WHERE house = '';

Hint: Type the operator used to compare a field with blank text. A blank is a real value, unlike NULL.

Spot the Error

Question 1

A SELECT name, phone

B FROM contacts

C WHERE name LIKE 'A%'

D AND phone = NULL;

Line D — = NULL never matches, because NULL is not equal to anything, not even NULL. Use IS NULL to find missing phone numbers.

Question 2

A SELECT COUNT(mark) AS missing

B FROM results

C WHERE mark IS NULL;

Line 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.

Question 3

A SELECT name

B FROM staff WHERE email = ''

C ORDER BY name;

Line 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.

Question 4

A SELECT name, email

B FROM staff

C WHERE email != NULL

D ORDER BY name;

Line 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.

Question 5

A UPDATE staff SET email = 'NULL'

B WHERE staffId = 7;

C SELECT * FROM staff;

Line 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.

Student with a class code? Join your class →