mindixx
mindixx

Reference · module 2

WHERE

Everything this module explains, on one page.

WHERE

Keep only the rows that match a condition.

  • WHERE keeps only the rows that match a condition: SELECT title FROM books WHERE year > 1950;. Rows that fail the condition are left out of the result entirely.
  • SQL compares with =, <>, <, >, <= and >=. Equality is a single = and not-equal is <>; most databases, SQLite included, also accept != for not-equal.
  • Text is compared in single quotes: WHERE author = 'Austen'. The parts of a query have a fixed order: SELECT, then FROM, then WHERE — the database refuses any other.

The mistake you are about to make

WHERE author = Austen

WHERE author = 'Austen'

The author's name feels like a value the database will understand. Without quotes Austen is a column name, and the query fails with no such column: Austen.

AND, OR, IN, BETWEEN

Combine conditions without being surprised by the order they run in.

  • AND needs both conditions, OR needs at least one: WHERE author = 'Austen' AND year > 1816 keeps only Austen's later books. NOT flips a condition.
  • AND binds tighter than OR, just as multiplication binds tighter than addition. a OR b AND c means a OR (b AND c), so add brackets when you mean something else.
  • IN checks a list: WHERE city IN ('Oslo', 'Rome') is shorter than two ORs. BETWEEN checks a range with both edges included: WHERE year BETWEEN 1800 AND 1900.

The mistake you are about to make

WHERE city = 'Oslo' OR city = 'Rome' AND price < 5

WHERE (city = 'Oslo' OR city = 'Rome') AND price < 5

The condition reads left to right like a sentence. AND runs first: only the Rome rows must be cheap, and every Oslo row comes back at any price.

NULL and LIKE

Find missing values correctly and search text by pattern.

  • NULL means the value is unknown or missing. It is not zero and not an empty string, and a comparison with it, even NULL = NULL, is never true.
  • So missing values are found with IS NULL, never with = NULL: WHERE phone IS NULL finds customers without a phone, and IS NOT NULL finds those with one.
  • LIKE matches text against a pattern: % stands for any run of characters and _ for exactly one. WHERE name LIKE 'A%' finds names starting with A.

The mistake you are about to make

WHERE phone = NULL

WHERE phone IS NULL

= NULL looks like an ordinary emptiness check. A comparison with NULL is never true, so the query does not fail — it returns zero rows even when a hundred are missing.

Back to the map