WHERE Clause

Learning Focus

Use this lesson to understand WHERE Clause with practical syntax and examples.

Concept Overview

Definition

The WHERE clause filters rows for SQL statements such as SELECT, UPDATE, and DELETE. It defines a condition that determines which rows are included in the operation.

In PostgreSQL, a WHERE condition is evaluated using three-valued logic: it can be TRUE, FALSE, or NULL (unknown). Only rows where the condition evaluates to TRUE are included.

Why is it important?

Without WHERE, you read or modify all rows in a table. That is a correctness risk (wrong results) and a safety risk (accidental bulk updates/deletes). A well-written WHERE clause gives you precision, prevents mistakes, and is often the difference between a fast query and a full table scan.

Where does it fit?

WHERE is used across SQL categories:

• DQL: SELECT ... WHERE ... filters which rows you retrieve
• DML: UPDATE ... WHERE ... and DELETE ... WHERE ... restrict which rows you change

It is also foundational for:

• joins (filtering joined sets)
• indexing (matching predicates to access paths)
• aggregation pipelines (filtering input rows before grouping)

---

Syntax & Rules

Basic Syntax

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

WHERE in UPDATE / DELETE:

UPDATE table_name
SET column1 = value
WHERE condition;

DELETE FROM table_name
WHERE condition;

Available Operators and Patterns

Category	Operator / Pattern	Example
Comparisons	=, !=, <>, <, >, <=, >=	salary >= 60000
Logical	AND, OR, NOT	(a OR b) AND c
Range	BETWEEN ... AND ...	price BETWEEN 10 AND 30
List	IN (...)	status IN ('paid','pending')
NULL	IS NULL, IS NOT NULL	deleted_at IS NULL
Pattern	LIKE, ILIKE	email ILIKE '%@company.com'
Set membership	EXISTS (...)	EXISTS (SELECT 1 FROM ...)
NULL-safe compare	IS DISTINCT FROM	a IS DISTINCT FROM b

Key Rules and Considerations

• Use single quotes for string literals: 'IT', '2026-03-05'.
• Use double quotes only for identifiers (table/column names) when required; quoted identifiers become case-sensitive.
• AND has higher precedence than OR. Use parentheses to make intent explicit.
• NULL cannot be compared with = or !=. Use IS NULL / IS NOT NULL.
• Filters should be index-friendly when performance matters; avoid wrapping filter columns in functions unless you have expression indexes.

---

Step-by-Step Examples

Example 1: Basic Filtering (Beginner)

Create data and filter by a simple equality predicate.

-- Sample data setup
CREATE TABLE employees (
  employee_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL,
  department  text NOT NULL,
  salary      numeric(12,2) NOT NULL CHECK (salary >= 0),
  hire_date   date NOT NULL,
  email       text,
  active      boolean NOT NULL DEFAULT true
);

INSERT INTO employees (full_name, department, salary, hire_date, email, active) VALUES
('John Smith',    'IT',       65000.00, '2020-01-15', 'john.smith@company.com', true),
('Sarah Johnson', 'HR',       55000.00, '2019-03-22', 'sarah.johnson@company.com', true),
('Mike Davis',    'IT',       72000.00, '2021-06-10', 'mike.davis@company.com', true),
('Lisa Brown',    'Finance',  58000.00, '2020-09-05', 'lisa.brown@company.com', true),
('David Wilson',  'IT',       68000.00, '2022-02-18', NULL, true);

-- Query: employees in IT
SELECT employee_id, full_name, department, salary
FROM employees
WHERE department = 'IT'
ORDER BY employee_id;

Expected output:

 employee_id | full_name    | department | salary
-------------+--------------+------------+----------
 1           | John Smith   | IT         | 65000.00
 3           | Mike Davis   | IT         | 72000.00
 5           | David Wilson | IT         | 68000.00
(3 rows)

Explanation:

• department = 'IT' keeps only rows where the department matches exactly.
• The ORDER BY makes results stable for review.

---

Example 2: Numeric Comparisons and Multiple Conditions (Intermediate)

Filter by salary and status using AND.

-- Query: IT employees with salary > 60000
SELECT full_name, department, salary
FROM employees
WHERE department = 'IT'
  AND salary > 60000
ORDER BY salary DESC;

Expected output:

 full_name    | department | salary
--------------+------------+----------
 Mike Davis   | IT         | 72000.00
 David Wilson | IT         | 68000.00
 John Smith   | IT         | 65000.00
(3 rows)

Explanation:

• AND requires both conditions to be true.
• This pattern is index-friendly when you have indexes on filtered columns.

