WHERE Clause - Filtering Data

Filter query results using the WHERE clause.

16 min read
Beginner

Introduction to WHERE Clause

In the previous lesson, we learned to retrieve data with SELECT. But what if you don't want all the rows? What if you only want employees in Engineering, or products under $50, or customers in California?

That's where the WHERE clause comes in! It's like a filter that lets only certain rows pass through.

A SQL clause that filters rows based on a condition. Only rows where the condition is TRUE are included in the result.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Let's work with our employees table:

employees
id
first_name
last_name
department
salary
hire_date
1AliceJohnsonEngineering750002022-01-15
2BobSmithMarketing650002021-06-20
3CharlieDavisEngineering850002020-03-10
4DianaWilsonSales700002022-08-01
5EveMartinezMarketing680002021-11-12
6FrankBrownEngineering720002023-02-28
7GraceLeeSales730002021-09-15

Basic WHERE with Equality

The simplest WHERE condition uses = (equals) to match exact values.

sql
SELECT first_name, last_name, department
FROM employees
WHERE department = 'Engineering';

Only rows where department = 'Engineering' are returned. The other rows are filtered out!

Important: Text values must be enclosed in single quotes ('...'). Numbers don't need quotes.

sql
SELECT first_name, last_name, salary
FROM employees
WHERE salary = 70000;

Tip: SQL is case-insensitive for keywords (SELECT, WHERE, FROM), but string comparisons are usually case-sensitive! 'Engineering' ≠ 'engineering'

Comparison Operators

WHERE conditions can use various comparison operators to filter data:

Comparison Operators
Operator
Meaning
Example
Reads as
`=`Equal to`salary = 70000`"salary equals 70000"
`!=` or `<>`Not equal to`department != 'Sales'`"department is not Sales"
`>`Greater than`salary > 70000`"salary greater than 70000"
`<`Less than`salary < 70000`"salary less than 70000"
`>=`Greater than or equal`salary >= 70000`"salary 70000 or more"
`<=`Less than or equal`salary <= 70000`"salary 70000 or less"

Greater than (>):

sql
SELECT first_name, salary
FROM employees
WHERE salary > 70000;

Less than or equal (<=):

sql
SELECT first_name, salary, hire_date
FROM employees
WHERE salary <= 68000;

Not equal (!= or <>):

sql
SELECT first_name, department
FROM employees
WHERE department != 'Engineering';

Note: Both != and <> mean "not equal." They're interchangeable - use whichever you prefer! Most SQL dialects support both.

Filtering with Dates

Date comparisons work just like numbers! Dates must be in quotes, typically in 'YYYY-MM-DD' format.

sql
-- Employees hired after January 1, 2022
SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date > '2022-01-01';
sql
-- Employees hired in 2021 or earlier
SELECT first_name, hire_date
FROM employees
WHERE hire_date <= '2021-12-31';

Best Practice: Always use the standard date format 'YYYY-MM-DD' (e.g., '2024-03-15') for maximum compatibility across different SQL databases.

Filtering Text Data

When filtering text, remember that string comparisons are usually case-sensitive.

sql
SELECT first_name, last_name
FROM employees
WHERE last_name = 'Smith';

This would return nothing (case mismatch):

sql
SELECT first_name, last_name
FROM employees
WHERE last_name = 'smith';  -- lowercase 's'

Case-insensitive comparison (if needed):

sql
SELECT first_name, last_name
FROM employees
WHERE LOWER(last_name) = 'smith';

LOWER() converts the column value to lowercase before comparing. There's also UPPER() for uppercase comparisons.

Working with NULL Values

Remember that NULL means "no value" or "unknown." NULL is special - you can't use = or != with it!

Let's add a column to track employees' managers:

employees (with manager_id)
id
first_name
department
manager_id
1AliceEngineering3
2BobMarketing5
3CharlieEngineeringNULL
4DianaSales7
5EveMarketingNULL
6FrankEngineering3
7GraceSalesNULL

Finding NULL values - Use IS NULL:

sql
-- Employees with no manager (CEOs, executives)
SELECT first_name, department, manager_id
FROM employees
WHERE manager_id IS NULL;

Finding non-NULL values - Use IS NOT NULL:

sql
-- Employees who have a manager
SELECT first_name, department, manager_id
FROM employees
WHERE manager_id IS NOT NULL;

Common mistake: Using = NULL or != NULL doesn't work!

❌ Wrong: WHERE manager_id = NULL ✅ Correct: WHERE manager_id IS NULL

❌ Wrong: WHERE manager_id != NULL ✅ Correct: WHERE manager_id IS NOT NULL

NULL is special - you must use IS NULL or IS NOT NULL.

WHERE Clause Execution Order

Understanding how SQL processes your query helps you write better queries:

SQL Query Execution Order
Step
Clause
What Happens
1FROMDatabase locates the table
2WHERERows are filtered based on condition
3SELECTSpecified columns are retrieved

This means:

  • WHERE filters rows before SELECT picks columns
  • You can filter on columns that aren't in your SELECT list
  • WHERE happens on the raw data in the table
sql
-- Filter by salary, but don't display salary
SELECT first_name, department
FROM employees
WHERE salary > 70000;

Even though salary isn't in the SELECT list, we can still filter by it in WHERE!

Practical Examples

Example 1: Finding high earners

sql
SELECT first_name, last_name, salary, department
FROM employees
WHERE salary >= 73000;

Example 2: Recent hires

sql
SELECT first_name, hire_date, department
FROM employees
WHERE hire_date >= '2022-01-01';

Example 3: Specific department employees

sql
SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Sales';

Common Mistakes to Avoid

Don't make these mistakes:

  1. Forgetting quotes around text: WHERE department = Engineering ❌

    • Correct: WHERE department = 'Engineering' ✓

  2. Using quotes around numbers: WHERE salary = '70000' ❌

    • Correct: WHERE salary = 70000 ✓ (though '70000' often works, it's not ideal)

  3. Using = with NULL: WHERE manager_id = NULL ❌

    • Correct: WHERE manager_id IS NULL ✓

  4. Wrong date format: WHERE hire_date = '01/15/2022' ❌

    • Correct: WHERE hire_date = '2022-01-15' ✓

  5. Case sensitivity issues: WHERE department = 'engineering' ❌

    • Correct: WHERE department = 'Engineering' ✓ (match the actual case)

  6. Mixing single and double quotes: WHERE name = "Alice" ❌

    • Correct: WHERE name = 'Alice' ✓ (use single quotes for strings)

Key Takeaways

What you learned:

✅ WHERE filters rows based on conditions ✅ Comparison operators: =, !=, >, <, >=, <= ✅ Text values need single quotes: 'Engineering' ✅ Numbers don't need quotes: 70000 ✅ Dates use quotes in YYYY-MM-DD format: '2022-01-15' ✅ NULL checks use IS NULL or IS NOT NULL (not = or !=) ✅ WHERE executes before SELECT ✅ You can filter by columns not in your SELECT list

Coming up next: In the next lesson, we'll learn how to combine multiple conditions with AND, OR, and NOT operators to create more powerful filters!

Practice Exercise: Try writing WHERE clauses to:

  1. Find employees in Marketing
  2. Find employees hired before 2022
  3. Find employees with salaries between 65000 and 75000
  4. Find employees without a manager (manager_id IS NULL)
🗄️

SQL Fundamentals

Master SQL from basics to advanced topics. Learn to query, manipulate, and analyze data in relational databases.