WHERE Clause
Overview
The WHERE clause is your primary filter. Without it, SELECT will return every single row in the table. WHERE evaluates a conditional statement for every row; if the condition evaluates to TRUE, the row is included in the final result. If FALSE or NULL, the row is discarded. This is the foundation of fetching specific data, like finding a user by their ID or finding all products under $20.
Syntax
-- Basic Syntax: SELECT columns FROM table WHERE condition;
-- 1. Exact Match Filter (Strings require single quotes!)
SELECT * FROM users
WHERE status = 'active';
-- 2. Numeric Filter
SELECT title, price FROM books
WHERE price = 15.99;
-- 3. Date Filter (Dates are treated as strings in queries)
SELECT * FROM orders
WHERE order_date = '2026-10-31';Common Pitfalls
- Using double quotes (
"active") for string values. In standard SQL, single quotes ('active') are strictly for string literal data, while double quotes ("user_name") are strictly for referencing column or table names. - Filtering on functions (e.g.,
WHERE YEAR(created_at) = 2026). If you wrap a column in a function, the database is forced to run that function on every single row before it can evaluate the WHERE clause. This destroys Indexing (creating a slow Full Table Scan). Always try to compare the raw column against a calculated value instead.
Interview Questions
SELECT query with a WHERE clause?Counter-intuitively, the database executes the FROM clause first (to locate the table), then executes the WHERE clause (to filter the rows), and only THEN executes the SELECT clause (to extract the specific columns). This means you cannot use a SELECT alias inside a WHERE clause!
Real-World Example
Fetching a specific user for a login authentication API.
-- The backend passes the email safely via parameterized queries
SELECT id, password_hash, is_banned
FROM users
WHERE email = 'admin@company.com';Check Your Knowledge
Test your understanding of WHERE Clause with these quick questions.