WHERE: keeping only the rows you want

Step 1 Understand it

Why you need it. Tables hold every row ever stored, and you almost always want a slice: one department, orders over £100, everyone hired this year. WHERE is how you say which rows count.

Watch it happen

Picture it

flowchart LR
  R["every row of employees"] --> C{"dept_id = 1<br/>AND salary under 90000?"}
  C -->|"true"| K["kept:<br/>Ben, Chen, Hana"]
  C -->|"false or unknown"| D["dropped:<br/>Asha and the rest"]

The idea

A condition is a statement about a row that is either true or false, like salary > 60000. The database checks it against every row and keeps the rows where it is true. You can combine conditions: AND needs both to be true, OR needs at least one, and NOT flips true and false. Comparisons are =, <> (not equal; != also works), <, <=, > and >=. Text values go in single quotes.

The shape of it

SELECT columns
FROM table_name
WHERE condition AND (other OR another);

Words in lower case are the parts you fill in.

How it works, step by step

1. FROM takes every row of employees.
2. WHERE checks the condition dept_id = 1 AND salary < 90000 on each row.
3. Asha: department 1 is true, but 95000 < 90000 is false. Both must be true for AND, so Asha is dropped.
4. Ben, Chen and Hana pass both tests, so they are kept. Everyone outside department 1 fails the first test.
5. SELECT then picks the columns from the rows that are left.
6. AND is worked out before OR, just as × comes before + in maths. Use brackets whenever you mix them.

Analogy

A bouncer with a list of rules. Every row walks up to the door; if it passes the rules, it gets in. "Over 18 AND on the guest list" is stricter than "over 18 OR on the guest list".

Where the analogy breaks

A bouncer answers yes or no. SQL has a third answer, unknown, which comes from NULL — and WHERE treats unknown the same as no. The NULL lesson next in this phase shows why that matters.

Worked examples

SELECT name, salary
FROM employees
WHERE dept_id = 1 AND salary < 90000
ORDER BY name;

SELECT name, dept_id, salary
FROM employees
WHERE (dept_id = 2 OR dept_id = 3) AND salary > 50000
ORDER BY name;

SELECT name, dept_id, salary
FROM employees
WHERE dept_id = 2 OR dept_id = 3 AND salary > 50000
ORDER BY name;

Must-know points

• WHERE runs on each row, before any grouping, and keeps only rows where the condition is true.
• Comparisons: =, <> (or !=), <, <=, >, >=. One = sign, not two.
• AND needs both sides true; OR needs at least one; NOT flips the answer.
• AND binds tighter than OR. Put brackets around every OR that is mixed with AND.
• Text goes in single quotes and must match exactly: 'Sales' is not 'sales' in most databases.

Common mistakes

• Mixing AND and OR without brackets, and getting extra rows.
• Writing ==. SQL uses a single = for comparing.
• Writing WHERE dept_id = 2 OR 3. Each side of OR must be a full condition: dept_id = 2 OR dept_id = 3, or better, dept_id IN (2, 3).

In other databases

• Text comparison: PostgreSQL is case-sensitive ('Sales' ≠ 'sales'); MySQL and SQL Server usually are not, depending on settings; in SQLite = is case-sensitive but LIKE is not.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  W3Schools — SQL WHERE Clause

  short page with a try-it editor, free

• Read

  Mode SQL Tutorial — SQL WHERE, SQL Comparison Operators, SQL AND, SQL OR

  Mode, free

• Practice

  SQLBolt — Lesson 2: Queries with constraints (Pt. 1)

  interactive exercises in the browser, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain the difference between AND and OR with an example from the orders table.

   Show answer

   AND needs both conditions to be true. OR needs at least one.

   WHERE status = 'delivered' AND amount > 100 returns orders that are both delivered and over £100: just 101 and 104.

   WHERE status = 'delivered' OR amount > 100 returns orders that are either: all 7 delivered orders, plus 105, which is only shipped but is over £100. That makes 8.

   So AND makes the result smaller, and OR makes it bigger.

2. Writefrom a blank page, then run it

   Write a query for orders that are delivered and worth at least £75, showing order_id and amount, sorted by order_id.

   Show answer

   "At least £75" includes £75, so use >=. Order 108 is exactly £75; with > it would be missed.

   sqlCopy

   SELECT order_id, amount
   FROM orders
   WHERE status = 'delivered' AND amount >= 75
   ORDER BY order_id;

   Try it

   4 rows

   order_id	amount
   101	120
   102	80
   104	200
   108	75

3. Predictsay it before you run it

   Which names does SELECT name FROM employees WHERE NOT dept_id = 1 ORDER BY name; return? Is Gus in the list?

   Show answer

   Dara, Eli and Fatima. Gus is not in the list.

   Gus's dept_id is NULL. dept_id = 1 is unknown for him, and NOT unknown is still unknown, not true. WHERE keeps only true rows, so he is dropped. To include him, add OR dept_id IS NULL.

   sqlCopy

   SELECT name
   FROM employees
   WHERE NOT dept_id = 1
   ORDER BY name;

   Try it

   3 rows

   name
   Dara
   Eli
   Fatima

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Rewrite WHERE status = 'shipped' OR status = 'pending' AND amount > 40 so it means "shipped or pending, and over £40". What did the original return?

   Show answer

   Add brackets around the OR: WHERE (status = 'shipped' OR status = 'pending') AND amount > 40.

   AND is worked out before OR, so the original really meant "shipped (any amount), or pending and over £40".

   Here both versions return only order 105. That is luck: the only shipped order is £150, and the only pending one (110) is £33. If a shipped order were £20, the original would wrongly include it. The brackets make the query say what you mean, whatever the data.

   sqlCopy

   SELECT order_id, status, amount
   FROM orders
   WHERE (status = 'shipped' OR status = 'pending') AND amount > 40
   ORDER BY order_id;

   Try it

   1 row

   order_id	status	amount
   105	shipped	150

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

WHERE filters rows before any grouping: the database evaluates the condition for each row and keeps only those where it is true — false and unknown are both dropped. I combine conditions with AND, OR and NOT, and because AND has higher precedence than OR, I always bracket OR conditions when mixing them, so the query says exactly what I mean.

Follow-up questions

Tick each check when you can do it without notes