Data Types & NULL Handling

Overview

NULL is not a value — it means 'unknown' or 'missing'. It is NOT the same as zero, an empty string, or false. This distinction is the single most important thing to understand about NULL. SQL uses three-valued logic: every comparison can be TRUE, FALSE, or NULL. Any arithmetic or comparison involving NULL produces NULL: 5 > NULL → NULL (not FALSE) NULL = NULL → NULL (not TRUE) NULL <> NULL → NULL (not TRUE) Because WHERE keeps only TRUE rows, a NULL result means the row is silently dropped — the most common source of 'missing rows' bugs. To test for NULL, use IS NULL and IS NOT NULL — never = NULL: SELECT name, country FROM customers WHERE country IS NULL ORDER BY name; Returns 1 row: Emma (country is NULL). COALESCE returns the first non-NULL argument — a fallback for missing data: SELECT name, COALESCE(country, 'Unknown') AS country FROM customers ORDER BY id; Returns 8 rows. Emma's row shows 'Unknown' instead of NULL. NULLIF returns NULL if two arguments are equal — useful for avoiding division by zero: SELECT NULLIF(0, 0); Returns NULL (since both are 0). PostgreSQL common data types: INTEGER (whole numbers), NUMERIC/DECIMAL (exact decimals like prices), TEXT/VARCHAR (strings), BOOLEAN (true/false), DATE (calendar dates), TIMESTAMP (date + time). Every type can hold NULL — it represents the absence of any value regardless of the column's type. Aggregate functions (COUNT, SUM, AVG) skip NULLs except COUNT(*), which counts rows regardless: SELECT COUNT(*) AS total_rows, COUNT(country) AS has_country FROM customers; Returns total_rows = 8, has_country = 7 (Emma's NULL country is skipped by COUNT(country)). Mental model: NULL is a 'fog' — any operation that touches it produces fog. IS NULL is the only flashlight that can see through it. COALESCE is a default value that replaces the fog with something concrete.

Mishandling NULLs causes silent data loss in queries. Rows vanish from WHERE filters, JOINs drop unmatched records, and aggregations skip values without warning. Understanding three-valued logic is prerequisite to writing correct LEFT JOINs and safe analytics.

Where used: handling optional fields (nullable columns) in REST APIs, data cleaning and ETL pipelines, COALESCE for default display values in reports

Why learn this

• LEFT JOINs produce NULL-padded rows — without understanding NULL, you cannot filter or aggregate join results correctly
• API responses with optional fields map directly to nullable columns — COALESCE translates NULLs to user-friendly defaults

Query walkthrough

SELECT name, COALESCE(country, 'Unknown') AS country
FROM customers
WHERE country IS NULL
ORDER BY name;

Focus: Watch WHERE use IS NULL to find Emma — the only customer with a missing country — then COALESCE replace the NULL with 'Unknown' in the output.

Common mistakes

• Writing WHERE column = NULL: NULL = NULL evaluates to NULL, not TRUE, so this condition matches zero rows. Always use IS NULL to test for NULL.
• Expecting NOT IN to work with NULLs in the list: If the IN list contains a NULL, NOT IN returns NULL for every comparison against it, which means no rows pass. Use NOT EXISTS or filter NULLs from the subquery.
• Confusing COUNT(*) and COUNT(column): COUNT(*) counts all rows including those with NULLs. COUNT(column) counts only non-NULL values in that column. Using the wrong one gives wrong totals.
• Treating NULL as an empty string or zero: NULL is not '' or 0 — it is the absence of any value. Concatenating a string with NULL yields NULL, and adding a number to NULL yields NULL.

Glossary

three-valued

A logic system in SQL where a condition can be true, false, or unknown (NULL), rather than just true or false.

concrete

A specific, actual value rather than something unknown or missing, like returning a default string instead of a NULL.

fallback

A default option or alternative plan that is used when the primary choice is missing or fails.

Recall questions

• What does NULL = NULL evaluate to, and why?
• How do you test whether a column contains NULL?
• What does COALESCE(country, 'Unknown') do?
• What is the difference between COUNT(*) and COUNT(column)?

Understanding checks

Does Emma appear in the results? How many rows come back?

Emma does NOT appear. 4 rows: Carlos (MX), Diana (UK), Farah (AE), Hana (JP).

Emma's country is NULL. NULL <> 'US' evaluates to NULL (not TRUE), so WHERE discards Emma's row. This is the classic 'silently lost row' bug — three-valued logic means NULL fails both = and <> tests.

What rows and values does this return?

2 rows: Emma (N/A), Hana (JP).

IS NULL catches Emma (her NULL country becomes 'N/A' via COALESCE). country = 'JP' catches Hana (her country is not NULL, so COALESCE returns 'JP' unchanged).

Practice tasks

Show all customers, replacing NULL country

This query only shows customers with NULL country. Modify it to show ALL customers, but still replace NULL countries with 'Unknown' using COALESCE. Remove the WHERE clause and keep the ordering.

Challenge

Count customers with and without a country

Write a single query that returns two numbers: total_customers (all 8) and has_country (those with a non-NULL country). Use COUNT(*) and COUNT(country) in the same SELECT.

Continue learning

Previous: WHERE & Operators

Next: LEFT / RIGHT JOIN

Return to SQL Roadmap