Lesson Description
Keep only rows that match a condition.
Lesson 02
Lesson Description
Keep only rows that match a condition.
Easy Project
Mini project: Create a hiring shortlist query that filters by role, salary range, and hire date with deterministic ordering.
In the previous lesson every query returned every row in the table. That is fine for a 10-row departments table, but real questions are almost always about a subset: "Which orders are still pending?", "Who in IT earns less than 5000?", "Which patients live in Seattle?"
WHERE is the filter. You give it a condition, and the database keeps only the rows where that condition is true. Everything else is dropped before the result is built. Learning to write precise conditions is probably the single most useful SQL skill, because almost every report, dashboard and data fix starts with "only the rows where...".
SELECT column1, column2
FROM table_name
WHERE condition;
WHERE always comes after FROM. The condition is usually a comparison between a column and a value, built with one of these operators:
| Operator | Meaning | Example |
|---|---|---|
= | equal to | department_id = 60 |
<> or != | not equal to | status <> 'delivered' |
< | less than | price < 20 |
> | greater than | salary > 10000 |
<= | less than or equal to | stock <= 20 |
>= | greater than or equal to | hire_date >= '2007-01-01' |
<> is the SQL standard spelling of "not equal". PostgreSQL accepts != as an alias, so pick one and be consistent.
Finance wants to see the highest earners:
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 10000;
PostgreSQL checks each of the 40 employees in turn. 11 of them have a salary above 10000, so you get 11 rows. Note that > is strict: an employee earning exactly 10000 (Peter Tucker) is not included. If the business rule is "10000 or more", use >=.
Filtering on a foreign key works the same way. Department 60 is IT:
SELECT first_name, last_name, salary
FROM employees
WHERE department_id = 60;
That returns 5 rows: Alexander Hunold at 9000, Bruce Ernst at 6000, David Austin and Valli Pataballa at 4800 each, and Diana Lorentz at 4200. Notice that department_id is a number, so the value 60 has no quotes.
Switch to the store sandbox. The warehouse needs every order that has not shipped yet:
SELECT id, customer_id, order_date
FROM orders
WHERE status = 'pending';
You get 7 of the 50 orders, including order 1021 placed on 2025-05-12. Text values go in single quotes. And in PostgreSQL, text comparison with = is case sensitive. In the hospital sandbox this query finds 2 doctors:
SELECT first_name, last_name
FROM doctors
WHERE specialty = 'Cardiologist';
Change the value to 'cardiologist' (lowercase c) and you get 0 rows, with no error. The database did exactly what you asked; there simply is no specialty spelled that way. When a filter on text returns nothing, check capitalization and stray spaces first. The next course covers ILIKE for case-insensitive matching.
If the text itself contains a single quote, double it. Dr. O'Brien is found like this:
SELECT first_name, last_name, specialty
FROM doctors
WHERE last_name = 'O''Brien';
One row comes back: Frank O'Brien, Orthopedic Surgeon. The two single quotes inside the string stand for one literal apostrophe.
Dates are compared as dates, so earlier is "less than" and later is "greater than". Write the date as text in ISO format, 'YYYY-MM-DD', and PostgreSQL converts it for you:
SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date >= '2007-01-01';
This returns 7 employees hired in 2007 or later, from Diana Lorentz (2007-02-07) to Eleni Zlotkey (2008-01-29). In the hospital sandbox, the same pattern finds doctors hired after 2018:
SELECT first_name, last_name, hire_date
FROM doctors
WHERE hire_date > '2018-12-31';
That gives 5 doctors, starting with Daniel Murphy on 2019-02-11. Writing > '2018-12-31' and >= '2019-01-01' are equivalent for a date column, and many people find the >= form easier to read.
The condition does not have to use a column from your SELECT list. The store's purchasing team wants a short list of products to restock:
SELECT name
FROM products
WHERE stock < 25;
You get 5 product names: Espresso Machine, 4K Monitor 27", Tennis Racket, Lego Castle and Knife Set. The stock values (8 to 22) are used for filtering but never shown. This works because WHERE runs before SELECT picks the output columns.
Often one condition is not enough. To find Shipping employees (department 50) who earn more than 6000, join both conditions with AND:
SELECT first_name, last_name, salary
FROM employees
WHERE department_id = 50
AND salary > 6000;
Only 4 rows satisfy both conditions at once: Matthew Weiss, Adam Fripp, Payam Kaufling and Shanta Vollman. A query has one WHERE clause; you combine conditions inside it. The next course, Filtering and Logic, covers AND, OR and NOT in depth, including the parentheses rules that trip people up.
For every row, PostgreSQL evaluates the condition and gets one of three answers: true, false or unknown. Only rows where the answer is true survive.
The third answer comes from NULL. In the HR sandbox, Steven King has no manager, so his manager_id is NULL. Try this:
SELECT first_name, last_name
FROM employees
WHERE manager_id = NULL;
It returns 0 rows, even though one employee clearly has a missing manager. Comparing anything to NULL with = gives unknown, not true, so the row is dropped. The correct test is IS NULL:
SELECT first_name, last_name
FROM employees
WHERE manager_id IS NULL;
Now you get 1 row: Steven King. The "Working with NULL" lesson in the next course explains this in full.
Two more details about evaluation:
WHERE runs right after FROM, before SELECT. That is why you can filter on columns you do not select, and why aliases from SELECT are not visible here.SELECT name FROM products WHERE name = "Hoodie";
The error is column "Hoodie" does not exist. Double quotes are for column and table names. Use 'Hoodie'.
SELECT first_name FROM employees WHERE hire_date > '31/12/2006';
PostgreSQL rejects this with date/time field value out of range: "31/12/2006", because it does not read day-first dates by default. Worse, a value like '01/02/2006' might be accepted but interpreted as January 2 rather than February 1. Always use '2006-12-31'.
= NULLAs shown above, = NULL silently matches nothing. Use IS NULL or IS NOT NULL.
> to include the boundarysalary > 10000 excludes 10000 itself. When a requirement says "at least" or "from", reach for >=. When it says "after" or "more than", > is usually right. If you are unsure, check the boundary value directly.
'cardiologist' would match 'Cardiologist' there. PostgreSQL is case sensitive out of the box. Do not rely on either behavior when moving queries between systems.'YYYY-MM-DD' is understood by every major database, which is another reason to use it.The exercises below this lesson ask you to filter employees by salary, by department and by hire date, and then to filter store products on two conditions at once. One exercise also asks for sorted output, so glance at ORDER BY in the next lesson if you get stuck. Read each prompt closely for words like "above" versus "at least", because the boundary matters to the grader.
WHERE keeps only the rows where the condition is true; everything else is discarded before SELECT runs.'YYYY-MM-DD'.SELECT aliases in WHERE.= NULL never matches. Use IS NULL.WHERE decides which rows enter the rest of the query. Filtering early is how you answer questions like "who earns over 10,000" or "orders placed this year", and it is also the cheapest place to cut work: every later step (joins, grouping, sorting) handles fewer rows.
Numeric comparison
SELECT first_name, last_name, salary
FROM employees
WHERE salary >= 10000;Keeps only rows where the condition is true. Rows where salary is NULL are dropped too, because NULL >= 10000 is unknown, not true.
Filtering on a date
SELECT id, customer_id, order_date
FROM orders
WHERE order_date >= DATE '2024-01-01';A typed date literal compares correctly against a date column. Comparing against a plain string works in PostgreSQL but depends on the date format.
WHERE NOT (salary > 5000) return them?No. salary > 5000 is unknown for NULL, and NOT unknown is still unknown, so the row is filtered out. Add OR salary IS NULL to include it.
Concept check
3 quick questions. One at a time. Instant score at the end.
List the first_name, last_name, and salary of every employee with a salary above 10000.
No results yet
Run a query to see results here
Return the first_name and last_name of employees in department 60 (IT).
No results yet
Run a query to see results here
Return first_name, last_name, and hire_date for employees hired after 2005-01-01, ordered by hire_date ascending.
No results yet
Run a query to see results here
From the store sandbox, return name, price, and stock for products with price < 50 AND stock > 0, ordered by price ascending.
No results yet
Run a query to see results here