WHERE & Building Conditions

A table can hold millions of rows, but you rarely want all of them. A selector wants only Indian players, a fantasy-cricket app wants players with more than 500 runs, a fan wants everyone from MI or CSK. The WHERE clause is how you tell SQL which rows to keep. This chapter goes through every WHERE operator one by one.

Definition

The WHERE clause filters the rows of a table. It evaluates a condition (a search condition / predicate) for every row and keeps only the rows for which the condition is TRUE. Rows where the condition is FALSE or UNKNOWN are removed from the result.

Let us unpack that:

• Filters rows — WHERE works on rows, not columns. Choosing columns is the job of SELECT.
• For every row — SQL checks the condition separately for each row, one at a time.
• Condition / predicate — an expression whose answer is TRUE, FALSE or UNKNOWN (UNKNOWN happens when a NULL is involved — covered in the IS NULL topic).
• Only TRUE survives — "not sure" (UNKNOWN) is treated the same as "no". This one rule explains many NULL surprises later.

Syntax

SELECT column_list
FROM table_name
WHERE condition;

How a condition is built

Every simple condition has the same shape — compare two things:

Condition = Expression + Operator + Expression

An expression is anything that produces a value. The left side is usually a column, but it can be any of these:

Text values go inside single quotes ('India'); numbers do not (500).

The five operator families

Plus IS NULL / IS NOT NULL for checking missing values.

Example — only Indian players

Our players table has 6 rows. Let us keep only the Indian ones.

SELECT *
FROM players
WHERE country = 'India';

How it works (execution order):

1. FROM players — SQL loads all 6 rows.
2. WHERE country = 'India' — checks each row; Jos Buttler (England) and David Warner (Australia) give FALSE and are dropped.
3. SELECT * — shows all columns of the 4 surviving rows.

Note: You write SELECT first, but SQL runs FROM → WHERE → SELECT. WHERE filters rows before grouping (GROUP BY) and before SELECT builds the output.

Example — a function and a subquery in the condition

SELECT name, runs
FROM players
WHERE runs = (SELECT MAX(runs) FROM players);

The subquery runs first and returns 850; the condition then becomes runs = 850.

Common mistakes

• ❌ WHERE country = India → SQL thinks India is a column name. ✅ WHERE country = 'India'
• ❌ WHERE country = "India" → in SQL Server double quotes mean an identifier (a name), not text. ✅ use single quotes.
• ❌ Putting WHERE before FROM: SELECT * WHERE runs > 500 FROM players. ✅ FROM then WHERE.
• ❌ Using a SELECT alias in WHERE: SELECT runs / 2 AS half ... WHERE half > 100 fails, because WHERE runs before SELECT creates the alias. ✅ repeat the expression: WHERE runs / 2 > 100.
• ❌ Using aggregates in WHERE: WHERE SUM(runs) > 1000. ✅ that belongs in HAVING (after GROUP BY).

Interview tip

Q: What is the difference between WHERE and HAVING? A: WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates like SUM() or COUNT().