SQLSorting and Limiting Resultsbeginner45 min read

Working with NULL Values

Understand NULL values and SQL three-valued logic, distinguish missing data from zero and empty strings, filter NULL values correctly, and use COALESCE and NULLIF to handle missing data and prevent division-by-zero errors.

On This Page (Table of Contents)

In this lesson, you will learn

✓Explain what NULL represents in a relational database
✓Distinguish NULL from zero, an empty string, and FALSE
✓Explain SQL three-valued logic using TRUE, FALSE, and UNKNOWN
✓Understand how NULL affects comparisons, arithmetic, and logical operators
✓Filter missing values using IS NULL and IS NOT NULL
✓Use COALESCE to return the first non-NULL expression
✓Use NULLIF to turn a specified value into NULL
✓Prevent division-by-zero errors using NULLIF
✓Understand how aggregate functions, joins, and constraints interact with NULL
✓Recognize common NULL-related bugs in SQL queries

1. What NULL Actually Means

In SQL, NULL represents a value that is missing, unknown, or not applicable. It does not mean that the value is zero or that a text field is empty.

Imagine a customers table. One customer has a phone number, another has no phone number recorded, and a third has chosen not to provide one. The database may store NULL for the missing phone number, depending on the application's design.

NULL does not tell you exactly why the value is missing. It simply indicates that the column does not currently contain an ordinary value.

Different Values Are Not the Same

sql

1
2
3
4
5
SELECT
  NULL AS missing_value,
  0 AS numeric_zero,
  '' AS empty_text,
  FALSE AS boolean_false;

PostgreSQL can return all four expressions in one row. NULL is a missing value, 0 is a number, an empty string is text containing no characters, and FALSE is a Boolean value.

Remember This

NULL is not zero, NULL is not an empty string, and NULL is not FALSE. Treat it as a special marker for missing or unknown information.

2. Understanding NULL with a Table

Consider this sample customers table. The phone column is allowed to contain NULL.

Sample Customer Data

sql

1
2
3
4
5
6
7
-- Illustrative data
customer_id | first_name | phone
------------+------------+-------------
1           | Jane       | 555-123-4567
2           | Robert     | NULL
3           | Carlos     | ''
4           | Aisha      | 555-987-6543

Robert's phone is missing as represented by NULL. Carlos's phone is an empty string. These values are different, so a query that checks for NULL will not automatically find empty strings.

3. SQL Three-Valued Logic

In ordinary Boolean logic, a condition is TRUE or FALSE. SQL introduces a third logical result: UNKNOWN. UNKNOWN commonly occurs when an ordinary comparison depends on a NULL value.

For example, if salary is NULL, the database cannot determine whether salary is greater than 50000. The expression salary > 50000 therefore evaluates to UNKNOWN.

This matters because a WHERE clause returns only rows for which its condition is TRUE. Rows whose condition is FALSE or UNKNOWN are excluded.

Filtering with an Unknown Comparison

sql

1
2
3
SELECT employee_id, salary
FROM employees
WHERE salary > 50000;

An employee with salary NULL is not returned by this query because NULL > 50000 evaluates to UNKNOWN, not TRUE.

The Three Results

TRUE: the condition is satisfied. FALSE: the condition is not satisfied. UNKNOWN: the condition cannot be determined from the available values. WHERE keeps only TRUE.

4. Why NULL = NULL Is Not TRUE

A common beginner mistake is assuming that two NULL values must be equal. In SQL, NULL means that the ordinary value is unavailable or unknown. Comparing two unknown values does not establish equality.

In PostgreSQL, NULL = NULL evaluates to NULL, which represents UNKNOWN in this Boolean context. The same issue applies when comparing an ordinary value to NULL.

Comparisons Involving NULL

sql

1
2
3
4
5
SELECT
  NULL = NULL AS null_equals_null,
  NULL = 5 AS null_equals_five,
  NULL <> 5 AS null_not_equal_five,
  5 = 5 AS five_equals_five;

Output

PostgreSQL displays the unknown Boolean results as NULL. Only 5 = 5 is TRUE. Do not use ordinary equality or inequality operators to test for NULL.

