beginnerCore SQL100% Free

SQL NULL Handling

Definition

NULL in SQL represents the absence of a value or unknown data, and it behaves differently from zero or empty string — NULL is not equal to anything, including itself.

Introduction to SQL NULL Handling

NULL is one of the most misunderstood concepts in SQL. It doesn't mean zero, empty string, or false — it means 'unknown' or 'not applicable'. This unique three-valued logic (TRUE, FALSE, NULL) affects comparisons, aggregations, JOINs, and more. Mastering NULL handling prevents subtle bugs in production queries.

Syntax

SQL

-- Check for NULL
WHERE column IS NULL
WHERE column IS NOT NULL

-- Replace NULL with a default value
SELECT COALESCE(column, 'default') FROM table;

-- Return NULL if two values are equal
SELECT NULLIF(column, 0) FROM table;

Examples

COALESCE — First Non-NULL Value

SELECT
  name,
  COALESCE(phone, mobile, email, 'No contact') AS contact
FROM customers;

COALESCE returns the first non-NULL value from its arguments. Falls back through phone → mobile → email → 'No contact'.

Try in Playground

NULL in JOINs

-- Rows with NULL dept_id are excluded from INNER JOIN
SELECT e.name, d.name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- LEFT JOIN preserves employees with NULL dept_id
SELECT e.name, COALESCE(d.name, 'Unassigned') AS dept
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

NULL dept_id values can never match any department in an INNER JOIN (NULL ≠ NULL). Use LEFT JOIN + COALESCE to handle employees without departments.

Try in Playground

Common Mistakes

Using = NULL instead of IS NULL — always false because NULL is not equal to NULL

COUNT(column) instead of COUNT(*) when column has NULLs — silently undercounts

Arithmetic with NULL — any calculation involving NULL returns NULL (5 + NULL = NULL)

Sorting NULLs unexpectedly — NULLs sort first in some databases, last in others; use NULLS FIRST/LAST

Frequently Asked Questions

What is NULL in SQL?

NULL represents missing, unknown, or not-applicable data. It is NOT the same as zero, false, or empty string. NULL has unique behavior: comparisons with NULL return NULL (unknown), not TRUE or FALSE. This is why = NULL never works — use IS NULL instead.

What is COALESCE in SQL?

COALESCE(val1, val2, ..., valN) returns the first non-NULL value from its argument list. It's the standard SQL way to provide fallback values: COALESCE(discount, 0) returns the discount if it exists, or 0 if it's NULL. Available in all major databases.

How does NULL affect SQL aggregate functions?

Aggregate functions (SUM, AVG, MIN, MAX, COUNT) ignore NULL values. COUNT(*) counts all rows; COUNT(col) skips NULLs. AVG divides by non-NULL count only. This can produce misleading statistics — if you want NULLs treated as zero, use COALESCE(col, 0).

What is the difference between NULL and empty string in SQL?

NULL means 'no value / unknown'. Empty string ('') is an actual value — a string of zero length. They are stored differently, compare differently ('' = '' is TRUE; NULL = NULL is NULL/unknown), and affect functions differently. Design choice: use NULL for truly missing data, empty string only when the value is known to be an empty string.

Practice This in the SQL Playground

Write real queries, see live results, and master SQL NULL Handling hands-on. 100% free.

Open SQL Playground Browse All Lessons