WHERE and Comparison Operators

SELECT chooses columns. WHERE chooses rows. Almost every real query has one.

SQL▶ Run
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 90000
ORDER BY salary DESC;

Three people. The database looks at each of the ten employee rows, evaluates salary > 90000, and keeps the row only when the answer is true.

The operators

<> is the standard form and works everywhere; != is accepted by every mainstream database but is not in the standard. Pick one and be consistent.

Note there is no == in SQL. Equality is a single =. If you come from a programming language, this is the typo you will make for about a week.

Comparison is not only for numbers

Text compares alphabetically and dates compare chronologically:

SQL▶ Run
SELECT first_name, hired_on
FROM employees
WHERE hired_on >= '2022-01-01'
ORDER BY hired_on;

This works because the dates are stored in YYYY-MM-DD order, where alphabetical order and chronological order happen to agree. That is the entire reason ISO 8601 is the format to store dates in. '03/01/2024' would sort between February and April of every year.

Text comparison is case-sensitive — sometimes

SQL▶ Run
SELECT name, country FROM customers WHERE name = 'Acme Ltd';

One row, even though the table holds both Acme Ltd and ACME LTD. Whether that is what you wanted depends on the database:

This is a genuine portability trap: the same query returns one row on Postgres and two on MySQL. When case must not matter, say so explicitly:

SQL▶ Run
SELECT customer_id, name FROM customers WHERE LOWER(name) = 'acme ltd';

Two rows — the duplicate customer the dataset deliberately contains. Being explicit costs one function call and removes the ambiguity everywhere.

The trap: <> silently drops NULLs

SQL▶ Run
SELECT COUNT(*) AS not_in_engineering
FROM employees
WHERE dept_id <> 1;

Five. There are ten employees and four are in department 1, so you would expect six. The missing row is the employee whose dept_id is NULL: NULL <> 1 is not true, and WHERE keeps only rows where the condition is true.

To include them you must say so:

SQL▶ Run
SELECT COUNT(*) AS not_in_engineering
FROM employees
WHERE dept_id <> 1 OR dept_id IS NULL;

Six. This is the single most common source of quietly wrong SQL, and the next few lessons are largely about it.

Common mistakes

• == instead of = — SQL has one equals sign.
• = NULL — never true, never matches. Use IS NULL.
• Assuming text comparison ignores case — it depends on the database.
• Forgetting NULL when negating — <> excludes unknowns.

Interview question

WHERE dept_id <> 1 returns fewer rows than you expect. Why?

Rows with NULL in dept_id are excluded, because comparing anything to NULL yields unknown rather than true.

Check yourself

1. Write a query for employees hired before 2020.
2. Why does WHERE salary = NULL return nothing?
3. Which databases compare text case-insensitively by default?