---

Example 3: IN, BETWEEN, and Parentheses (Intermediate)

Use list and range filters together; demonstrate precedence.

-- Query: employees in IT or Finance with salary between 60000 and 70000
SELECT full_name, department, salary
FROM employees
WHERE (department IN ('IT', 'Finance'))
  AND salary BETWEEN 60000 AND 70000
ORDER BY department, salary;

Expected output:

 full_name    | department | salary
--------------+------------+----------
 Lisa Brown   | Finance    | 58000.00
 John Smith   | IT         | 65000.00
 David Wilson | IT         | 68000.00
(3 rows)

Notes:

• BETWEEN 60000 AND 70000 is inclusive on both ends.
• Parentheses make the IN group explicit.

---

Example 4: Pattern Matching, Case Sensitivity, and NULLs (Advanced)

PostgreSQL supports ILIKE for case-insensitive pattern matching.

-- Query: employees with company email domain (case-insensitive)
SELECT full_name, email
FROM employees
WHERE email ILIKE '%@company.com'
ORDER BY full_name;

Expected output:

 full_name     | email
---------------+------------------------------
 John Smith    | john.smith@company.com
 Lisa Brown    | lisa.brown@company.com
 Mike Davis    | mike.davis@company.com
 Sarah Johnson | sarah.johnson@company.com
(4 rows)

Now find employees with missing email values:

SELECT full_name
FROM employees
WHERE email IS NULL
ORDER BY full_name;

Expected output:

 full_name
-------------
 David Wilson
(1 row)

Explanation:

• ILIKE is PostgreSQL-specific and avoids manual LOWER() wrapping.
• IS NULL is required for NULL checks.

---

Example 5: Production-Ready Filtering (Advanced)

Demonstrate safe time-window filtering and index-friendly predicates.

