Most questions you ask a table are about some of its rows: the orders placed this month, the employees in one department, the products under a price. WHERE is how a query says which. It is the most-used clause after SELECT and FROM, and the one where correctness most often slips — through a comparison with NULL, a text column compared as a number, or a date range that misses its last day. This lesson covers the comparison operators, comparing numbers, text and dates, the NULL rule, and why filtering in WHERE is the cheapest thing a query does.

The shape

SELECT name, salary
FROM employees
WHERE salary >= 60000
ORDER BY salary DESC, name;

WHERE comes after FROM and before ORDER BY (and GROUP BY, once you meet it). Logically it runs second: FROM produces the rows, WHERE tests its condition against each one, and only rows where the condition is true move on to SELECT. The condition is any expression that yields true, false or unknown — usually a comparison.

Comparison operators

Note the single =: SQL has no == (SQLite tolerates it; MySQL does not). <> is the standard spelling of "not equal" and != an alternative every major database accepts.

Both sides can be expressions:

SELECT name FROM order_items WHERE price * qty > 1000;
SELECT name FROM employees WHERE salary * 12 >= 1000000;

Column against column works too: WHERE shipped_on > promised_on finds late deliveries.

Comparing numbers, text and dates

Numbers compare numerically. Keep literals unquoted: WHERE year = 2. MySQL would convert '2' to a number for you, but quoting numbers hides type mistakes, and comparing a text column with a number makes MySQL convert every row's text to a number — '12abc' becomes 12 — and prevents an index from being used.

Text compares by the column's collation — its alphabet. = tests equality; < and > compare in dictionary order:

SELECT name FROM students WHERE name < 'M';    -- names before M in the alphabet

In MySQL the default collations are case-insensitive and ignore trailing spaces, so WHERE city = 'pune' also finds 'Pune'. PostgreSQL and SQLite compare case-sensitively. Store values consistently and don't let a query's correctness depend on the difference.

Dates compare chronologically. Write a date literal as a string in ISO format, 'YYYY-MM-DD', and MySQL converts it:

SELECT order_id, order_date
FROM orders
WHERE order_date >= '2024-03-01'
ORDER BY order_date, order_id;

ISO strings also sort correctly as text, which is why the format matters: '2024-03-01' < '2024-12-01' is true either way, while '01/03/2024' versus '12/01/2024' would compare as text in the wrong order. Never store or compare dates in a day-first or month-first text format.

On a DATETIME column, WHERE created_at = '2024-03-01' matches only midnight. A whole day is a half-open range:

WHERE created_at >= '2024-03-01' AND created_at < '2024-03-02'

That form also handles months of any length (>= '2024-02-01' AND < '2024-03-01') without knowing whether February has 28 or 29 days.

NULL is never equal to anything

The rule that catches everyone: a comparison with NULL is neither true nor false — it is unknown (NULL), and WHERE keeps only rows where the condition is true.

SELECT name FROM employees WHERE manager_id = NULL;     -- always zero rows
SELECT name FROM employees WHERE manager_id IS NULL;    -- the employees with no manager
SELECT name FROM employees WHERE manager_id IS NOT NULL;

= NULL is never true, not even for rows whose value is NULL — "is this unknown value equal to that unknown value?" has no answer. IS NULL and IS NOT NULL are the tests for presence.

The same rule hides in ordinary conditions. Given three employees, one with no department: