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?

DfWHERE Clause

A SQL clause that filters rows based on a condition. Only rows that evaluate to TRUE are included in the result set.

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

Use % at the start of a pattern (like '%son') to find suffixes. This prevents index usage and causes full table scans. Prefer patterns that start with a fixed character for better performance.

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;

Always use range comparisons on dates instead of functions like MONTH() or YEAR(). Functions on columns prevent the database from using indexes, causing slow full table scans.

WHERE with NULL

DfNULL

NULL represents an unknown or missing value. It is not equal to 0, an empty string, or any other value. You cannot compare NULL using = or !=.

-- 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

NULL is not the same as 0 or empty string. Always use IS NULL and IS NOT NULL to check for NULL values — never use = NULL.

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

When combining AND and OR, always use parentheses to make your intent explicit. Without them, AND is evaluated before OR, which can produce unexpected results.

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

The WHERE clause is evaluated after FROM but before SELECT. This means you cannot reference column aliases defined in the SELECT clause within a WHERE condition.

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 $10 and$ 100, excluding the 'Clearance' category.

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

Key Takeaways

WHERE filters rows based on conditions — only matching rows are returned
Use comparison operators for numbers, text, and dates
Use IS NULL / IS NOT NULL for NULL checks — never use = NULL
Combine conditions with AND and OR — use parentheses for clarity
WHERE is evaluated after FROM but before SELECT — aliases aren't available

WHERE Clause