Filtering with WHERE

The WHERE Clause

The WHERE clause filters rows based on a condition. Only rows that satisfy the condition are included in the result:

SELECT * FROM products WHERE category = 'Electronics';

This returns only the products whose category column equals 'Electronics'.

Warning: SQL uses single quotes for string literals. Double quotes are reserved for identifiers (like column or table names). Writing WHERE category = "Electronics" will cause an error in PostgreSQL because it looks for a column named Electronics.

Think of WHERE like tuning the Enterprise's sensors to filter out the noise: you are scanning for specific life signs in a sea of data.

Comparison Operators

SQL provides the standard set of comparison operators:

SELECT * FROM products WHERE price > 50;
SELECT * FROM products WHERE price <= 20;

Combining Conditions with AND / OR

Use AND when both conditions must be true. Use OR when at least one must be true:

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

-- At least one condition must be true
SELECT * FROM products WHERE category = 'Kitchen' OR category = 'Office';

When mixing AND and OR, use parentheses to make the logic explicit:

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

The IN Operator

IN checks whether a value matches any value in a list. It is a cleaner alternative to multiple OR conditions:

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

This is equivalent to:

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

The BETWEEN Operator

BETWEEN checks whether a value falls within a range (inclusive on both ends):

SELECT * FROM products WHERE price BETWEEN 10 AND 100;

This is equivalent to price >= 10 AND price <= 100.

Pattern Matching with LIKE

LIKE matches text against a pattern using two wildcards:

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

-- Products ending with 'er'
SELECT * FROM products WHERE name LIKE '%er';

-- Products with exactly 3 characters
SELECT * FROM products WHERE name LIKE '___';

Tip: LIKE is case-sensitive in PostgreSQL. Use ILIKE for case-insensitive matching: WHERE name ILIKE '%coffee%'.

NULL Checks

NULL represents the absence of a value. You cannot use = to check for NULL. Use IS NULL or IS NOT NULL instead:

SELECT * FROM products WHERE category IS NOT NULL;

Your Task

Find all products in the 'Electronics' category with a price between 50 and 1000.