SQL Fundamentals

The WHERE Clause

Filter your data to find exactly what you need — no more, no less.

Comparison Operators — equals, greater than, less than, and more
Logical Operators — combine conditions with AND, OR, NOT
Pattern Matching — search text with LIKE and wildcards

WHERE is like a sieve for your data.

What is WHERE?

SELECT column1, column2
FROM table_name
WHERE condition;

Component	Description	Example
`WHERE`	Keyword	Required
`condition`	Boolean expression	`salary > 70000`
Comparison operators	`=`, `<>`, `>`, `<`, `>=`, `<=`	`WHERE price >= 100`
Logical operators	`AND`, `OR`, `NOT`	`WHERE a > 1 AND b < 10`

Comparison Operators

Operator	Meaning	Example	Description
`=`	Equal to	`WHERE age = 25`	Exact match
`<>` or `!=`	Not equal	`WHERE age <> 25`	Everything except 25
`>`	Greater than	`WHERE price > 100`	Above 100
`<`	Less than	`WHERE price < 50`	Below 50
`>=`	Greater or equal	`WHERE score >= 90`	90 or above
`<=`	Less or equal	`WHERE score <= 60`	60 or below

-- Equal to
SELECT first_name, last_name, salary
FROM employees
WHERE salary = 75000;

-- Greater than
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 70000;

first_name	last_name	salary
Alice	Johnson	75000
Carol	Williams	85000
Eve	Brown	91000

WHERE with Text

-- Exact match (case-sensitive in some databases)
SELECT * FROM customers WHERE city = 'New York';

-- Case-insensitive search
SELECT * FROM customers WHERE LOWER(city) = 'new york';

-- Pattern matching with LIKE
SELECT * FROM customers WHERE name LIKE 'A%';      -- Starts with A
SELECT * FROM customers WHERE name LIKE '%son';    -- Ends with son
SELECT * FROM customers WHERE name LIKE '%john%';  -- Contains john

Wildcard	Description	Example	Matches
`%`	Any number of characters	`'%son'`	Johnson, Smith
`_`	Exactly one character	`'_lice'`	Alice, Ellice

WHERE with Dates

-- Specific date
SELECT * FROM orders WHERE order_date = '2024-01-15';

-- Date range
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
AND order_date <= '2024-03-31';

-- Using BETWEEN for dates
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

-- Recent orders (last 30 days)
SELECT * FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30' DAY;

WHERE with NULL

-- Check for NULL (cannot use = NULL)
SELECT * FROM customers WHERE email IS NULL;

-- Check for NOT NULL
SELECT * FROM customers WHERE email IS NOT NULL;

-- WRONG! Returns nothing
SELECT * FROM customers WHERE email = NULL;
SELECT * FROM customers WHERE email != NULL;

Expression	Result	Explanation
`NULL = NULL`	NULL	NULL is unknown
`NULL != NULL`	NULL	NULL is unknown
`NULL IS NULL`	TRUE	Correct way to check
`NULL IS NOT NULL`	FALSE	Correct way to check

Multiple Conditions

-- AND: Both conditions must be true
SELECT * FROM employees
WHERE salary > 70000
AND department = 'Engineering';

-- OR: At least one condition must be true
SELECT * FROM employees
WHERE department = 'Engineering'
OR department = 'Marketing';

-- Combined AND and OR (use parentheses!)
SELECT * FROM employees
WHERE (department = 'Engineering' OR department = 'Marketing')
AND salary > 60000;

Precedence	Operator	Description
1 (Highest)	`NOT`	Negation
2	`AND`	Logical AND
3 (Lowest)	`OR`	Logical OR

WHERE with IN

-- Match against a list of values
SELECT * FROM employees
WHERE department IN ('Engineering', 'Marketing', 'Sales');

-- Equivalent to multiple OR conditions
SELECT * FROM employees
WHERE department = 'Engineering'
OR department = 'Marketing'
OR department = 'Sales';

-- NOT IN: Exclude values
SELECT * FROM employees
WHERE department NOT IN ('HR', 'Finance');

WHERE with BETWEEN

-- Inclusive range (includes both endpoints)
SELECT * FROM employees
WHERE salary BETWEEN 60000 AND 80000;

-- Equivalent to:
SELECT * FROM employees
WHERE salary >= 60000
AND salary <= 80000;

Performance Tips

Tip	Bad Example	Good Example	Why
Avoid functions on columns	`WHERE YEAR(date) = 2024`	`WHERE date >= '2024-01-01'`	Can use index
Avoid leading wildcards	`WHERE name LIKE '%son'`	`WHERE name LIKE 'John%'`	Can use index
Use IN instead of OR	`WHERE x=1 OR x=2 OR x=3`	`WHERE x IN (1,2,3)`	Cleaner, same performance
Use BETWEEN for ranges	`WHERE a>=1 AND a<=10`	`WHERE a BETWEEN 1 AND 10`	More readable

Common Mistakes

-- BAD: Comparing NULL with =
SELECT * FROM customers WHERE email = NULL;

-- GOOD: Use IS NULL
SELECT * FROM customers WHERE email IS NULL;

-- BAD: Using OR chains
SELECT * FROM employees
WHERE department = 'Engineering'
OR department = 'Marketing'
OR department = 'Sales'
OR department = 'Finance';

-- GOOD: Use IN
SELECT * FROM employees
WHERE department IN ('Engineering', 'Marketing', 'Sales', 'Finance');

-- BAD: Function on column in WHERE
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- GOOD: Range comparison
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2025-01-01';

Practice Exercises

Exercise 1: Find all employees who earn more than 60000, work in Engineering, and were hired after January 1, 2023.

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 60000
  AND department = 'Engineering'
  AND hire_date > '2023-01-01';

Exercise 2: Find all customers from 'New York', 'Los Angeles', or 'Chicago' with email addresses.

SELECT * FROM customers
WHERE city IN ('New York', 'Los Angeles', 'Chicago')
AND email IS NOT NULL;

Exercise 3: Find all products priced between 100, excluding the 'Clearance' category.

SELECT * FROM products
WHERE price BETWEEN 10 AND 100
AND category != 'Clearance';

WHERE Clause