SQL Tutorial - WHERE Clause

The WHERE clause is a fundamental component of the SELECT statement, used to filter rows based on specified conditions. It determines which records are included in the query result, allowing you to hone in on the data you need and exclude the rest. Efficient use of WHERE is critical for both correctness and performance.

1. Purpose of WHERE

By applying conditions to columns, WHERE eliminates unwanted rows before any grouping or aggregation. This early filtering reduces data volume and improves query speed, as subsequent operations work on a smaller dataset.

2. Syntax and Placement

SELECT column_list
FROM table_name
WHERE condition1 [AND|OR condition2 ...];

The WHERE clause appears after FROM (and any JOIN) but before GROUP BY, HAVING, ORDER BY, and LIMIT.

3. Comparison Operators

Basic comparisons:

= : equal

<> or != : not equal

<, >, <=, >= : less/greater than (or equal)

Example: Equality and Inequality

SELECT *
FROM employees
WHERE department = 'Engineering'
  AND salary >= 70000;

This returns only Engineering employees earning at least 70,000.

4. Range and Set Filters

BETWEEN

SELECT first_name, salary
FROM employees
WHERE salary BETWEEN 60000 AND 80000;

Includes salaries from 60,000 through 80,000 inclusive.

SELECT *
FROM employees
WHERE department IN ('HR', 'Finance', 'Sales');

Selects employees whose department matches any listed value. More concise than multiple ORs.

5. Pattern Matching with LIKE

LIKE enables wildcard searches on text columns:

% matches zero or more characters.

_ matches exactly one character.

Examples

-- Names starting with 'A'
SELECT * FROM employees WHERE first_name LIKE 'A%';

-- Emails ending with domain
SELECT * FROM employees WHERE email LIKE '%@example.com';

-- Phone numbers with format '123-456-7890'
SELECT * FROM contacts WHERE phone LIKE '___-___-____';

6. NULL Handling

In SQL, NULL represents unknown or missing values. Standard comparisons (= NULL) do not work; use IS NULL or IS NOT NULL.

Examples

SELECT * FROM employees WHERE manager_id IS NULL;

SELECT * FROM orders WHERE shipped_date IS NOT NULL;

The first query finds employees with no manager assigned; the second finds orders that have been shipped.

7. Combining Conditions

Use AND, OR, and parentheses to build complex filters.

Example

SELECT *
FROM employees
WHERE (department = 'Sales' OR department = 'Marketing')
  AND salary > 65000
  AND hire_date < '2024-01-01';

Parentheses ensure OR logic is evaluated before the AND with salary and hire date.

8. Performance Considerations

Indexed Columns: Filter on indexed columns to leverage fast lookups.

Avoid Functions: Wrapping columns in functions (e.g., UPPER(name) = 'ALICE') prevents index use.

Selective Predicates: Use highly selective conditions to reduce scanned rows.

Statistics: Keep table statistics current so the optimizer makes good choices.

9. Complex Examples

Subquery in WHERE

SELECT first_name, last_name
FROM employees
WHERE dept_id = (
  SELECT dept_id FROM departments WHERE name = 'Engineering'
);

Correlated Subquery

SELECT e.first_name, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id
);

Each employee’s salary is compared to the average salary of their own department.

10. Summary

The WHERE clause is an indispensable tool for precise data retrieval. By mastering comparisons, pattern matching, NULL checks, and logical operators—and by applying performance best practices—you’ll write queries that return exactly the data you need, efficiently and accurately.