-- Orders table for realistic filtering
CREATE TABLE orders (
  order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  status      text NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
  amount      numeric(12,2) NOT NULL CHECK (amount >= 0),
  created_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO orders (customer_id, status, amount, created_at) VALUES
(42, 'paid',    120.00, '2026-02-01 01:00:00+00'),
(42, 'paid',     90.00, '2026-02-15 12:00:00+00'),
(42, 'pending',  15.00, '2026-02-28 23:59:00+00'),
(42, 'paid',    199.99, '2026-03-01 00:00:00+00'),
(99, 'paid',     49.00, '2026-02-10 10:30:00+00');

-- Index designed for common filters
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

-- Query: orders for customer 42 in February 2026 (half-open interval)
SELECT order_id, status, amount, created_at
FROM orders
WHERE customer_id = 42
  AND created_at >= '2026-02-01'
  AND created_at <  '2026-03-01'
ORDER BY created_at;

Expected output:

 order_id | status  | amount | created_at
----------+---------+--------+---------------------------
 1        | paid    | 120.00 | 2026-02-01 01:00:00+00
 2        | paid    | 90.00  | 2026-02-15 12:00:00+00
 3        | pending | 15.00  | 2026-02-28 23:59:00+00
(3 rows)

Explanation:

• created_at >= start AND created_at < end avoids window overlaps.
• Composite index matches the filter + order pattern.

---

Practical Use Cases

1. Customer Segmentation (Marketing)

Business problem: Target customers in a city and age range.

SELECT customer_id, full_name
FROM customers
WHERE city = 'Chicago'
  AND age BETWEEN 25 AND 35;

2. Inventory Management (Operations)

Business problem: Identify low-stock items.

SELECT sku, product_name, quantity
FROM inventory
WHERE quantity < 10
ORDER BY quantity ASC;

3. Payroll Forecasting (Finance)

Business problem: Find employees in Engineering with high salaries.

SELECT employee_id, full_name, salary
FROM employees
WHERE department = 'Engineering'
  AND salary > 80000;

4. Order Fulfillment Backlog (Support)

Business problem: Pull pending orders for a queue.

SELECT order_id, customer_id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at ASC
LIMIT 100;

5. Compliance Review (Risk)

Business problem: Find large transactions that are not approved.

SELECT transaction_id, amount, approved_at
FROM transactions
WHERE amount > 10000
  AND approved_at IS NULL;

---

Common Mistakes & Troubleshooting

Mistake 1: Using = NULL

Wrong SQL:

SELECT *
FROM employees
WHERE email = NULL;

Bad outcome: returns zero rows (because email = NULL is never TRUE).

Fix:

SELECT *
FROM employees
WHERE email IS NULL;

Mistake 2: Incorrect string quoting

Wrong SQL:

SELECT *
FROM employees
WHERE department = "IT";

Error message: ERROR: column "IT" does not exist

Fix:

SELECT *
FROM employees
WHERE department = 'IT';

Mistake 3: AND/OR precedence confusion

Wrong SQL:

SELECT full_name, department, salary
FROM employees
WHERE department = 'IT'
   OR department = 'HR'
  AND salary > 60000;

Bad outcome: the query is interpreted as IT OR (HR AND salary > 60000).

Fix:

SELECT full_name, department, salary
FROM employees
WHERE (department = 'IT' OR department = 'HR')
  AND salary > 60000;

Mistake 4: Killing index usage with functions

Wrong SQL:

SELECT *
FROM users
WHERE lower(email) = lower('dev@example.com');

Bad outcome: often forces a sequential scan if there is no expression index.

Fix (add expression index):

CREATE INDEX idx_users_email_lower ON users (lower(email));

Fix (use citext, if appropriate):

CREATE EXTENSION IF NOT EXISTS citext;
-- store email as citext to get case-insensitive comparisons

Debugging Tips

1. Start by validating the WHERE clause with SELECT ... LIMIT 10.
2. Inspect table structure and column names with \d table_name in psql.
3. Add parentheses to remove ambiguity when mixing AND and OR.
4. If performance is slow, run EXPLAIN (ANALYZE, BUFFERS) to confirm index usage.

---

Best Practices

✅ Always test a WHERE clause with SELECT before using it in UPDATE or DELETE.
✅ Always use parentheses when mixing AND and OR.
✅ Always use IS NULL / IS NOT NULL for NULL checks.
✅ Always filter timestamps with half-open ranges (>= start AND < end) to avoid overlap.
✅ Always index columns used in frequent filters and sorts.
❌ Avoid using DISTINCT as a band-aid for incorrect joins.
❌ Avoid leading-wildcard LIKE patterns on large tables without the right indexes.
❌ Avoid applying functions to filter columns unless you have expression indexes.

---

Hands-On Practice

Exercise 1 (Easy)

Task: Select all employees hired after 2020-01-01.

-- Write your query here

Solution:

SELECT employee_id, full_name, hire_date
FROM employees
WHERE hire_date > '2020-01-01'
ORDER BY hire_date;

Exercise 2 (Medium)

Task: Find employees whose name starts with S and are active.

-- Write your query here

Solution:

SELECT employee_id, full_name
FROM employees
WHERE full_name ILIKE 's%'
  AND active = true
ORDER BY full_name;

Exercise 3 (Advanced)

Task: Select IT employees with salary between 60000 and 80000 hired in 2020 or later, and with company email domain.

-- Write your query here

Solution:

SELECT full_name, department, salary, hire_date, email
FROM employees
WHERE department = 'IT'
  AND salary BETWEEN 60000 AND 80000
  AND hire_date >= '2020-01-01'
  AND email ILIKE '%@company.com';

---

Connection to Other Concepts

Related lesson	Why it matters
SELECT Statement	WHERE filters which rows a SELECT returns
SELECT DISTINCT	DISTINCT often pairs with WHERE to de-duplicate a filtered set
ORDER BY	Sorting filtered results requires explicit ordering
Filtering and Conditions	Deeper coverage of operators, patterns, and NULL-safe logic
Index Strategy	Indexes determine whether WHERE predicates can be fast
Performance Optimization	EXPLAIN shows whether WHERE uses indexes or scans

---

Visual Learning Diagram

---

Common Pitfalls

Pitfall	Consequence	Prevention
Omitting WHERE in UPDATE/DELETE	Accidental bulk data changes	Always preview with SELECT first and use transactions
Mixing AND/OR without parentheses	Wrong rows matched	Use parentheses to make intent explicit
Comparing NULL with =	Conditions never match	Use IS NULL / IS NOT NULL
Leading-wildcard pattern (%term%) on large tables	Slow scans	Use trigrams/full-text where appropriate
Function-wrapped filters (lower(col) = ...) without index	Index not used	Use expression index or citext

---

Quick Reference

SELECT * FROM t WHERE col = 1;
SELECT * FROM t WHERE col IN (1,2,3);
SELECT * FROM t WHERE col BETWEEN 10 AND 20;
SELECT * FROM t WHERE col IS NULL;
SELECT * FROM t WHERE name ILIKE 'a%';

What's Next

• Previous: SELECT DISTINCT - Review the previous lesson to reinforce context.
• Next: ORDER BY - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.