Filtering Rows with WHERE Clause0%

Filtering Rows with WHERE Clause

Beginner12 min read•Updated: 2026-09-12

Filtering Rows with WHERE: Predicates and Boolean Logic

Without filtering, every query would dump an entire table containing millions of rows over the network. The WHERE clause specifies search conditions (predicates) that filter rows before they are returned to the client. Only rows for which the WHERE predicate evaluates to TRUE are included in the result set.

1. Syntax of the `WHERE` Clause

The WHERE clause appears immediately after the FROM clause:

1SELECT column_1, column_2
2FROM table_name
3WHERE search_condition;

2. How the Database Executes `WHERE`

Understanding the logical execution order of a SQL query explains why query writing behaves the way it does:

 Logical Order of Operations:
 1. FROM       <-- Identify and load source table
 2. WHERE      <-- Filter rows based on condition (DISCARDS FALSE/UNKNOWN ROWS)
 3. SELECT     <-- Project specific requested columns & compute expressions
 4. ORDER BY   <-- Sort the remaining rows
 5. LIMIT      <-- Restrict row count

Because the WHERE clause executes in Step 2, it filters out unwanted rows before expressions are calculated or memory is allocated for sorting in Step 4.

3. Filtering on Text, Numbers, and Dates

1-- Numeric Filtering: Find products costing more than ₹5,000
2SELECT product_name, price 
3FROM products 
4WHERE price > 5000.00;
5 
6-- String Filtering: Find customer by exact email
7-- (Case-insensitive by default under utf8mb4_0900_ai_ci collation)
8SELECT * 
9FROM customers 
10WHERE email = 'aarav@example.com';
11 
12-- Date Filtering: Find orders placed on or after Jan 1, 2026
13SELECT order_id, order_date, total_amount 
14FROM orders 
15WHERE order_date >= '2026-01-01';

4. SQL Three-Valued Logic in WHERE Clauses

SQL does not use simple binary logic (True / False); it uses Three-Valued Logic:

1
TRUE: The condition is met. Row is included.
2
FALSE: The condition is not met. Row is discarded.
3
UNKNOWN: The condition involves a NULL. Row is discarded!

1-- If an employee has salary = NULL:
2SELECT * FROM employees WHERE salary > 50000;
3-- The expression (NULL > 50000) evaluates to UNKNOWN!
4-- The row is NOT included in the result set.

5. Best Practices & Common Pitfalls

Avoid Functions on Indexed Columns in WHERE: Wrapping an indexed column inside a function prevents MySQL from using the index (Non-Sargable Query):

-- SLOW (Full Table Scan): The index on order_date cannot be used!
SELECT * FROM orders WHERE YEAR(order_date) = 2026;

-- FAST (Index Range Scan): The index is fully utilized!
SELECT * FROM orders WHERE order_date >= '2026-01-01' AND order_date <= '2026-12-31';

String Quoting: Always enclose string and date literals in single quotes ('Engineering', '2026-01-01'). Numbers do not use quotes (5000).

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In the logical execution order of a SQL query, when is the WHERE clause evaluated?

Question 2

How does the WHERE clause treat a row when its condition evaluates to UNKNOWN due to a NULL value?

Question 3

Why is WHERE YEAR(created_at) = 2026 slower on a large indexed table than WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'?

Question 4

Which character is the standard SQL literal delimiter for string and date values in a WHERE clause?

Question 5

What will the query SELECT * FROM products WHERE stock_quantity = 0; return?

Next Lesson

Comparison Operators in SQL

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
SELECT Statement & Column Aliases	Comparison Operators in SQL

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Comparison Operators in SQL

Filtering Rows with WHERE Clause

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Filtering Rows with WHERE: Predicates and Boolean Logic

1. Syntax of the `WHERE` Clause

The WHERE clause appears immediately after the FROM clause:

1SELECT column_1, column_2
2FROM table_name
3WHERE search_condition;

2. How the Database Executes `WHERE`

Understanding the logical execution order of a SQL query explains why query writing behaves the way it does:

 Logical Order of Operations:
 1. FROM       <-- Identify and load source table
 2. WHERE      <-- Filter rows based on condition (DISCARDS FALSE/UNKNOWN ROWS)
 3. SELECT     <-- Project specific requested columns & compute expressions
 4. ORDER BY   <-- Sort the remaining rows
 5. LIMIT      <-- Restrict row count

Because the WHERE clause executes in Step 2, it filters out unwanted rows before expressions are calculated or memory is allocated for sorting in Step 4.

3. Filtering on Text, Numbers, and Dates

1-- Numeric Filtering: Find products costing more than ₹5,000
2SELECT product_name, price 
3FROM products 
4WHERE price > 5000.00;
5 
6-- String Filtering: Find customer by exact email
7-- (Case-insensitive by default under utf8mb4_0900_ai_ci collation)
8SELECT * 
9FROM customers 
10WHERE email = 'aarav@example.com';
11 
12-- Date Filtering: Find orders placed on or after Jan 1, 2026
13SELECT order_id, order_date, total_amount 
14FROM orders 
15WHERE order_date >= '2026-01-01';

4. SQL Three-Valued Logic in WHERE Clauses

SQL does not use simple binary logic (True / False); it uses Three-Valued Logic:

1
TRUE: The condition is met. Row is included.
2
FALSE: The condition is not met. Row is discarded.
3
UNKNOWN: The condition involves a NULL. Row is discarded!

1-- If an employee has salary = NULL:
2SELECT * FROM employees WHERE salary > 50000;
3-- The expression (NULL > 50000) evaluates to UNKNOWN!
4-- The row is NOT included in the result set.

5. Best Practices & Common Pitfalls

Avoid Functions on Indexed Columns in WHERE: Wrapping an indexed column inside a function prevents MySQL from using the index (Non-Sargable Query):

-- SLOW (Full Table Scan): The index on order_date cannot be used!
SELECT * FROM orders WHERE YEAR(order_date) = 2026;

-- FAST (Index Range Scan): The index is fully utilized!
SELECT * FROM orders WHERE order_date >= '2026-01-01' AND order_date <= '2026-12-31';

String Quoting: Always enclose string and date literals in single quotes ('Engineering', '2026-01-01'). Numbers do not use quotes (5000).

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In the logical execution order of a SQL query, when is the WHERE clause evaluated?

Question 2

How does the WHERE clause treat a row when its condition evaluates to UNKNOWN due to a NULL value?

Question 3

Why is WHERE YEAR(created_at) = 2026 slower on a large indexed table than WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'?

Question 4

Which character is the standard SQL literal delimiter for string and date values in a WHERE clause?

Question 5

What will the query SELECT * FROM products WHERE stock_quantity = 0; return?

Next Lesson

Comparison Operators in SQL

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
SELECT Statement & Column Aliases	Comparison Operators in SQL

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Filtering Rows with WHERE: Predicates and Boolean Logic

1. Syntax of the WHERE Clause

2. How the Database Executes WHERE

3. Filtering on Text, Numbers, and Dates

4. SQL Three-Valued Logic in WHERE Clauses

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Comparison Operators in SQL

Related Lessons

Practice Quiz

Loading Page Content...

Filtering Rows with WHERE: Predicates and Boolean Logic

1. Syntax of the WHERE Clause

2. How the Database Executes WHERE

3. Filtering on Text, Numbers, and Dates

4. SQL Three-Valued Logic in WHERE Clauses

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Comparison Operators in SQL

Related Lessons

Practice Quiz

1. Syntax of the `WHERE` Clause

2. How the Database Executes `WHERE`

1. Syntax of the `WHERE` Clause

2. How the Database Executes `WHERE`