5. Correctly Checking for NULL

Use IS NULL to find rows where a column contains NULL. Use IS NOT NULL to find rows where it contains a non-NULL value.

These predicates test whether the value is NULL; they do not compare the column with an ordinary value.

IS NULL and IS NOT NULL

sql

1
2
3
4
5
6
7
8
9
-- Find customers whose phone is missing
SELECT customer_id, first_name, phone
FROM customers
WHERE phone IS NULL;
 
-- Find customers whose phone is present
SELECT customer_id, first_name, phone
FROM customers
WHERE phone IS NOT NULL;

The first query returns only rows with NULL phone values. The second returns only rows with non-NULL phone values. An empty string is included by IS NOT NULL because it is a value, even though it contains no characters.

6. Why WHERE column = NULL Does Not Work

The expression column = NULL evaluates to UNKNOWN rather than TRUE when the column is NULL. Therefore, using it in WHERE does not select the missing values.

The same issue affects column <> NULL. Use IS NULL or IS NOT NULL instead.

Incorrect and Correct Queries

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Incorrect: does not select NULL email values
SELECT *
FROM users
WHERE email = NULL;
 
-- Correct
SELECT *
FROM users
WHERE email IS NULL;
 
-- Incorrect: does not select non-NULL email values
SELECT *
FROM users
WHERE email <> NULL;
 
-- Correct
SELECT *
FROM users
WHERE email IS NOT NULL;

Both ordinary comparisons with NULL produce UNKNOWN for these checks. IS NULL and IS NOT NULL are the correct predicates.

7. Truth Tables for AND, OR, and NOT

UNKNOWN also affects logical operators. Understanding these rules helps you predict which rows a WHERE clause returns.

Evaluating Logical Expressions

sql

1
2
3
4
5
6
SELECT
  (TRUE AND NULL) AS true_and_unknown,
  (FALSE AND NULL) AS false_and_unknown,
  (TRUE OR NULL) AS true_or_unknown,
  (FALSE OR NULL) AS false_or_unknown,
  NOT NULL AS not_unknown;

Output

In PostgreSQL, NULL can stand in for an unknown Boolean result in these expressions. The logical operators follow SQL's three-valued truth tables.

SQL Three-Valued Logic

Expression	Result	Reason
TRUE AND UNKNOWN	UNKNOWN	The unknown condition could change the final result
FALSE AND UNKNOWN	FALSE	One false condition makes the AND result false
TRUE OR UNKNOWN	TRUE	One true condition makes the OR result true
FALSE OR UNKNOWN	UNKNOWN	The unknown condition determines the final result
NOT UNKNOWN	UNKNOWN	Negating an unknown result remains unknown
TRUE AND TRUE	TRUE	Both conditions are true
FALSE OR FALSE	FALSE	Neither condition is true

8. NULL in Arithmetic Expressions

Arithmetic involving NULL generally produces NULL because the result cannot be determined from the missing input. This applies to addition, subtraction, multiplication, and division when the operation is otherwise valid.

If a product's discount is NULL, adding that discount to a price will not automatically treat the missing discount as zero. You must decide whether a fallback value is appropriate.

Arithmetic with NULL

sql

1
2
3
4
5
SELECT
  10 + NULL AS addition_result,
  10 - NULL AS subtraction_result,
  10 * NULL AS multiplication_result,
  10 / NULL AS division_result;

Output

These examples use valid numeric operations with NULL operands. Division by NULL returns NULL; division by zero is a different case and normally raises an error in PostgreSQL.

9. Replacing Missing Values with COALESCE

COALESCE evaluates its arguments from left to right and returns the first non-NULL argument. If every argument is NULL, the result is NULL.

It is useful for reports, display labels, and calculations where the application has a meaningful fallback. Choose the fallback carefully: a missing salary is not necessarily equivalent to a salary of zero.

Provide a Fallback Value

sql

1
2
3
4
5
SELECT
  customer_id,
  first_name,
  COALESCE(phone, 'No Phone on File') AS contact_phone
FROM customers;

Output

Robert receives the fallback text because phone is NULL. Carlos's empty string is not NULL, so COALESCE returns the empty string rather than the fallback.

