SQL WHERE Clause – Filtering Rows

WHERE Syntax

The WHERE clause is placed after the FROM clause. The database evaluates the condition for every row and returns only the rows where the condition is TRUE.

SQL

SELECT column1, column2
FROM table_name
WHERE condition;

ℹ️

WHERE runs BEFORE SELECT

The SQL execution order is not the same as the written order. The database processes FROM first (identify the table), then WHERE (filter rows), and then SELECT (choose which columns to return). This is why you cannot use a column alias defined in SELECT inside a WHERE condition — the alias does not exist yet when WHERE runs.

All examples use the following employees table:

id	first_name	department	salary	manager_id
1	Alice	Engineering	85000	NULL
2	Bob	Marketing	62000	1
3	Carol	Engineering	91000	1
4	David	Marketing	58000	2
5	Eve	Engineering	77000	3

Equality – = and <>

The most basic WHERE condition checks whether a column equals a specific value. Use = for equality and <> (or !=) for not-equal.

SQL

-- Rows where department equals 'Engineering'
SELECT first_name, department, salary
FROM employees
WHERE department = 'Engineering';
-- Returns: Alice, Carol, Eve

-- Rows where department is NOT Engineering
SELECT first_name, department
FROM employees
WHERE department <> 'Engineering';
-- Returns: Bob, David

-- <> and != are equivalent; <> is standard SQL
WHERE department != 'Marketing'   -- same as <>

⚠️

String comparisons are case-sensitive in PostgreSQL

In PostgreSQL, WHERE department = 'engineering' and WHERE department = 'Engineering' return different results. MySQL's default collation is case-insensitive for string comparisons. Always match the case of your data, or use LOWER(department) = 'engineering' for case-insensitive matching in any database.

Comparison Operators

For numeric and date columns, use the standard comparison operators to filter ranges:

SQL

-- Greater than
SELECT first_name, salary FROM employees WHERE salary > 80000;
-- Returns: Alice (85000), Carol (91000)

-- Less than or equal
SELECT first_name, salary FROM employees WHERE salary <= 62000;
-- Returns: Bob (62000), David (58000)

-- Greater than or equal
SELECT first_name, salary FROM employees WHERE salary >= 77000;
-- Returns: Alice, Carol, Eve

-- Using dates (same operators apply)
SELECT * FROM orders WHERE order_date >= '2026-01-01';
SELECT * FROM orders WHERE order_date < '2026-06-01';

Operator	Meaning	Example
`=`	Equal to	`salary = 85000`
`<>` / `!=`	Not equal to	`department <> 'HR'`
`>`	Greater than	`salary > 70000`
`<`	Less than	`salary < 60000`
`>=`	Greater than or equal	`salary >= 80000`
`<=`	Less than or equal	`salary <= 65000`

Multiple Conditions – AND and OR

You can combine multiple conditions using AND and OR. AND requires both conditions to be true; OR requires at least one to be true.

SQL

-- AND: both conditions must be true
SELECT first_name, department, salary
FROM employees
WHERE department = 'Engineering' AND salary > 80000;
-- Returns: Alice (85000), Carol (91000)

-- OR: at least one condition must be true
SELECT first_name, department, salary
FROM employees
WHERE department = 'Engineering' OR salary < 60000;
-- Returns: Alice, Carol, Eve (Engineering) AND David (salary < 60000)

-- Combining AND and OR — use parentheses for clarity!
SELECT first_name, salary
FROM employees
WHERE (department = 'Engineering' AND salary > 80000)
   OR (department = 'Marketing'   AND salary > 60000);
-- Engineering with salary > 80000 OR Marketing with salary > 60000

⚠️

AND binds tighter than OR — always use parentheses

WHERE a OR b AND c is evaluated as WHERE a OR (b AND c) because AND has higher operator precedence than OR. This often produces unexpected results. Always use explicit parentheses when combining AND and OR to make your intent clear: WHERE (a OR b) AND c vs WHERE a OR (b AND c) are very different queries.

NULL Checks – IS NULL and IS NOT NULL

NULL in SQL is a special marker meaning "no value" or "unknown". It is fundamentally different from zero, false, or an empty string. NULL requires special syntax for comparison — you cannot use = NULL.

SQL

-- WRONG: this never returns any rows, even for NULL values!
SELECT * FROM employees WHERE manager_id = NULL;

-- CORRECT: use IS NULL
SELECT first_name FROM employees WHERE manager_id IS NULL;
-- Returns: Alice (the top-level manager, no manager above her)

-- Find all employees who DO have a manager
SELECT first_name FROM employees WHERE manager_id IS NOT NULL;
-- Returns: Bob, Carol, David, Eve

