NULL means unknown / missing. It is not zero, not an empty string, and not equal to anything — not even another NULL.

Comparisons with NULL

SQL

SELECT NULL = NULL      AS eq,
       NULL <> 1        AS ne,
       NULL IS NULL     AS is_null,
       1 IS NOT NULL    AS is_not_null,
       NULL + 5         AS arithmetic,
       'a' || NULL      AS concat;

Output

eq   | ne   | is_null | is_not_null | arithmetic | concat
-----+------+---------+-------------+------------+-------
NULL | NULL | 1       | 1           | NULL       | NULL

(SQLite shows TRUE/FALSE as 1/0 and UNKNOWN as NULL.) NULL propagates through arithmetic and concatenation — one missing value makes the whole expression NULL.

SQL

SELECT name FROM employees WHERE email = NULL;        -- always empty!
SELECT name FROM employees WHERE email IS NULL ORDER BY name;

Output

name
----

name
----
Dev
Neha

Truth tables

`AND`	TRUE	FALSE	UNKNOWN
TRUE	TRUE	FALSE	UNKNOWN
FALSE	FALSE	FALSE	FALSE
UNKNOWN	UNKNOWN	FALSE	UNKNOWN

`OR`	TRUE	FALSE	UNKNOWN
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	UNKNOWN
UNKNOWN	TRUE	UNKNOWN	UNKNOWN

NOT UNKNOWN is still UNKNOWN. So a row with a NULL column fails both x = 5 and NOT (x = 5):

SQL

SELECT COUNT(*) AS total,
       SUM(dept_id = 1)                   AS in_eng,
       SUM(NOT (dept_id = 1))             AS not_in_eng,
       SUM(dept_id IS NULL)               AS no_dept
FROM employees;

Output

total | in_eng | not_in_eng | no_dept
------+--------+------------+--------
10    | 4      | 5          | 1

The NOT IN trap

SQL

-- customers outside Mumbai and Delhi (Kavya's city is NULL)
SELECT name FROM customers WHERE city NOT IN ('Mumbai', 'Delhi') ORDER BY name;
SELECT 'x' AS found WHERE 3 NOT IN (1, 2, NULL);

Output

name
-----
Sneha

found
-----

NULL-handling functions

Function	Returns	Portability
`COALESCE(a, b, c…)`	First non-`NULL` argument	Standard — everywhere
`NULLIF(a, b)`	`NULL` if `a = b`, else `a`	Standard — everywhere
`IFNULL(a, b)`	`b` if `a` is `NULL`	MySQL, SQLite
`ISNULL(a, b)` / `NVL(a, b)`	Same idea	SQL Server / Oracle
`a IS DISTINCT FROM b`	Null-safe `<>` (`NULL` vs `NULL` → false)	Standard; PostgreSQL, SQL Server 2022, SQLite (`IS NOT`)

SQL

SELECT name,
       COALESCE(email, 'no email')          AS contact,
       COALESCE(dept_id, 0)                 AS dept
FROM employees
WHERE emp_id IN (6, 7, 10)
ORDER BY emp_id;

-- NULLIF avoids division by zero: x / 0 → x / NULL → NULL
SELECT 100 / NULLIF(0, 0) AS safe_div;

-- null-safe equality (SQLite: IS / IS NOT; standard: IS [NOT] DISTINCT FROM)
SELECT NULL IS NULL AS both_null, 1 IS NOT NULL AS differ, NULL IS 1 AS null_vs_1;

Output

name  | contact       | dept
------+---------------+-----
Arjun | arjun@corp.in | 2
Neha  | no email      | 2
Dev   | no email      | 0

safe_div
--------
NULL

both_null | differ | null_vs_1
----------+--------+----------
1         | 1      | 0

NULL in aggregates, sorting and grouping

Aggregates ignore NULLs: COUNT(email) counts non-null emails; AVG divides by the non-null count.
COUNT(*) counts rows, whatever they contain.
GROUP BY and DISTINCT put all NULLs into one group.
ORDER BY puts NULLs first or last depending on the engine.
UNIQUE allows multiple NULLs (except SQL Server).

SQL

SELECT COUNT(*) AS all_rows, COUNT(email) AS with_email, COUNT(dept_id) AS with_dept
FROM employees;

Output

all_rows | with_email | with_dept
---------+------------+----------
10       | 8          | 9

What is the difference between NULL, 0 and an empty string?

Why does WHERE col <> 'x' not return rows where col is NULL?