10. COALESCE with Multiple Arguments

You can provide more than two arguments to COALESCE. SQL checks them from left to right and stops at the first non-NULL value.

Choose the First Available Phone Number

sql

1
2
3
4
5
6
7
8
9
SELECT
  customer_id,
  COALESCE(
    phone,
    mobile,
    work_phone,
    'No Phone on File'
  ) AS contact_phone
FROM customers;

If phone is available, it is selected. Otherwise, mobile is checked, then work_phone. The fallback text is returned only if all three columns are NULL.

11. COALESCE in Calculations

COALESCE can replace NULL with a chosen value before performing a calculation. This is appropriate only when the fallback reflects the business meaning of the data.

Calculate a Discounted Price

sql

1
2
3
4
5
6
SELECT
  product_id,
  price,
  discount_amount,
  price - COALESCE(discount_amount, 0) AS final_price
FROM products;

This treats a missing discount amount as zero. Use this rule only if NULL really means that no discount should be applied. If NULL means the discount is unknown, replacing it with zero may mislead users.

12. Understanding NULLIF

NULLIF(a, b) returns NULL if a and b compare equal; otherwise, it returns a. It is useful when a particular value should be treated as missing for a calculation.

For example, NULLIF(conversions, 0) turns a zero conversion count into NULL. This allows a later division to return NULL instead of attempting division by zero.

Basic NULLIF Examples

sql

1
2
3
4
SELECT
  NULLIF(5, 5) AS same_values,
  NULLIF(5, 3) AS different_values,
  NULLIF(0, 0) AS zero_values;

Output

same_values | different_values | zero_values ------------+------------------+------------ NULL | 5 | NULL

When the arguments compare equal, NULLIF returns NULL. Otherwise, it returns the first argument. If an argument is NULL, the equality comparison may be UNKNOWN, in which case NULLIF does not treat the comparison as equal.

13. Preventing Division by Zero with NULLIF

In PostgreSQL, dividing a numeric value by zero normally raises a division-by-zero error. NULLIF can prevent the operation by converting a zero denominator to NULL before division.

The resulting metric is NULL when the denominator is zero. This means the metric is undefined for that row; it does not mean the result is zero.

Calculate Cost per Conversion

sql

1
2
3
4
5
6
7
8
9
SELECT
  campaign_id,
  total_spend,
  conversions,
  ROUND(
    total_spend / NULLIF(conversions, 0),
    2
  ) AS cost_per_conversion
FROM marketing_campaigns;

Output

campaign_id | total_spend | conversions | cost_per_conversion ------------+-------------+-------------+-------------------- 101 | 1500.00 | 45 | 33.33 102 | 800.00 | 0 | NULL 103 | 2200.00 | 80 | 27.50

For campaign 102, NULLIF(0, 0) returns NULL. The division therefore returns NULL instead of raising a division-by-zero error. The sample output assumes compatible numeric column types.

14. Combining COALESCE and NULLIF

COALESCE and NULLIF solve different problems. NULLIF converts a specified value into NULL. COALESCE chooses a fallback when it encounters NULL.

You can combine them when the business rule requires it. For example, NULLIF can turn an empty string into NULL, and COALESCE can then return a fallback label. This is particularly useful when importing text data from systems that represent missing values as empty strings.

Treat Empty Text as Missing

sql

1
2
3
4
5
6
7
SELECT
  customer_id,
  COALESCE(
    NULLIF(email, ''),
    'No Email on File'
  ) AS email_display
FROM customers;

NULLIF(email, '') converts an empty string into NULL. COALESCE then returns the fallback label for either an empty string or a NULL email. This example assumes email is a text column.

15. NULL and Aggregate Functions

Many SQL aggregate functions ignore NULL inputs. For example, AVG(salary) calculates the average of non-NULL salary values rather than treating missing salaries as zero.

COUNT(*) counts rows, including rows whose columns contain NULL. COUNT(column) counts only rows where that column is not NULL. These differences are important when reporting totals and averages.

COUNT(*) Versus COUNT(column)

sql

