Filtering Data — SQL

WHERE condition goes after FROM, and only the rows that meet the condition go into the result. A condition uses a comparison operator (a sign that compares…

In a shopping app, you tick the Electronics filter and set a maximum price of $1,000. Out of thousands of items, only a few are still shown. The other items are not gone from the shop: they just do not meet your conditions.

In SQL, a filter like the one in the shopping app is written with WHERE, followed by a condition. The database checks every row one by one. A row that meets the condition passes, and only the rows that pass go into the query's result. The table itself does not change: only the result is filtered.

WHERE and the comparison operators

The products table that the exercise uses:

id name price category
1 Laptop 1200 electronics
2 Mouse 15 electronics
3 Notebook 4 stationery
4 Pen 1 stationery
5 Monitor 350 electronics

WHERE goes after FROM. The query below reads: fetch the name and price columns from the products table, where price is less than 1000.

SELECT name, price
FROM products
WHERE price < 1000;

The result is 4 rows: Mouse, Notebook, Pen, Monitor. Laptop does not pass, because its price is 1200.

The < sign is a comparison operator: a sign that compares two values. This is the list:

Operator Means Example
= equal to category = 'electronics'
!= or <> not equal to category != 'electronics'
> / < greater / less than price > 100
>= / <= greater or equal / less or equal price <= 4

AND, OR and NOT

You can join two conditions with AND (both must be true) or OR (one true condition is enough). Before you look at the table below, guess: of the 5 products in the products table, how many pass when the condition is WHERE category = 'electronics' OR price < 1000?

SQLite checks every condition on every row, and writes true as 1 and false as 0. The AND and OR columns are the result of joining both conditions:

name category = 'electronics' price < 1000 AND OR
Laptop 1 0 0 1
Mouse 1 1 1 1
Notebook 0 1 0 1
Pen 0 1 0 1
Monitor 1 1 1 1

With AND, only 2 rows pass:

SELECT name, price
FROM products
WHERE category = 'electronics' AND price < 1000;
name price
Mouse 15
Monitor 350

With OR, all five rows pass, because every product meets at least one condition. Laptop passes because of its category, Notebook and Pen because of their price.

NOT reverses the result of a condition (true becomes false, false becomes true). WHERE NOT category = 'electronics' gives Notebook and Pen, the same as category != 'electronics'.

LIKE, IN and BETWEEN

LIKE compares text with a pattern. In a pattern, % stands for any characters, any number of them, even zero. _ stands for exactly one character:

SELECT name FROM products WHERE name LIKE 'M%';   -- Mouse, Monitor
SELECT name FROM products WHERE name LIKE '%o%';  -- Laptop, Mouse, Notebook, Monitor

'M%' means text that starts with M. '%o%' means text with an o anywhere in it. In SQLite and MySQL, LIKE treats upper and lower case as the same for A to Z. In PostgreSQL, LIKE treats them as different, and the version that treats them as the same is called ILIKE.

IN means the value equals one of the items in the list inside the parentheses:

SELECT name FROM products WHERE name IN ('Mouse', 'Pen', 'Fan');  -- Mouse, Pen

That query is the same as name = 'Mouse' OR name = 'Pen' OR name = 'Fan', only shorter. There is no Fan in the table, so the result is only Mouse and Pen.

BETWEEN means the value is inside a range, both ends included (here 4 and 350). In this query, AND is part of BETWEEN, not a join of two conditions:

SELECT name FROM products WHERE price BETWEEN 4 AND 350;  -- Mouse, Notebook, Monitor

⚠️ Why is the result an error, empty, or too big?

TL;DR: WHERE condition goes after FROM, and only the rows that meet the condition go into the result. A condition uses a comparison operator (a sign that compares two values): =, != or <> (not equal to), >, <, >=, <=. Text goes inside single quotes, numbers have no quotes and no thousands separators. Two conditions are joined with AND (both must be true) or OR (one true condition is enough), and NOT reverses the result of a condition. AND is calculated before OR. LIKE compares text with a pattern: % stands for any characters, any number of them, _ for exactly one character. IN (...) is true when the column's value equals one of the items in the list. BETWEEN a AND b is true for values from a to b, with a and b included.

⚡ Filter rows with WHERE

Write:

SELECT name, price
FROM products
WHERE price < 1000;

Meaning: WHERE is followed by a condition, here price < 1000 (the price is less than 1000). A row that meets the condition passes, and only the rows that pass go into the result. Of the 5 products in the products table, 4 pass: Mouse, Notebook, Pen, Monitor. Laptop does not pass, because its price is 1200. Numbers are written without thousands separators, text inside single quotes.

⚡ AND or OR

Write:

SELECT name, price
FROM products
WHERE category = 'electronics' AND price < 1000;
Guess first: Laptop, Mouse and Monitor are in the electronics category, Notebook and Pen are not. If AND is replaced with OR, how many of the 5 products pass?

All five. Every product meets at least one condition: Laptop because of its category, Notebook and Pen because of their price.

Meaning: AND joins two conditions, and both must be true. With AND, only Mouse and Monitor pass. With OR, one true condition is enough.

⚡ Patterns, lists, ranges

Write:

SELECT name FROM products WHERE name LIKE 'M%';

Meaning: LIKE compares text with a pattern. In a pattern, % stands for any characters, any number of them. So 'M%' means text that starts with M: Mouse and Monitor. IN (...) is true when the column's value equals one of the items in the list inside the parentheses. BETWEEN a AND b is true for values from a to b, with a and b included. The AND inside BETWEEN does not join two conditions.

What you will learn