Where – Guided Study Workspace

Introduction

In real-world business applications, database tables often hold millions of rows. If you query an orders table with 5,000,000 records, asking for every single row will overwhelm network bandwidth, slow down your application, and deliver millions of irrelevant rows.

Instead of retrieving everything, SQL allows you to specify a criteria filter using the WHERE clause. The database tests each row against your condition and returns only the rows that satisfy the condition.

Diagram 1: The WHERE Filtering Pipeline

All Candidate Rows
(e.g. 5,000,000 rows)

➔

WHERE Condition Test
salary > 50000

➔

Filtered Result Set
TRUE ➔ Keep | FALSE ➔ Drop

What Does WHERE Do?

The WHERE clause filters table rows based on a specified boolean condition. For each row scanned in the table, SQL evaluates the condition to TRUE, FALSE, or UNKNOWN (when dealing with NULLs).

SELECT *
FROM employees
WHERE department = 'Sales';

Only rows where department = 'Sales' evaluates strictly to TRUE are included in the final output result set. All other rows are dropped.

Basic WHERE Syntax

The WHERE clause is placed immediately after the FROM clause:

SELECT column1, column2
FROM table_name
WHERE column_name operator value;

SELECT column1, column2: Specifies which vertical columns to output.
FROM table_name: Specifies the source table being queried.
WHERE column_name operator value: The logical filter condition tested against every candidate row.

Comparison Operators

SQL supports standard comparison operators to evaluate relationships between column values and literal constants:

Operator	Meaning	Example Query	Matching Criteria
`=`	Equal to	`WHERE status = 'Active'`	Matches exact value
`<>` or `!=`	Not equal to	`WHERE department <> 'HR'`	Matches anything except HR
`>`	Greater than	`WHERE salary > 60000`	Matches numbers strictly over 60,000
`<`	Less than	`WHERE age < 30`	Matches numbers strictly below 30
`>=`	Greater than or equal to	`WHERE rating >= 4.5`	Matches 4.5 and anything higher
`<=`	Less than or equal to	`WHERE stock <= 10`	Matches 10 and anything lower

Filtering Text Values

When filtering textual columns (such as names, cities, or categories), text values must always be written as string literals wrapped in single quotes:

SELECT name, email
FROM customers
WHERE city = 'Mumbai';

💡

Single Quotes Rule: Writing WHERE city = Mumbai will fail because SQL interprets unquoted words as column identifiers rather than text data.

Filtering Numeric Values

Numeric values (integers, decimals, prices) are written directly without quotation marks:

SELECT name, salary
FROM employees
WHERE salary >= 65000;

Writing quotes around numbers (e.g. WHERE salary > '65000') forces implicit type conversions and should be avoided.

Filtering Dates

In standard SQL, date literals are represented using the ISO-8601 standard format: 'YYYY-MM-DD' enclosed in single quotes:

SELECT order_id, customer_id, total_amount
FROM orders
WHERE order_date = '2026-01-15';

ℹ️

Portability Note: While ISO-8601 ('YYYY-MM-DD') works across PostgreSQL, MySQL, SQLite, and SQL Server, advanced date math functions vary between engines and are covered in dedicated date modules.

Combining Conditions — Basic Introduction

You can combine multiple criteria using basic boolean operators:

AND: All conditions must evaluate to TRUE.
OR: At least one condition must evaluate to TRUE.
NOT: Inverts the boolean result of a condition.

-- Both conditions must match
SELECT name, salary
FROM employees
WHERE salary > 60000 AND city = 'Mumbai';

WHERE and SELECT: Division of Responsibilities

Beginners often wonder how SELECT and WHERE collaborate. The separation of concerns is clean:

🔍 WHERE Clause

Operates horizontally across ROWS. It decides which records survive the filter criteria.

Chooses qualifying rows

📋 SELECT Clause

Operates vertically across COLUMNS. It decides which attributes to display in the result set.

Chooses projected columns

Diagram 2: SELECT vs WHERE Responsibilities

Database Table

➔

WHERE
Filters Rows (Horizontal)

➔

SELECT
Extracts Columns (Vertical)

➔

Output Table

WHERE and NULL — Basic Warning

In SQL, NULL represents missing or unknown data. A very common beginner mistake is writing:

-- ❌ INCORRECT: Returns 0 rows every time!
SELECT * FROM employees WHERE commission = NULL;

⚠️

Why This Fails: Under SQL 3-valued logic, testing equality with NULL evaluates to UNKNOWN, never TRUE. To test for missing data, standard SQL uses IS NULL or IS NOT NULL, which is explored in depth in our dedicated NULL module.

Understanding WHERE Step by Step

Let us trace how SQL processes WHERE salary > 60000 row-by-row on a sample table:

Row Candidate	salary Value	Condition Test: `salary > 60000`	Evaluation Outcome	Action
Rahul (62,000)	62000	`62000 > 60000`	TRUE	✓ Kept in output
Priya (48,000)	48000	`48000 > 60000`	FALSE	✗ Excluded
Amit (85,000)	85000	`85000 > 60000`	TRUE	✓ Kept in output
Sneha (54,000)	54000	`54000 > 60000`	FALSE	✗ Excluded

