SQL - Where Clause

SQL - The WHERE Clause

The SQL WHERE Clause

The SQL WHERE clause is one of the most fundamental and powerful parts of the language. It is used to filter records and extract only those that fulfill a specified condition. It is used with Data Manipulation Language (DML) statements like SELECT, UPDATE, and DELETE to specify which rows should be affected.

Syntax

The basic syntax places the WHERE clause after the FROM clause (in a SELECT) or table name (in UPDATE/DELETE).

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

Let’s set up a sample table for our examples.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);

INSERT INTO employees VALUES
(1, 'Alice', 'Smith', 'Engineering', 90000, '2020-03-15'),
(2, 'Bob', 'Johnson', 'Engineering', 85000, '2021-07-22'),
(3, 'Charlie', 'Brown', 'HR', 65000, '2019-01-10'),
(4, 'Diana', 'Prince', 'Sales', 72000, '2022-05-30'),
(5, 'Ethan', 'Hunt', 'Sales', NULL, '2023-11-01');

WHERE with SELECT, UPDATE, and DELETE

The WHERE clause is essential for targeted data operations.

-- SELECT: Get all employees in the 'Engineering' department.
SELECT first_name, last_name, salary FROM employees
WHERE department = 'Engineering';

-- UPDATE: Give all employees in the 'Sales' department a 10% raise.
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Sales';

-- DELETE: Remove an employee with a specific ID.
DELETE FROM employees
WHERE employee_id = 3;

CRITICAL: Never forget the WHERE clause on an UPDATE or DELETE statement unless you intend to modify or delete every single row in the table. This is a common and destructive mistake.

Comparison and Logical Operators

You can build complex conditions using a variety of operators.

• Comparison: =, != or <>, >, <, >=, <=
• Range: BETWEEN ... AND ... (inclusive)
• Logical: AND, OR, NOT

Example: Combining Operators

Find all employees in the ‘Engineering’ department who earn more than $86,000, or any employee hired in 2022. Note the use of parentheses to control the order of operations (AND is evaluated before OR).

SELECT first_name, department, salary, hire_date FROM employees
WHERE (department = 'Engineering' AND salary > 86000)
   OR hire_date BETWEEN '2022-01-01' AND '2022-12-31';

Output

first_name	department	salary	hire_date
Alice	Engineering	90000.00	2020-03-15
Diana	Sales	79200.00	2022-05-30

The IN and NOT IN Operators

The IN operator provides a concise way to check if a value matches any value in a list. It is often more readable than multiple OR conditions.

-- Find employees in the HR or Sales departments.
SELECT first_name, department FROM employees
WHERE department IN ('HR', 'Sales');

The NOT IN with NULL Pitfall

Be very careful when using NOT IN with a subquery that might return NULL values. A comparison to NULL is neither true nor false, but ‘unknown’. This can lead to unexpected empty result sets.

-- This might not return what you expect if any employee has a NULL salary.
SELECT * FROM employees WHERE employee_id NOT IN (SELECT employee_id FROM employees WHERE salary IS NULL);

-- A safer alternative is often to use NOT EXISTS.
SELECT * FROM employees e1 WHERE NOT EXISTS (SELECT 1 FROM employees e2 WHERE e2.salary IS NULL AND e1.employee_id = e2.employee_id);

Pattern Matching with LIKE

The LIKE operator is used for string pattern matching. It uses two main wildcards:

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

Example

-- Find employees whose last name starts with 'S'.
SELECT * FROM employees WHERE last_name LIKE 'S%';

-- Find employees whose last name ends with 'n'.
SELECT * FROM employees WHERE last_name LIKE '%n';

-- Find employees whose first name has 'o' as the second letter.
SELECT * FROM employees WHERE first_name LIKE '_o%';

Handling NULL Values

NULL represents a missing or unknown value. You cannot test for NULL with standard comparison operators like = or !=. You must use IS NULL or IS NOT NULL.

Example

-- This is INCORRECT and will not return any rows.
SELECT * FROM employees WHERE salary = NULL;

-- This is the CORRECT way to find employees with an unknown salary.
SELECT * FROM employees WHERE salary IS NULL;