Skip to main content
SQL Fundamentals
2 / 5
0%

WHERE: filtering rows

0/4 solved4 to unlock next
PrevLocked

Lesson 02

WHERE: filtering rows

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.

The problem WHERE solves

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...".

Syntax

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:

OperatorMeaningExample
=equal todepartment_id = 60
<> or !=not equal tostatus <> 'delivered'
<less thanprice < 20
>greater thansalary > 10000
<=less than or equal tostock <= 20
>=greater than or equal tohire_date >= '2007-01-01'

<> is the SQL standard spelling of "not equal". PostgreSQL accepts != as an alias, so pick one and be consistent.

Worked examples

Example 1: filter on a number

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.

Example 2: filter on text

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.

Example 3: filter on a date

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.

Example 4: filter on a column you do not display

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.

Example 5: two conditions with AND

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.

Under the hood: true, false and unknown

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.
  • Filtering early also matters for speed. On large tables, an index on the filtered column lets PostgreSQL jump straight to the matching rows instead of reading the whole table.

Common mistakes

Double quotes around a value

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'.

Writing dates in a local format

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'.

Using = NULL

As shown above, = NULL silently matches nothing. Use IS NULL or IS NOT NULL.

Expecting > to include the boundary

salary > 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.

How other databases differ

  • MySQL and SQL Server usually use case-insensitive collations by default, so 'cardiologist' would match 'Cardiologist' there. PostgreSQL is case sensitive out of the box. Do not rely on either behavior when moving queries between systems.
  • The ISO date format 'YYYY-MM-DD' is understood by every major database, which is another reason to use it.

Practice

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.

Key takeaways

  • WHERE keeps only the rows where the condition is true; everything else is discarded before SELECT runs.
  • Numbers go bare, text and dates go in single quotes, and dates should use 'YYYY-MM-DD'.
  • Text comparison in PostgreSQL is case sensitive; a typo returns zero rows, not an error.
  • You can filter on columns you do not select, but you cannot use SELECT aliases in WHERE.
  • = NULL never matches. Use IS NULL.

WHERE: filtering rows: in depth

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.

Worked examples

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.

Common mistakes

  • Writing = NULL instead of IS NULL. Equality with NULL is never true, so the filter returns zero rows.
  • Comparing text case-sensitively by accident: WHERE last_name = 'king' will not match 'King' in PostgreSQL.
  • Filtering a timestamp with = on a date (order_date = '2024-03-01' when the column holds times). Use a half-open range: >= start AND < next day.

Other databases

  • PostgreSQL text comparison is case-sensitive by default; MySQL with its default collation is case-insensitive.
  • SQL Server has no DATE 'yyyy-mm-dd' literal syntax; use '2024-01-01' or CAST('2024-01-01' AS date).
Check yourself: An employee has salary NULL. Does 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

Quick check: WHERE: filtering rows

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Employees earning more than $10,000

Enterprise Workforce AnalyticseasyPostgreSQL

List the first_name, last_name, and salary of every employee with a salary above 10000.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Everyone in IT

Enterprise Workforce AnalyticseasyPostgreSQL

Return the first_name and last_name of employees in department 60 (IT).

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Employees hired after 2005

Enterprise Workforce AnalyticseasyPostgreSQL

Return first_name, last_name, and hire_date for employees hired after 2005-01-01, ordered by hire_date ascending.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 4

In-stock products under $50

Commerce Operations IntelligenceeasyPostgreSQL

From the store sandbox, return name, price, and stock for products with price < 50 AND stock > 0, ordered by price ascending.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

SQL Fundamentals
2 / 5
0%

WHERE: filtering rows

0/4 solved4 to unlock next
PrevLocked