⚡ Live Interactive SQL WHERE Practice Lab

📦 Candidate Row Evaluation8 Total Candidates

id	name	department	salary	city	Evaluation Status
1	Rahul Sharma	Sales	₹62,000	Mumbai	✓ TRUE (Keep)
2	Priya Patel	HR	₹48,000	Delhi	✗ FALSE (Drop)
3	Amit Verma	Engineering	₹85,000	Bengaluru	✓ TRUE (Keep)
4	Sneha Rao	Sales	₹54,000	Mumbai	✓ TRUE (Keep)
5	Vikram Singh	Engineering	₹92,000	Delhi	✓ TRUE (Keep)
6	Ananya Gupta	HR	₹45,000	Pune	✗ FALSE (Drop)
7	Rohan Joshi	Sales	₹71,000	Bengaluru	✓ TRUE (Keep)
8	Kavita Nair	Engineering	₹68,000	Mumbai	✓ TRUE (Keep)

✍️ SQL Editor● Live Filter Engine

📋 Output Result Set6 Rows Kept (2 Dropped)

id	name	department	salary	city
1	Rahul Sharma	Sales	₹62,000	Mumbai
3	Amit Verma	Engineering	₹85,000	Bengaluru
4	Sneha Rao	Sales	₹54,000	Mumbai
5	Vikram Singh	Engineering	₹92,000	Delhi
7	Rohan Joshi	Sales	₹71,000	Bengaluru
8	Kavita Nair	Engineering	₹68,000	Mumbai

Common WHERE Mistakes

1. Forgetting Quotes Around Strings

WHERE department = Sales causes SQL to look for a column named Sales. Use single quotes: 'Sales'.

2. Writing Operators Backwards (=> or =<)

Comparison operators must be written as >= or <=, never =>.

3. Using = NULL instead of IS NULL

WHERE column = NULL evaluates to UNKNOWN for all rows. Use WHERE column IS NULL.

4. Over-Filtering with Conflicting AND Conditions

Writing WHERE city = 'Mumbai' AND city = 'Delhi' returns 0 rows because no single row can have two different city values simultaneously. Use OR instead.

🧠 Interactive Challenge: Predict The Result (1 of 3)

SELECT name FROM employees WHERE department = 'Sales';

How many employee rows evaluate to TRUE for this condition?

🛠️ Interactive Challenge: Fix The Query (1 of 4)

Fix the syntax error in the text condition.

SELECT name FROM employees WHERE city = Mumbai;

Practical WHERE Exercises

Task Goal	Target Table	Required SQL Solution	Concept Tested
1. Find Mumbai Employees	`employees`	`SELECT * FROM employees WHERE city = 'Mumbai';`	Text equality filter
2. Products Above ₹1,000	`products`	`SELECT title, price FROM products WHERE price > 1000;`	Numeric greater-than test
3. Low-Stock Alert	`inventory`	`SELECT item_id, stock FROM inventory WHERE stock <= 5;`	Boundary condition (<=)
4. Exclude Inactive Users	`users`	`SELECT * FROM users WHERE status <> 'Inactive';`	Not-equal operator (<>)
5. High-Earner Sales Reps	`employees`	`SELECT name FROM employees WHERE department = 'Sales' AND salary > 60000;`	Combined condition (AND)

WHERE Best Practices

Filter Early: Always use WHERE to narrow rows at the database level rather than fetching everything and filtering in application code.
Match Types Carefully: Never put quotes around numeric columns; always put quotes around text and date values.
Index Filtered Columns: Frequently queried columns in WHERE clauses should be indexed in production schemas for sub-millisecond lookups.
Use IS NULL for Missing Data: Avoid = NULL to prevent silent zero-row returns.

What You Should Know Now

✓Purpose of WHERE: Filters candidate rows based on boolean conditions
✓Comparison Operators: =, <>, >, <, >=, <=
✓Text vs Numbers: Text uses single quotes; numbers do not
✓SELECT vs WHERE: SELECT chooses columns; WHERE chooses rows
✓NULL Behavior: Cannot test with =; requires IS NULL
✓Row Evaluation: Rows evaluate to TRUE (keep) or FALSE (drop)

🎯 Knowledge Check Quiz: SQL WHERE

Test your understanding of row filtering, comparison operators, data types, and query logic.

1. What is the primary role of the WHERE clause in a SQL query?

2. Which comparison operator represents "Not Equal to" in standard SQL?

3. Why does "WHERE salary = NULL" fail to return employees with missing salary data?

4. In the query: "SELECT name, city FROM employees WHERE salary > 60000;", how do SELECT and WHERE divide their responsibilities?

5. Given a table with values [10, 20, 30, 40, 50], how many rows will "WHERE score <= 30" return?

6. Why must string/text conditions be written as "WHERE department = 'Sales'" rather than "WHERE department = Sales"?

7. What happens if you execute "SELECT * FROM employees;" without any WHERE clause?

8. What is the correct logical condition to find employees who belong to Sales AND earn more than 60,000?