ℹ️

Why does = NULL never work?

NULL represents an unknown value. Comparing an unknown to anything — including another unknown — produces NULL (neither TRUE nor FALSE). The expression NULL = NULL evaluates to NULL, not TRUE. Because NULL is not TRUE, the row is excluded. The only correct way to test for NULL is IS NULL and IS NOT NULL, which are specifically designed to handle the three-valued logic of SQL.

Practical Example

Putting it all together — a realistic query that combines multiple WHERE conditions:

SQL

-- Find all Engineering employees earning over $60,000
-- who have a manager assigned
SELECT first_name, department, salary, manager_id
FROM employees
WHERE department = 'Engineering'
  AND salary > 60000
  AND manager_id IS NOT NULL;
-- Returns: Carol (91000, manager_id=1), Eve (77000, manager_id=3)

-- Alternative: find all high earners regardless of department,
-- or anyone in Marketing
SELECT first_name, department, salary
FROM employees
WHERE salary > 80000
   OR department = 'Marketing';
-- Returns: Alice, Carol (salary > 80000) + Bob, David (Marketing)

WHERE: Filtering Rows Before Anything Else Happens

WHERE keeps only rows matching a condition — and it runs early, before grouping and before the columns in SELECT are computed. That ordering explains two classic errors.

SELECT name, price * 1.1 AS with_tax
FROM products
WHERE price > 100 AND category = 'books';

Two things WHERE can't do

Can't use a SELECT alias: WHERE with_tax > 50 fails — WHERE runs before the alias exists. Repeat the expression or use a subquery.
Can't use aggregates: WHERE COUNT(*) > 5 is illegal — that's HAVING's job, which runs after grouping.

Operator	Matches
`BETWEEN 1 AND 10`	inclusive range
`IN ('a','b')`	any of a list
`LIKE 'A%'`	pattern (% = any chars)
`IS NULL`	missing value

NULL trap: WHERE col = NULL never matches anything — comparison with NULL yields "unknown," not true. Always use IS NULL / IS NOT NULL. And mind precedence: AND binds tighter than OR, so parenthesize mixed conditions.

🏋️ Practical Exercise

Filter rows with a single WHERE condition.
Combine two conditions with AND.
Use OR for alternatives.
Filter on a text value.
Filter on a numeric range.

🔥 Challenge Exercise

On an orders table, write a query returning orders with amount > 100 placed in 2024, combining conditions with AND. Explain how WHERE filters rows before grouping and the difference between WHERE and HAVING.

📋 Summary

The WHERE clause filters rows before they are returned. Only rows where the condition is TRUE are included.
WHERE runs before SELECT in the logical execution order — column aliases defined in SELECT cannot be used in WHERE.
Equality: use = and <> (or !=) for equals / not equals.
Comparison operators: >, <, >=, <= work on numbers, dates, and strings.
Combine conditions with AND (both must be true) and OR (at least one must be true). Use parentheses because AND binds tighter than OR.
To test for NULL, always use IS NULL / IS NOT NULL — never = NULL.

Interview Questions

What does the WHERE clause do?
Does WHERE filter before or after grouping?
What is the difference between WHERE and HAVING?
How do you combine multiple conditions?
How do you filter for NULL values?

FAQ

Can I use WHERE with UPDATE and DELETE? +

Yes — WHERE works identically with UPDATE and DELETE. UPDATE employees SET salary = 90000 WHERE id = 3; updates only the row with id=3. DELETE FROM employees WHERE department = 'Marketing'; deletes all Marketing rows. Omitting WHERE from UPDATE or DELETE applies the operation to every row in the table — always double-check your WHERE condition before running a destructive operation.

What is the difference between WHERE and HAVING? +

WHERE filters individual rows before any grouping or aggregation. HAVING filters groups after GROUP BY has been applied. For example, WHERE salary > 70000 excludes individual rows with salary under 70000 before counting. HAVING COUNT(*) > 5 excludes groups (departments) that have 5 or fewer rows after grouping. You cannot use aggregate functions (SUM, COUNT, AVG) in WHERE — only in HAVING.

Can I use WHERE with a subquery? +

Yes — subqueries in WHERE are very powerful. For example: SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); returns everyone earning above the average salary. The subquery runs first and its result is used in the outer WHERE condition. Subqueries in WHERE are covered in detail in the Subqueries section of this course.

How do I filter by multiple values for the same column? +

Use the IN operator: WHERE department IN ('Engineering', 'Marketing', 'HR') is equivalent to WHERE department = 'Engineering' OR department = 'Marketing' OR department = 'HR' but much cleaner to read. The IN operator is covered in detail in the BETWEEN & IN lesson.