-- Basic NULL behavior comparison

SELECT

COUNT(*) AS total_employees,

COUNT(bonus) AS employees_with_bonus,

SUM(bonus) AS total_bonus_paid,

AVG(bonus) AS avg_bonus_excluding_null,

AVG(COALESCE(bonus, 0)) AS avg_bonus_treating_null_as_zero

FROM employees;

COUNT(*) includes all rows regardless of NULL values

COUNT(column) counts only non-NULL values in that column

SUM, AVG, MIN, MAX completely ignore NULL values

When ALL values are NULL, aggregates return NULL (except COUNT which returns 0)

AVG does NOT treat NULL as zero - it averages only non-NULL values

NULL means "unknown/missing" - not zero, not empty

Standard NULL handling in aggregates

Standard NULL handling in aggregates

Standard NULL handling in aggregates

Core references in this topic include WHERE, =, OR. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value

Use with exact matches. Do not use = NULL.

OR

Matches rows when at least one condition is TRUE.

condition_a OR condition_b

Use parentheses when mixing OR with AND.

IS NULL / IS NOT NULL

Tests whether a value is missing. SQL NULL semantics require dedicated NULL predicates.

manager_id IS NULL

Never use = NULL or != NULL.

LIKE

Pattern-matching operator for wildcard string searches.

name LIKE 'Joh%'

ANY / ALL

Compares one value against every or at least one value from a subquery result.

salary > ALL (SELECT salary FROM interns)