1
2
3
4
5
SELECT
  COUNT(*) AS total_employees,
  COUNT(salary) AS employees_with_salary,
  AVG(salary) AS average_known_salary
FROM employees;

Output

total_employees | employees_with_salary | average_known_salary ----------------+-----------------------+---------------------- 5 | 3 | 60000.00

The illustrative result assumes five employees, three non-NULL salaries, and an average of 60000 across those three salaries. COUNT(*) counts all rows; COUNT(salary) ignores NULL salaries; AVG(salary) averages the non-NULL salary values.

16. NULL and GROUP BY

When grouping rows, SQL groups NULL values together into one group for GROUP BY purposes. This allows you to count records with missing values separately from records with known values.

Count Customers by Phone Value

sql

1
2
3
4
5
6
SELECT
  phone,
  COUNT(*) AS customer_count
FROM customers
GROUP BY phone
ORDER BY phone NULLS LAST;

Rows whose phone value is NULL form one group. In PostgreSQL, NULLS LAST places that group at the end for this ascending ordering. An empty string is a separate group.

17. NULL and LEFT JOIN

A LEFT JOIN returns all rows from the left table, even when there is no matching row in the right table. Columns from the unmatched right side are filled with NULL in the result.

These NULL values do not necessarily mean that the stored right-table columns themselves contain NULL. They may indicate that no matching right-table row exists.

Find Customers Without Orders

sql

1
2
3
4
5
6
7
8
SELECT
  c.customer_id,
  c.first_name,
  o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Assuming order_id is a non-NULL primary key, this query returns customers with no matching order. The NULL order_id is produced by the unmatched side of the LEFT JOIN.

18. NULL and NOT IN

NULL can cause surprising behavior with NOT IN. If the list or subquery contains NULL, a value that does not match any known list value may produce UNKNOWN rather than TRUE. Since WHERE keeps only TRUE, the expected rows can disappear.

When checking that no matching row exists, NOT EXISTS is often easier to reason about, especially if the subquery column may contain NULL.

A NOT IN Trap

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Potentially surprising when the subquery returns NULL
SELECT customer_id
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id
  FROM blocked_customers
);
 
-- Often safer for checking that no match exists
SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM blocked_customers AS b
  WHERE b.customer_id = c.customer_id
);

The NOT EXISTS version tests for the absence of a matching row. It avoids the specific NOT IN problem caused by NULL values in the subquery result. If c.customer_id itself is NULL, the equality predicate still does not match, so handle that case separately if needed.

19. NULL and Constraints

A column can allow or disallow NULL values depending on its schema. NOT NULL prevents a column from storing NULL. A PRIMARY KEY is both unique and non-NULL. A UNIQUE constraint has NULL behavior that depends on the database and constraint definition; in PostgreSQL, a standard UNIQUE constraint normally allows multiple NULL values.

A CHECK constraint does not automatically reject NULL. In PostgreSQL, a CHECK constraint rejects a row when its condition is FALSE, but a NULL/UNKNOWN result passes the check. Use NOT NULL separately if the column must contain a value.

Combine NOT NULL with CHECK

sql

1
2
3
4
5
CREATE TABLE employees_example (
  employee_id INTEGER PRIMARY KEY,
  salary NUMERIC(10, 2) NOT NULL,
  CHECK (salary >= 0)
);

NOT NULL requires a salary value, while CHECK (salary >= 0) rejects negative salaries. Without NOT NULL, a NULL salary could pass the CHECK constraint in PostgreSQL.

20. Common Mistakes

Avoid these mistakes when working with missing data.

Incorrect and Correct Patterns

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- Mistake 1: Comparing with = NULL
SELECT *
FROM users
WHERE email = NULL;
 
-- Correct
SELECT *
FROM users
WHERE email IS NULL;
 
 
-- Mistake 2: Comparing with <> NULL
SELECT *
FROM users
WHERE email <> NULL;
 
-- Correct
SELECT *
FROM users
WHERE email IS NOT NULL;
 
 
-- Mistake 3: Assuming NULL equals zero
SELECT price + COALESCE(discount_amount, 0)
FROM products;
 
-- Use this only when NULL means no discount.
 
 
-- Mistake 4: Dividing directly by a possibly zero value
SELECT total_spend / conversions
FROM marketing_campaigns;
 
