Filtering with WHERE

The WHERE Clause

WHERE filters rows before they are returned. Only rows where the condition is true are included in the result.

SELECT * FROM products WHERE category = 'Electronics';

Comparison Operators

Logical Operators: AND, OR, NOT

Combine conditions with logical operators:

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

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

-- Negate a condition
SELECT * FROM products
WHERE NOT category = 'Office';

Use parentheses to control precedence. AND binds more tightly than OR:

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

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

BETWEEN — Range Conditions

BETWEEN is a shortcut for range checks (inclusive on both ends):

SELECT * FROM products WHERE price BETWEEN 20 AND 60;
-- Equivalent to: WHERE price >= 20 AND price <= 60

IN — Matching a List

IN checks if a value is in a list:

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

-- Equivalent to:
-- WHERE category = 'Electronics' OR category = 'Kitchen'

LIKE — Pattern Matching

LIKE matches string patterns. Use % for any sequence of characters and _ for exactly one character:

SELECT * FROM products WHERE name LIKE 'Head%';   -- starts with Head
SELECT * FROM products WHERE name LIKE '%er';      -- ends with er
SELECT * FROM products WHERE name LIKE '%Book%';  -- contains Book

Your Task

Find all products that are in the 'Electronics' category or have a price below 20. Return the name and price columns.