SQL WHERE

Return only the rows you need with comparisons, AND, OR, NOT, IN, BETWEEN, LIKE and IS NULL.

Lesson 3 of 102Beginner19 min read

Most of the time you don't want every row. You want the customers in Leeds, the products under 10 pounds, or the orders from last week. The WHERE clause filters a table so the result contains only the rows that match a condition.

WHERE comes after FROM. For every row, the database checks the condition. If it's true, the row is included; if not, it's skipped. Conditions can compare numbers, text and dates, combine several tests, and match patterns in text.

In this lesson you'll learn the comparison operators, combine conditions with AND, OR and NOT, and use the shortcuts IN, BETWEEN, LIKE and IS NULL.

Comparison operators

OperatorMeaningExample
=equal tocity = 'Leeds'
<> or !=not equal tocategory <> 'Bags'
> <greater / less thanprice > 10
>= <=greater or equal / less or equalstock <= 20

Notice that SQL uses a single = for comparing. Text values go in single quotes: 'Leeds'. Numbers don't need quotes. Dates are usually written as text in the form '2024-03-15' and compare in date order.

AND, OR and NOT

  • AND keeps a row only if both conditions are true.
  • OR keeps a row if either condition is true.
  • NOT reverses a condition.
SQL
SELECT name, price
FROM products
WHERE category = 'Kitchen' AND price < 10;

When you mix AND and OR, AND is checked first, just as multiplication comes before addition in maths. Use brackets to make your meaning clear: WHERE (city = 'Leeds' OR city = 'Lyon') AND joined >= '2024-01-01'.

IN and BETWEEN

IN checks against a list of values. It's a tidy replacement for several ORs:

SQL
WHERE city IN ('Leeds', 'Lisbon', 'Porto')

BETWEEN checks a range, including both ends:

SQL
WHERE price BETWEEN 5 AND 25

Both work with numbers, text and dates, and both can be reversed with NOT: NOT IN (...), NOT BETWEEN ....

LIKE and IS NULL

LIKE matches text against a pattern:

  • % stands for any number of characters, including none.
  • _ stands for exactly one character.

So name LIKE 'D%' finds names starting with D, name LIKE '%box' finds names ending in "box", and name LIKE '_en' matches "Ben" and "Pen".

A NULL value means "unknown" or "missing". You can't test it with =. Use IS NULL or IS NOT NULL instead: WHERE city IS NULL.

Filtering by dates

Dates stored as DATE or in ISO text format, YYYY-MM-DD, compare in calendar order, so ordinary comparison operators work:

SQL
SELECT id, order_date
FROM orders
WHERE order_date >= '2024-03-01'
  AND order_date <  '2024-04-01';

This half-open range, from the first day of March up to but not including the first of April, is the safest way to select a whole month. It works for dates and for timestamps with times of day. BETWEEN '2024-03-01' AND '2024-03-31' looks equivalent, but with timestamps it misses everything after midnight on 31 March. The dates and times lesson covers date functions.

Upper and lower case in text filters

Whether city = 'leeds' matches Leeds depends on the database. PostgreSQL and SQLite compare text exactly, so case matters; MySQL's default settings ignore case. LIKE follows similar rules, except that SQLite's LIKE ignores case for English letters, and PostgreSQL offers ILIKE for case-insensitive patterns. To match regardless of case anywhere, compare lowercase versions: WHERE LOWER(city) = 'leeds'.

SQL WHERE Syntax

WHERE

SELECT columns
FROM table_name
WHERE condition;

-- examples of conditions
WHERE price >= 10 AND stock > 0
WHERE city IN ('Leeds', 'Lyon')
WHERE joined BETWEEN '2024-01-01' AND '2024-12-31'
WHERE name LIKE 'A%'
WHERE city IS NULL

SQL WHERE Summary

  • WHERE condition keeps only the rows where the condition is true.
  • Compare with =, <>, >, <, >= and <=. Text goes in single quotes.
  • Combine tests with AND, OR and NOT; use brackets to group them.
  • IN (...) checks a list; BETWEEN a AND b checks an inclusive range.
  • LIKE matches patterns with % and _; test missing values with IS NULL.

SQL WHERE Examples

Each example shows the code, the output it produces and what each part does.

Example 1

Customers in one city

SQL
SELECT name, joined
FROM customers
WHERE city = 'Leeds';
Output
name | joined
-----+-----------
Ben  | 2023-05-22
Dev  | 2024-03-15
Line-by-line explanation
  1. Line 3Only rows where city is exactly 'Leeds' are returned.
Example 2

Cheap products in stock

SQL
SELECT name, price, stock
FROM products
WHERE price < 15 AND stock > 0;
Output
name         | price | stock
-------------+-------+------
Notebook     | 3.50  | 120
Pen          | 1.20  | 300
Water bottle | 12.00 | 40
Line-by-line explanation
  1. Line 3Both tests must pass. The lunch box is cheap but has 0 in stock, so it's left out.
Example 3

Ranges, lists and patterns

SQL
SELECT name, category, price
FROM products
WHERE price BETWEEN 5 AND 30
  AND category IN ('Kitchen', 'Home');