-- Safer for a zero denominator in PostgreSQL
SELECT total_spend / NULLIF(conversions, 0)
FROM marketing_campaigns;
 
 
-- Mistake 5: Assuming empty text is NULL
SELECT *
FROM customers
WHERE email IS NULL;
 
-- If empty strings also represent missing data:
SELECT *
FROM customers
WHERE email IS NULL OR email = '';

Use the correct NULL predicates, make fallback assumptions explicit, and distinguish empty text from a missing value.

21. Guided Practice

Try each exercise before checking the solutions. Assume the listed tables and columns exist.

Practice Questions

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Task 1:
-- Find employees whose salary is NULL.
 
-- Task 2:
-- Find customers whose phone is not NULL.
 
-- Task 3:
-- Display 'Unknown' when a customer's city is NULL.
 
-- Task 4:
-- Calculate price minus discount_amount,
-- treating a NULL discount as zero.
 
-- Task 5:
-- Calculate revenue per order only when quantity
-- is not zero, returning NULL when quantity is zero.
 
-- Task 6:
-- Display 'No Email' for both NULL and empty
-- email strings.
 
-- Task 7:
-- Count all employees and employees whose
-- salary is not NULL.
 
-- Task 8:
-- Find customers with no matching orders
-- using a LEFT JOIN.

Focus on selecting the right tool for each situation: IS NULL, IS NOT NULL, COALESCE, NULLIF, COUNT, or LEFT JOIN.

22. Practice Solutions

Use these solutions to check your work.

Solution Queries

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- Task 1
SELECT employee_id, salary
FROM employees
WHERE salary IS NULL;
 
-- Task 2
SELECT customer_id, phone
FROM customers
WHERE phone IS NOT NULL;
 
-- Task 3
SELECT
  customer_id,
  COALESCE(city, 'Unknown') AS city_display
FROM customers;
 
-- Task 4
SELECT
  product_id,
  price - COALESCE(discount_amount, 0) AS final_price
FROM products;
 
-- Task 5
SELECT
  order_id,
  revenue / NULLIF(quantity, 0) AS revenue_per_unit
FROM order_items;
 
-- Task 6
SELECT
  customer_id,
  COALESCE(NULLIF(email, ''), 'No Email') AS email_display
FROM customers;
 
-- Task 7
SELECT
  COUNT(*) AS total_employees,
  COUNT(salary) AS employees_with_salary
FROM employees;
 
-- Task 8
SELECT c.customer_id, c.first_name
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Task 5 assumes revenue and quantity are compatible numeric types. The LEFT JOIN solution assumes order_id is a non-NULL key for matched orders.

23. Multiple-Choice Questions

Choose the best answer for each question before checking the answer key.

Questions 1–5

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Q1. What does NULL usually represent?
-- A. Zero
-- B. Empty text
-- C. Missing or unknown information
-- D. FALSE
 
-- Q2. What is the result of NULL = NULL?
-- A. TRUE
-- B. FALSE
-- C. UNKNOWN
-- D. Zero
 
-- Q3. Which predicate checks for NULL?
-- A. = NULL
-- B. IS NULL
-- C. == NULL
-- D. LIKE NULL
 
-- Q4. What does COALESCE( NULL, 7, 9 ) return?
-- A. NULL
-- B. 7
-- C. 9
-- D. An error
 
-- Q5. What does NULLIF(5, 5) return?
-- A. 5
-- B. TRUE
-- C. FALSE
-- D. NULL

The questions test the meaning of NULL, SQL's three-valued logic, and the basic behavior of COALESCE and NULLIF.

24. Quiz Answer Key

Check your answers and make sure you understand why each answer is correct.

Questions 6–12 and Answers

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-- Q6. Which rows does WHERE keep?
-- A. TRUE only
-- B. FALSE only
-- C. UNKNOWN only
-- D. TRUE and UNKNOWN
-- Answer: A
 
-- Q7. What is FALSE AND UNKNOWN?
-- A. TRUE
-- B. FALSE
-- C. UNKNOWN
-- D. NULL error
-- Answer: B
 
