Filtering Rows — WHERE Clause

The WHERE clause filters rows in a SQL query — it specifies conditions that each row must satisfy to be included in the result. Only rows where the WHERE condition evaluates to TRUE are passed forward. Rows where the condition is FALSE or NULL are discarded.

WHERE is executed before SELECT in the query execution order. The full execution order is: FROM (identify the source table), WHERE (filter rows), GROUP BY (group filtered rows), HAVING (filter groups), SELECT (compute output columns from remaining rows), ORDER BY (sort), LIMIT (cut to n rows). This sequence has important consequences: you cannot reference a SELECT alias in a WHERE clause because WHERE runs before SELECT and the alias does not yet exist. You cannot use aggregate functions (COUNT, SUM, AVG) in WHERE because aggregation happens after WHERE — use HAVING instead for filtering aggregate results.

Performance-wise, WHERE is the most important clause for query speed. An effective WHERE condition on an indexed column can reduce the rows the database processes from 500 million to 100 — a 5-million-fold reduction in work before SELECT, ORDER BY, or any other clause runs.

NULL represents the absence of a value — it means "unknown" or "not applicable." In SQL, any comparison involving NULL returns NULL, not TRUE or FALSE. This is called three-valued logic. The expression column = NULL does not return TRUE for rows where column is NULL — it returns NULL. And the WHERE clause discards rows where the condition result is NULL (not just FALSE). So WHERE column = NULL will always return zero rows, regardless of what the data contains.

This behaviour is technically correct but surprises almost every SQL beginner. NULL means "we don't know the value" — and "we don't know" = "we don't know" does not logically produce a definitive TRUE answer, so SQL returns NULL instead.

To check for NULL, SQL provides the IS NULL and IS NOT NULL operators. These are specifically designed to handle the three-valued logic of NULL: IS NULL returns TRUE when the value is NULL, and IS NOT NULL returns TRUE when the value is anything other than NULL. WHERE delivery_date IS NULL finds all rows where delivery_date has not been set. WHERE delivery_date IS NOT NULL finds all rows where it has been set to a real date. These are the only correct ways to filter NULL values.

WHERE and HAVING both filter data, but they operate at different stages of query execution and on different things. WHERE filters individual rows before any grouping or aggregation. It operates on raw column values and runs early in the execution pipeline — immediately after FROM. HAVING filters groups after GROUP BY has aggregated rows, and it operates on aggregate values like COUNT(), SUM(), AVG(). HAVING runs after SELECT in the execution order.

A concrete example: to find the total revenue per city, but only for cities with more than ₹5,000 in delivered orders, you need both. WHERE order_status = 'Delivered' filters individual rows before the city grouping. HAVING SUM(total_amount) > 5000 filters the city groups after their revenue has been computed. You cannot use WHERE SUM(total_amount) > 5000 because SUM() does not exist yet when WHERE runs.

Performance difference: WHERE filters early and reduces the number of rows that GROUP BY must process. HAVING filters late after all aggregation is done. For performance, always move filtering to WHERE when possible and use HAVING only for conditions that genuinely require aggregate values. If you can express a filter in WHERE instead of HAVING, always do — it reduces the work done by GROUP BY.

A full table scan is when the database reads every page of a table from start to finish to find rows that match a WHERE condition. On a small table this is fine. On a table with 500 million rows stored across thousands of disk pages, a full table scan can take minutes or longer — and blocks other queries from running efficiently.

Whether WHERE causes a full table scan depends on whether the filtered column has an index. If the column has a B-tree index, the database uses the index to jump directly to the matching rows without reading the whole table — an index lookup. This turns a minutes-long full scan into a sub-millisecond operation. If the column has no index, the database has no choice but to read every row.

WHERE conditions that prevent index usage include: applying a function to the column (WHERE UPPER(city) = 'BANGALORE' cannot use an index on city), using OR conditions across different columns without a composite index, and using leading wildcards in LIKE (WHERE email LIKE '%gmail.com' cannot use an index because the prefix is unknown). The solution for function-wrapped columns is to rewrite the condition: WHERE city = 'Bangalore' (case-sensitive, uses index) or add a functional index on UPPER(city). Understanding when WHERE triggers a full scan versus an index lookup is the foundation of query optimisation.

String comparison in WHERE checks whether a column value matches a specified string. In PostgreSQL, SQLite, and DuckDB (used in the Chaduvuko playground), string comparisons are case-sensitive by default — the exact characters must match including capitalisation. 'Bangalore' does not equal 'bangalore' or 'BANGALORE'. In MySQL, string comparisons are case-insensitive by default because MySQL's default collation (utf8mb4_general_ci, where ci stands for case-insensitive) treats uppercase and lowercase as equivalent.

A real example of a case-sensitivity issue: an analyst queries WHERE city = 'bangalore' on a PostgreSQL database where cities are stored as 'Bangalore' (capital B). The query returns zero rows — not because there are no customers in Bangalore, but because 'bangalore' and 'Bangalore' are treated as different strings. The analyst might incorrectly conclude the data is missing.

