Filtering with WHERE

The WHERE Clause

The WHERE clause filters rows based on one or more conditions. Only rows where the condition evaluates to true are included in the result.

SELECT * FROM products WHERE category = 'Electronics';

Comparison Operators

SELECT name, price FROM products WHERE price < 20;
SELECT name, price FROM products WHERE price >= 50;

AND, OR, NOT

Combine conditions with logical operators:

-- Both conditions must be true
SELECT * FROM products WHERE category = 'Office' AND price < 20;

-- Either condition must be true
SELECT * FROM products WHERE category = 'Kitchen' OR category = 'Accessories';

-- Negates the condition
SELECT * FROM products WHERE NOT category = 'Electronics';

Use parentheses to control precedence:

SELECT * FROM products
WHERE (category = 'Office' OR category = 'Kitchen') AND price < 40;

LIKE — Pattern Matching

LIKE matches text patterns using wildcards:

• % — matches any sequence of characters (including none)
• _ — matches exactly one character

-- Products whose name starts with 'C'
SELECT * FROM products WHERE name LIKE 'C%';

-- Products with 'Maker' anywhere in the name
SELECT * FROM products WHERE name LIKE '%Maker%';

SQLite's LIKE is case-insensitive for ASCII characters by default.

IN — Match a List

IN checks if a value matches any value in a list:

SELECT * FROM products WHERE category IN ('Electronics', 'Kitchen');

This is equivalent to:

SELECT * FROM products WHERE category = 'Electronics' OR category = 'Kitchen';

BETWEEN — Range Check

BETWEEN checks if a value falls within an inclusive range:

SELECT * FROM products WHERE price BETWEEN 10 AND 60;

IS NULL / IS NOT NULL

Check for missing values:

SELECT * FROM orders WHERE customer_id IS NULL;
SELECT * FROM products WHERE category IS NOT NULL;

Never use = NULL — it always returns false. Always use IS NULL.

Your Task

Select the name and price of all products in the 'Office' category with a price less than 20.