Filtering with the WHERE clause

The basics of WHERE

To retrieve only data that matches a specific condition, you use a WHERE clause. Filtering inside the database is dramatically faster than fetching everything and filtering it in your application, and it also reduces network cost.

SELECT *
FROM products
WHERE price > 50000;

Comparison operators

Operator	Meaning	Example
=	equal to	category = 'PC'
!=, <>	not equal to	status != 'cancelled'
>	greater than	price > 50000
>=	greater than or equal to	price >= 50000
<	less than	price < 10000
<=	less than or equal to	price <= 10000

String conditions

Strings are wrapped in single quotes. Double quotes don't work on some databases, so make it a habit to use single quotes.

This site's sample users table stores city names in Japanese, so to filter by Tokyo you write city = '東京' (literally the Japanese word for "Tokyo") — the SQL syntax itself works exactly the same way as it would with any other string value.

SELECT *
FROM users
WHERE city = '東京'; -- '東京' = "Tokyo"

Combining conditions with AND / OR

AND requires "both conditions to be true"; OR requires "at least one condition to be true."

SELECT *
FROM products
WHERE category = 'PC' AND price >= 100000;

Fuzzy matching with LIKE

% represents any sequence of characters, and _ represents any single character.

SELECT * FROM users WHERE name LIKE '田%';    -- starts with "田" (a common first character in Japanese surnames)
SELECT * FROM users WHERE name LIKE '%太郎';   -- ends with "太郎" (a common Japanese given name)
SELECT * FROM users WHERE email LIKE '%@example%'; -- contains it anywhere

Matching multiple values with IN

IN can be used instead of lining up multiple values with OR. It stays readable even as the number of values grows.

SELECT * FROM orders
WHERE status IN ('pending', 'cancelled');

Specifying a range with BETWEEN

BETWEEN a AND b means greater than or equal to a, and less than or equal to b (inclusive of both ends).

SELECT * FROM products
WHERE price BETWEEN 5000 AND 50000;