Filtering Data with WHERE

The WHERE Clause

The WHERE clause filters rows — only rows that match the condition are returned. Without WHERE, your query returns every row in the table.

SELECT columns
FROM table
WHERE condition;

SQL evaluates WHERE before SELECT — it first finds all matching rows, then picks the columns you asked for.

Sample Data

CREATE TABLE employees (
    id         INTEGER PRIMARY KEY,
    name       TEXT    NOT NULL,
    department TEXT    NOT NULL,
    salary     REAL    NOT NULL,
    hire_date  TEXT    NOT NULL,
    is_active  INTEGER DEFAULT 1  -- 1 = active, 0 = inactive
);

INSERT INTO employees VALUES
(1, 'Priya Sharma',  'Finance',    75000, '2022-03-15', 1),
(2, 'Raj Patel',     'Technology', 92000, '2021-07-01', 1),
(3, 'Meera Singh',   'Marketing',  68000, '2023-01-10', 1),
(4, 'Arjun Nair',    'Finance',    81000, '2020-11-22', 1),
(5, 'Sunita Rao',    'Technology', 88000, '2022-09-05', 0),
(6, 'Dev Kumar',     'Marketing',  72000, '2021-04-18', 1),
(7, 'Kiran Mehta',   'Finance',    95000, '2019-06-30', 1),
(8, 'Ananya Das',    'Technology', 63000, '2024-02-14', 1);

Comparison Operators

Operator	Meaning	Example
=	Equal	salary = 75000
!= or <>	Not equal	department != 'Finance'
>	Greater than	salary > 80000
<	Less than	salary < 70000
>=	Greater than or equal	salary >= 80000
<=	Less than or equal	hire_date <= '2022-01-01'

Examples

-- Employees in Finance
SELECT name, salary
FROM employees
WHERE department = 'Finance';

-- Salary over 80,000
SELECT name, salary
FROM employees
WHERE salary > 80000;

-- Hired before 2022
SELECT name, hire_date
FROM employees
WHERE hire_date < '2022-01-01';

-- NOT equal — everyone except Marketing
SELECT name, department
FROM employees
WHERE department <> 'Marketing';

Logical Operators: AND, OR, NOT

Combine multiple conditions with logical operators:

AND — Both Conditions Must Be True

-- Finance employees earning more than 80,000
SELECT name, department, salary
FROM employees
WHERE department = 'Finance'
  AND salary > 80000;

Result: only Arjun Nair (Finance, 81000) and Kiran Mehta (Finance, 95000).

OR — At Least One Condition Must Be True

-- Finance OR Technology employees
SELECT name, department
FROM employees
WHERE department = 'Finance'
   OR department = 'Technology';

NOT — Negates the Condition

-- Everyone except Technology
SELECT name, department
FROM employees
WHERE NOT department = 'Technology';

-- Equivalent
SELECT name, department
FROM employees
WHERE department != 'Technology';

Combining AND, OR, NOT

Use parentheses to control precedence — AND has higher precedence than OR:

-- Finance OR (Technology AND salary > 85000)
-- Without parentheses AND binds tighter
SELECT name, department, salary
FROM employees
WHERE department = 'Finance'
   OR department = 'Technology' AND salary > 85000;

-- Same meaning, explicit with parentheses (preferred)
SELECT name, department, salary
FROM employees
WHERE department = 'Finance'
   OR (department = 'Technology' AND salary > 85000);

-- Finance with salary > 80000 OR Technology with salary > 80000
SELECT name, department, salary
FROM employees
WHERE (department = 'Finance' OR department = 'Technology')
  AND salary > 80000;

Rule: Always use parentheses when mixing AND and OR. Never rely on implicit precedence — it's a common source of bugs.

BETWEEN — Range Conditions

BETWEEN low AND high is inclusive on both ends:

-- Employees earning between 70,000 and 90,000
SELECT name, salary
FROM employees
WHERE salary BETWEEN 70000 AND 90000;

-- Same as
WHERE salary >= 70000 AND salary <= 90000

BETWEEN with Dates

-- Hired in 2022
SELECT name, hire_date
FROM employees
WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31';

NOT BETWEEN

-- Salaries outside 70,000–90,000
SELECT name, salary
FROM employees
WHERE salary NOT BETWEEN 70000 AND 90000;

IN — Match Against a List

IN checks if a value matches any value in a list:

-- Employees in Finance or Marketing
SELECT name, department
FROM employees
WHERE department IN ('Finance', 'Marketing');

-- Same as
WHERE department = 'Finance' OR department = 'Marketing'

IN is much cleaner when you have many values:

-- Check multiple IDs
SELECT name
FROM employees
WHERE id IN (1, 3, 5, 7);

NOT IN

-- Everyone except Finance and Technology
SELECT name, department
FROM employees
WHERE department NOT IN ('Finance', 'Technology');

Watch out: NOT IN with a list that contains NULL returns no rows. This is a classic SQL gotcha. Always ensure your IN list has no NULLs, or use NOT EXISTS instead.

LIKE — Pattern Matching