-- Q8. What is TRUE OR UNKNOWN?
-- A. TRUE
-- B. FALSE
-- C. UNKNOWN
-- D. NULL error
-- Answer: A
 
-- Q9. What does COUNT(column) count?
-- A. All rows, including NULL values
-- B. Only NULL values
-- C. Rows where column is not NULL
-- D. Unique rows only
-- Answer: C
 
-- Q10. What does COUNT(*) count?
-- A. Only non-NULL values in one column
-- B. All rows in the input
-- C. Only distinct rows
-- D. Only rows containing NULL
-- Answer: B
 
-- Q11. What does NULLIF(0, 0) return?
-- A. 0
-- B. FALSE
-- C. NULL
-- D. An empty string
-- Answer: C
 
-- Q12. Why use NULLIF(quantity, 0) as a divisor?
-- A. It converts zero to one
-- B. It converts zero to NULL
-- C. It removes the row
-- D. It changes the quantity column permanently
-- Answer: B

Remember that COUNT(*) counts rows, COUNT(column) ignores NULL values, and NULLIF changes the expression result rather than modifying stored data.

25. SQL Interview Questions and Answers

Practice explaining each answer in your own words and writing a short SQL example where appropriate.

Core Interview Questions

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-- Q1. What is NULL?
-- NULL represents missing, unknown, or inapplicable data.
 
-- Q2. Is NULL equal to zero?
-- No. Zero is a numeric value; NULL represents missing data.
 
-- Q3. Is NULL the same as an empty string?
-- No. An empty string is a text value with no characters.
 
-- Q4. What is SQL three-valued logic?
-- SQL conditions can evaluate to TRUE, FALSE, or UNKNOWN.
 
-- Q5. Why does NULL = NULL not return TRUE?
-- Ordinary comparisons involving NULL evaluate to UNKNOWN.
 
-- Q6. How do you check for missing values?
-- Use IS NULL or IS NOT NULL.
 
-- Q7. What does COALESCE do?
-- It returns the first non-NULL argument.
 
-- Q8. What does NULLIF do?
-- It returns NULL when its two arguments compare equal;
-- otherwise, it returns the first argument.
 
-- Q9. How can you avoid division by zero?
-- Use NULLIF on a denominator that may be zero.
 
-- Q10. What is the difference between COUNT(*) and COUNT(col)?
-- COUNT(*) counts rows; COUNT(col) counts non-NULL values.
 
-- Q11. Do aggregate functions generally ignore NULL?
-- Most common aggregates ignore NULL inputs, but COUNT(*)
-- counts rows and different aggregates have different rules.
 
-- Q12. What does a LEFT JOIN return when no row matches?
-- Columns from the unmatched right side are filled with NULL.
 
-- Q13. Why can NOT IN behave unexpectedly with NULL?
-- A NULL in the list or subquery can make a non-match
-- evaluate to UNKNOWN rather than TRUE.
 
-- Q14. How can COALESCE and NULLIF be combined?
-- NULLIF can turn a specified value, such as an empty
-- string, into NULL; COALESCE can then supply a fallback.
 
-- Q15. Does a CHECK constraint automatically reject NULL?
-- Not in PostgreSQL. A CHECK passes when its result is
-- TRUE or UNKNOWN; use NOT NULL when required.
 
-- Q16. Does GROUP BY put NULL values into a group?
-- Yes. NULL grouping values are grouped together.
 
-- Q17. Can NULL be used in a primary key?
-- No. Primary-key columns must be unique and non-NULL.
 
-- Q18. Should every NULL be replaced with zero?
-- No. Choose a fallback only when it matches the
-- meaning of the missing data.

Interviewers often test whether you understand the difference between missing data and a real value, rather than just remembering the syntax.

26. Lesson Summary

NULL represents missing, unknown, or inapplicable information. It is not zero, an empty string, or FALSE. SQL uses three-valued logic, so comparisons involving NULL usually produce UNKNOWN. A WHERE clause returns only rows whose condition is TRUE.