Output
name         | category | price
-------------+----------+------
Water bottle | Kitchen  | 12.00
Lunch box    | Kitchen  | 8.75
Desk lamp    | Home     | 24.50
Line-by-line explanation
  1. Line 3BETWEEN includes 5 and 30 themselves.
  2. Line 4IN keeps rows whose category is in the list.
Example 4

Names that match a pattern

SQL
SELECT name
FROM products
WHERE name LIKE '%o%';
Output
name
------------
Notebook
Water bottle
Lunch box
Line-by-line explanation
  1. Line 3% on both sides means "contains an o anywhere". In SQLite and MySQL, LIKE ignores letter case for English text; in PostgreSQL use ILIKE for that.

Common SQL WHERE Mistakes

Double quotes around text

Don't
SELECT * FROM customers WHERE city = "Leeds";
Do
SELECT * FROM customers WHERE city = 'Leeds';

Testing NULL with =

Don't
SELECT * FROM customers WHERE city = NULL;
Do
SELECT * FROM customers WHERE city IS NULL;

Nothing is ever equal to NULL, not even another NULL, so the first query always returns zero rows.

Mixing AND and OR without brackets

Don't
SELECT name FROM customers
WHERE city = 'Leeds' OR city = 'Lyon' AND joined >= '2024-01-01';
Do
SELECT name FROM customers
WHERE (city = 'Leeds' OR city = 'Lyon') AND joined >= '2024-01-01';

Without brackets, AND is applied first, so Ben from Leeds is returned even though he joined in 2023.

SQL WHERE in Real Projects

Filtering in real apps

Every search box and filter on a website ends up as a WHERE clause. Choosing "Kitchen" and "under 20" in a shop's sidebar becomes WHERE category = 'Kitchen' AND price < 20, and typing in a search box often becomes a LIKE '%word%' condition.

SQL WHERE FAQ

Can I filter on COUNT or SUM in WHERE?

No. WHERE filters individual rows before any grouping happens, so aggregate results don't exist yet. Use HAVING after GROUP BY, for example HAVING COUNT(*) > 2, as the ORDER BY and GROUP BY lesson shows.

How do I search for a literal % or _ with LIKE?

Escape it and declare the escape character: WHERE note LIKE '%50\%%' ESCAPE '\' finds text containing "50%". Without escaping, % means "any characters" and _ means "any one character", so a search for 50% would match far more than intended.

Is IN faster than several OR conditions?

They usually perform the same, because the database treats city IN ('Leeds', 'Lyon') and city = 'Leeds' OR city = 'Lyon' the same way. Use IN because it's shorter and avoids mistakes with brackets when combined with AND.

Why does NOT IN sometimes return no rows?

If the list or subquery contains a NULL, x NOT IN (1, NULL) can never be true: the comparison with NULL is unknown, so every row is filtered out. Remove the NULLs with WHERE col IS NOT NULL in the subquery, or use NOT EXISTS. The NULL values lesson explains why.

Does the order of conditions in WHERE affect speed?

Generally not: the database's query planner decides which conditions to check first and whether to use an index. The order does matter for meaning when you mix AND and OR, so use brackets to make the logic explicit.

SQL WHERE Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the query to find products that cost more than 20.

SELECT name, price
FROM products
 price  20;
Predict the outputmedium

Which names does this return?

SELECT name FROM customers
WHERE city IN ('Lisbon', 'Porto');
Multiple choicemedium

Which pattern finds product names that end in "lamp"?

Predict the outputhard

How many products does this return?

SELECT name FROM products
WHERE stock BETWEEN 15 AND 40;
Multiple choicemedium

Which condition finds products whose name starts with "B"?

SQL WHERE Quiz

5 questions to check what you learned.

  1. Which clause filters rows in a SELECT query?

    • FILTER
    • WHERE
    • FROM
    • ORDER BY

  2. How should you write a text value in a condition?

    • Leeds
    • (Leeds)
    • {Leeds}
    • 'Leeds'

  3. How do you find rows where city has no value?

    • WHERE city = NULL
    • WHERE city = ''
    • WHERE city IS NULL
    • WHERE NULL(city)

  4. Is price BETWEEN 10 AND 20 the same as price >= 10 AND price <= 20?

    • No, BETWEEN excludes both ends
    • No, BETWEEN excludes 20
    • Only for whole numbers
    • Yes, BETWEEN includes both ends

  5. Which rows does WHERE price BETWEEN 5 AND 10 include?

    • Prices above 5 and below 10 only
    • Prices from 5 to 10, including 5 and 10
    • Only prices of exactly 5 or 10
    • Prices below 5 or above 10

Related SQL topics

02SELECTRead exactly the columns you need, rename them, calculate new values and remove duplicates.05ORDER BY & GROUP BYSort results, return only the top rows, and summarise data with COUNT, SUM, AVG and GROUP BY.08INSERT, UPDATE, DELETEAdd new rows, change existing data and remove rows safely, without losing anything by accident.
Esc
↑↓ to navigateEnter to open