LIKE matches text patterns using wildcards:

Wildcard	Matches
%	Zero or more characters
_	Exactly one character

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

-- Names ending with 'a'
SELECT name FROM employees WHERE name LIKE '%a';

-- Names containing 'an'
SELECT name FROM employees WHERE name LIKE '%an%';

-- Names with exactly 9 characters
SELECT name FROM employees WHERE name LIKE '_________';

-- Email addresses at meritshot.com
SELECT name, email FROM employees WHERE email LIKE '%@meritshot.com';

LIKE Case Sensitivity

• In PostgreSQL and MySQL: LIKE is case-sensitive by default
• In SQLite: LIKE is case-insensitive for ASCII characters

To do case-insensitive matching in PostgreSQL, use ILIKE:

-- PostgreSQL only
SELECT name FROM employees WHERE name ILIKE 'priya%';

-- Cross-database approach
SELECT name FROM employees WHERE LOWER(name) LIKE 'priya%';

NOT LIKE

-- Employees whose name doesn't start with 'S'
SELECT name FROM employees WHERE name NOT LIKE 'S%';

NULL Checks: IS NULL and IS NOT NULL

You cannot compare NULL with =. Always use IS NULL or IS NOT NULL:

-- Employees without a manager (manager_id is NULL)
SELECT name FROM employees WHERE manager_id IS NULL;

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

-- This NEVER finds NULL rows (always empty result)
SELECT * FROM employees WHERE manager_id = NULL;   -- WRONG!

-- This correctly finds NULL rows
SELECT * FROM employees WHERE manager_id IS NULL;  -- CORRECT

Practical Examples

Example 1: Active Finance Employees Earning Over 75k

SELECT name, salary, hire_date
FROM employees
WHERE department = 'Finance'
  AND salary > 75000
  AND is_active = 1;

Example 2: Technology or Marketing — Mid-Range Salaries

SELECT name, department, salary
FROM employees
WHERE department IN ('Technology', 'Marketing')
  AND salary BETWEEN 65000 AND 85000;

Example 3: Recent Hires with Specific Name Pattern

SELECT name, hire_date, department
FROM employees
WHERE hire_date >= '2023-01-01'
  AND name LIKE '%a%';

Example 4: Inactive or Low-Paid

SELECT name, salary, is_active
FROM employees
WHERE is_active = 0
   OR salary < 70000;

WHERE in UPDATE and DELETE

WHERE isn't just for SELECT — it's equally critical in UPDATE and DELETE:

-- Update salary only for Technology employees
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Technology';

-- Delete only inactive employees
DELETE FROM employees
WHERE is_active = 0;

Warning: An UPDATE or DELETE without WHERE affects every row. Before running either in production, always test with SELECT first using the same WHERE condition.

Execution Order Tip

SQL processes clauses in this logical order:

FROM → WHERE → SELECT → ORDER BY

This means:

• Column aliases defined in SELECT cannot be used in WHERE (WHERE runs before SELECT)
• The WHERE condition accesses raw column values, not computed aliases

-- Wrong — 'annual_salary' alias not available in WHERE
SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 900000;  -- Error!

-- Correct — repeat the expression
SELECT salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 900000;

Common Mistakes

1. Using = for NULL

-- Wrong
WHERE email = NULL

-- Correct
WHERE email IS NULL

2. Forgetting quotes around strings

-- Wrong
WHERE department = Finance

-- Correct
WHERE department = 'Finance'

3. OR without parentheses

-- Probably not what you intended
WHERE department = 'Finance' OR department = 'Technology' AND salary > 80000
-- Reads as: Finance OR (Technology AND salary > 80000)

-- Clear intent with parentheses
WHERE (department = 'Finance' OR department = 'Technology') AND salary > 80000

4. BETWEEN is inclusive — not exclusive

-- This INCLUDES both 70000 and 90000
WHERE salary BETWEEN 70000 AND 90000

5. NOT IN with NULLs

-- If any value in the list is NULL, this returns NO rows
WHERE department NOT IN ('Finance', NULL)  -- always empty!

Practice Exercises

1. Find all employees in the Technology department with a salary above 85,000.
2. List employees hired between 2021 and 2022 (inclusive both years).
3. Find employees whose name contains the letter 'i' (case-insensitive).
4. Show all employees NOT in Finance or Marketing.
5. Find employees with a salary between 70,000 and 90,000 who are active (is_active = 1).

Summary

In this chapter you learned:

• WHERE filters rows before they are returned by SELECT
• Comparison operators: =, !=, <>, >, <, >=, <=
• Logical operators: AND (both true), OR (either true), NOT (negation)
• Use parentheses when mixing AND and OR
• BETWEEN low AND high — inclusive range filter
• IN (val1, val2, ...) — match against a list; NOT IN for exclusion
• LIKE '%pattern%' — text pattern matching with % and _ wildcards
• IS NULL / IS NOT NULL — the only correct way to check for NULL
• Column aliases from SELECT are not available in WHERE

Next up: Sorting and LIMIT — learn to order your results and retrieve just the top or bottom rows.