Use IS NULL and IS NOT NULL to test for missing values. Use COALESCE to select the first non-NULL value, and NULLIF to return NULL when two arguments compare equal. A common safety pattern is denominator NULLIF(value, 0), which prevents division by zero by making the denominator NULL when it is zero.

Remember that aggregate functions, joins, grouping, constraints, and NOT IN all have important NULL-related behavior. Choose fallback values based on the meaning of the data, and test edge cases instead of assuming missing values behave like ordinary values.

27. Final Checklist

Before moving to the next lesson, make sure you can:

- Explain the difference between NULL, zero, an empty string, and FALSE.
- Explain TRUE, FALSE, and UNKNOWN.
- Predict how NULL affects comparisons and logical operators.
- Write IS NULL and IS NOT NULL predicates.
- Use COALESCE with one or more fallback values.
- Use NULLIF to handle zero denominators.
- Explain COUNT(*) versus COUNT(column).
- Identify NULL values introduced by a LEFT JOIN.
- Explain why NOT IN can behave unexpectedly when NULL is present.
- Recognize why NOT NULL may be needed alongside a CHECK constraint.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Checking for NULL with the equals operator

Why it happens: Developers assume that because column = 5 works, column = NULL should also work.

Incorrect Approach

sql

SELECT * FROM users WHERE email = NULL;

Correct Solution

sql

SELECT * FROM users WHERE email IS NULL;

Key takeaway: Ordinary comparisons involving NULL evaluate to UNKNOWN. Use IS NULL or IS NOT NULL to test for missing values.

#2 Treating NULL as zero

Why it happens: A missing numeric value can look like a zero in a report, but the meanings are different.

Incorrect Approach

sql

SELECT AVG(COALESCE(salary, 0)) FROM employees;

Correct Solution

sql

SELECT AVG(salary) FROM employees;

Key takeaway: Use the second query when the goal is to average recorded salaries while ignoring missing ones. Replacing missing salaries with zero changes the calculation and should be done only when the business rule explicitly requires it.

#3 Assuming an empty string is NULL

Why it happens: Both values can appear to mean that no text was provided.

Incorrect Approach

sql

SELECT * FROM customers WHERE email IS NULL;

Correct Solution

sql

SELECT * FROM customers WHERE email IS NULL OR email = '';

Key takeaway: The second query finds both NULL and empty-string values in PostgreSQL. Use it only if the application treats both as missing.

#4 Dividing by a value that can be zero

Why it happens: A denominator may be zero even when the column is declared NOT NULL.

Incorrect Approach

sql

SELECT total_spend / conversions FROM marketing_campaigns;

Correct Solution

sql

SELECT total_spend / NULLIF(conversions, 0) FROM marketing_campaigns;

Key takeaway: NULLIF turns a zero denominator into NULL, so the division returns NULL instead of raising a division-by-zero error in PostgreSQL.

#5 Assuming NOT IN is always safe with NULL values

Why it happens: A subquery containing NULL can cause a comparison to evaluate to UNKNOWN.

Incorrect Approach

sql

SELECT customer_id FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM blocked_customers);

Correct Solution

sql

SELECT c.customer_id FROM customers AS c WHERE NOT EXISTS (SELECT 1 FROM blocked_customers AS b WHERE b.customer_id = c.customer_id);

Key takeaway: NOT EXISTS avoids the specific NULL-in-the-subquery issue with NOT IN. If the outer customer_id itself can be NULL, decide explicitly whether such rows should be included.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

NULL represents missing, unknown, or inapplicable information; it is not zero, an empty string, or FALSE.
SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN.
A WHERE clause keeps rows only when its condition evaluates to TRUE.
Use IS NULL and IS NOT NULL to test whether a value is NULL.
Ordinary comparisons with NULL usually produce UNKNOWN.
COALESCE returns the first non-NULL expression.
NULLIF returns NULL when its two arguments compare equal; otherwise, it returns the first argument.
Division by NULL produces NULL, while division by zero normally raises an error in PostgreSQL.
Most aggregate functions ignore NULL inputs, but COUNT(*) counts rows and COUNT(column) counts non-NULL values.
Replacing NULL with a default is a business decision, not just a formatting choice.

Was this lesson helpful?

Found an error? Report an issue