Filtering Data (The WHERE Clause)

The WHERE clause is used in SQL to filter the records returned by a SELECT statement, or those affected by UPDATE and DELETE statements. It specifies a condition that must be true for a record to be included in the result set or manipulation operation.

1. Basic Syntax and Comparison Operators

The WHERE clause is always placed immediately after the FROM clause. It uses standard comparison operators to test the values in a column.

Execution Example: Filtering by Value

Let’s retrieve employees whose salary is exactly $72,000.00.

SELECT first_name, last_name, salary
FROM Employees
WHERE salary = 72000.00;

Execution Example: Filtering by Range

To retrieve employees hired after a specific date:

SELECT first_name, hire_date
FROM Employees
WHERE hire_date > '2023-01-01';

2. Logical Operators (AND, OR, NOT)

Logical operators combine multiple conditions in the WHERE clause, allowing for highly specific filtering.

• AND: Returns a record only if both the left and right conditions are true.
• OR: Returns a record if at least one of the conditions is true.
• NOT: Reverses the result of any condition (e.g., NOT salary > 60000 is the same as salary <= 60000).

Execution Example: Using AND

Retrieve employees who have a salary greater than $65,000 AND were hired in 2023.

SELECT first_name, salary, hire_date
FROM Employees
WHERE salary > 65000 
AND hire_date BETWEEN '2023-01-01' AND '2023-12-31';

Tip: Use parentheses () to enforce the order of operations if combining AND and OR to ensure clarity.

3. Special Filtering Operators

These operators are designed for specific, common filtering tasks.

4. Pattern Matching with LIKE

The LIKE operator is used to search for specified patterns in a text column. It requires the use of two wildcards:

• % (Percent Sign): Represents zero, one, or multiple characters.
• _ (Underscore): Represents exactly one single character.

Execution Example: Using LIKE

Retrieve employees whose email address contains the word “example”:

SELECT first_name, email
FROM Employees
WHERE email LIKE '%example%';