Solutions for case-insensitive matching: use LOWER(city) = 'bangalore' to convert both sides to lowercase before comparing — this works in all databases but prevents index usage on city. In PostgreSQL, use the ILIKE operator (case-insensitive LIKE) as an alternative: WHERE city ILIKE 'bangalore'. For production systems where case-insensitive search is frequent, create a functional index on LOWER(city) so that WHERE LOWER(city) = 'bangalore' can use the index. The safest long-term practice is to standardise data capitalisation on insert — store all cities as 'Bangalore' consistently, then case-sensitive comparison is always correct and fast.

Cause: You are comparing a numeric column (customer_id is INTEGER) with a string value ('1' in quotes). PostgreSQL is strict about type matching — it will not silently convert '1' to 1 for comparison. This happens when you paste code from a tool that adds quotes around all values regardless of type, or when column types are not what you expect.

Fix: Remove the quotes from numeric values: WHERE customer_id = 1 — no quotes. PostgreSQL will not implicitly cast a string to an integer in a comparison. If you are generating queries dynamically in code, use parameterised queries with the correct Python/JS type (integer, not string) for the value. MySQL is more permissive and will silently cast '1' to 1 — which is why the same code works in MySQL but fails in PostgreSQL.

Cause: The date string is in DD-MM-YYYY format but PostgreSQL (and most databases) expect YYYY-MM-DD (ISO 8601 format). PostgreSQL tries to parse the string as a date and fails because '15' is not a valid year in the YYYY position of the default format. This is an extremely common error when dates come from a spreadsheet, a user form, or a non-technical source.

Fix: Always use YYYY-MM-DD format in SQL queries: WHERE order_date = '2024-01-15'. If your data is stored in a different format, convert it at insertion time. If you must parse a non-standard format at query time, use TO_DATE() in PostgreSQL: WHERE order_date = TO_DATE('15-01-2024', 'DD-MM-YYYY'). In MySQL use STR_TO_DATE(): WHERE order_date = STR_TO_DATE('15-01-2024', '%d-%m-%Y').

Cause: This is not a database error but a logic error — your WHERE condition does not match any rows even though you expect it to. Most common causes: string value is wrong case (WHERE city = 'bangalore' but data stores 'Bangalore'), extra whitespace in the stored value (WHERE email = 'aisha@gmail.com' but stored as ' aisha@gmail.com' with a leading space), using = NULL instead of IS NULL, or the value genuinely does not exist in the table.

Fix: Debug systematically. First, run SELECT * FROM table LIMIT 10 to see actual stored values. Check case and whitespace carefully. If checking for NULL, use IS NULL not = NULL. Use LOWER() on both sides to rule out case issues: WHERE LOWER(city) = LOWER('Bangalore'). Use TRIM() to rule out whitespace: WHERE TRIM(email) = 'aisha@gmail.com'. Once you find the exact stored value, update your WHERE condition to match it precisely.

Cause: You gave a computed column an alias (AS total) in SELECT and then tried to use that alias in WHERE. This fails because WHERE executes before SELECT in SQL's logical processing order — the alias 'total' does not exist yet when WHERE is evaluated. This is one of the most common SQL logic errors for beginners who expect aliases to be available throughout the query.

Fix: Repeat the expression in WHERE instead of using the alias: WHERE total_amount > 1000 (use the original column name), or WHERE (unit_price - cost_price) > 100 (repeat the calculation). Alternatively, wrap your query in a subquery or CTE (covered in Modules 36 and 55) where the inner query defines the alias and the outer query filters on it: SELECT * FROM (SELECT *, unit_price - cost_price AS margin FROM products) t WHERE margin > 100. Note: ORDER BY is the one clause where aliases ARE available, because ORDER BY executes after SELECT.

Cause: This is not an error but a critical warning to understand. An UPDATE or DELETE with a WHERE clause that matches zero rows executes successfully — the database reports 0 rows affected. This happens when: the ID does not exist, the value has a typo, or the condition is too restrictive. The danger is in the opposite case: a WHERE clause that is less restrictive than intended can affect many more rows than you expected, including wrong rows.

Fix: Before running any UPDATE or DELETE with a WHERE clause, always run the equivalent SELECT with the same WHERE to see which rows will be affected: SELECT * FROM customers WHERE customer_id = 999 — if this returns 0 rows, your UPDATE will also affect 0 rows. Fix the condition before running the destructive operation. As a habit: SELECT first, then UPDATE or DELETE. Many SQL clients have a safe mode that requires you to explicitly confirm bulk operations.

The FreshMart operations team needs a report of all high-value delivered orders placed in February 2024 — specifically orders with a total_amount above ₹800 that were successfully delivered. Show the order_id, order_date, delivery_date, payment_method, and total_amount. Sort by total_amount descending.