WHERE with AND operator in SQL - Step-by-Step Execution

Concept Flow - WHERE with AND operator

Start Query

↓

Scan Table Rows

↓

Evaluate Condition 1

↓

Evaluate Condition 2

↓

Condition 1 AND Condition 2?

Yes No↓

Include Row

↓

Return Filtered Rows

↓

End

The query scans each row, checks both conditions joined by AND, includes the row only if both are true, then returns all such rows.

Execution Sample

SQL

SELECT * FROM Employees
WHERE Department = 'Sales' AND Salary > 50000;

This query selects employees who work in Sales and earn more than 50000.

Execution Table

Step	Row Data	Condition 1: Department='Sales'	Condition 2: Salary>50000	AND Result	Action
1	{ID:1, Dept:'Sales', Salary:65000}	True	True	True	Include Row
2	{ID:2, Dept:'Sales', Salary:45000}	True	False	False	Skip Row
3	{ID:3, Dept:'HR', Salary:70000}	False	True	False	Skip Row
4	{ID:4, Dept:'Sales', Salary:52000}	True	True	True	Include Row
5	{ID:5, Dept:'IT', Salary:48000}	False	False	False	Skip Row
6	No more rows	-	-	-	End of scan

💡 All rows scanned; only rows where both conditions are true are included.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	Final
Current Row	None	{ID:1, Dept:'Sales', Salary:65000}	{ID:2, Dept:'Sales', Salary:45000}	{ID:3, Dept:'HR', Salary:70000}	{ID:4, Dept:'Sales', Salary:52000}	{ID:5, Dept:'IT', Salary:48000}	None
Condition 1 Result	N/A	True	True	False	True	False	N/A
Condition 2 Result	N/A	True	False	True	True	False	N/A
AND Result	N/A	True	False	False	True	False	N/A
Rows Included	[]	[{ID:1,...}]	[{ID:1,...}]	[{ID:1,...}]	[{ID:1,...},{ID:4,...}]	[{ID:1,...},{ID:4,...}]	[{ID:1,...},{ID:4,...}]

Key Moments - 2 Insights

Why is a row skipped even if one condition is true?

Because the AND operator requires both conditions to be true. For example, in step 2, Department='Sales' is true but Salary>50000 is false, so the row is skipped.

What happens if both conditions are false?

The row is skipped since AND needs both true. See step 5 where both conditions are false, so the row is not included.

Visual Quiz - 3 Questions

Look at the execution table, what is the AND result at step 3?

A Unknown

B True

C False

D Error

💡 Hint

Check the 'AND Result' column at step 3 in the execution_table.

At which step does the condition Department='Sales' become false?

A Step 3

B Step 2

C Step 4

D Step 5

💡 Hint

Look at the 'Condition 1' column in the execution_table for false values.

If Salary condition changed to Salary > 60000, which step's AND result would change from True to False?

A Step 1

B Step 4

C Step 5

D Step 2

💡 Hint

Compare Salary values at steps 1 and 4 with new condition >60000.

Concept Snapshot

WHERE clause filters rows based on conditions.
AND operator requires all conditions to be true.
Each row is checked: if all true, row included.
If any condition false, row skipped.
Useful to combine multiple filters in queries.

Full Transcript

This visual execution shows how the SQL WHERE clause with AND operator works. The query scans each row of the Employees table. For each row, it checks if the Department is 'Sales' and if the Salary is greater than 50000. Only rows where both conditions are true are included in the result. The execution table traces each row's data, condition results, and final decision to include or skip. The variable tracker shows how conditions and included rows change step by step. Key moments clarify why rows are skipped if any condition fails. The quiz tests understanding of condition evaluation and AND logic. This helps beginners see exactly how AND filters data in SQL queries.

Practice

1. What does the WHERE clause with the AND operator do in an SQL query?

easy

A. Filters rows that meet any one of the conditions

B. Joins two tables together

C. Filters rows that meet all specified conditions

D. Sorts the result set in ascending order

Solution

1. Step 1: Understand the WHERE clause

   The WHERE clause filters rows based on conditions given in the query.

2. Step 2: Understand the AND operator

   The AND operator requires all conditions to be true for a row to be included.

3. Final Answer:

   Filters rows that meet all specified conditions -> Option C

4. Quick Check:

   WHERE + AND = all conditions true [OK]

Hint: AND means all conditions must be true together [OK]

Common Mistakes:

• Thinking AND means any condition can be true
• Confusing WHERE with JOIN
• Assuming AND sorts data

2. Which of the following is the correct syntax to select rows where age is greater than 18 AND city is 'New York'?

easy

A. SELECT * FROM users WHERE age > 18 AND city = 'New York';

B. SELECT * FROM users WHERE age > 18, city = 'New York';

C. SELECT * FROM users WHERE age > 18 OR city = 'New York';

D. SELECT * FROM users WHERE age > 18 AND city == 'New York';

Solution

1. Step 1: Check the operator between conditions

   The question asks for AND, so the operator must be AND, not OR or comma.

2. Step 2: Check the comparison operators

   Use single equals (=) for comparison in SQL, not double equals (==).

3. Final Answer:

   SELECT * FROM users WHERE age > 18 AND city = 'New York'; -> Option A

4. Quick Check:

   AND + = correct syntax [OK]

Hint: Use AND and single = for conditions in WHERE [OK]

Common Mistakes:

• Using OR instead of AND
• Using comma instead of AND
• Using == instead of = for comparison

3. Given the table employees with columns name, department, and salary, what will this query return?

SELECT name FROM employees WHERE department = 'Sales' AND salary > 50000;

medium

A. Names of employees in Sales with salary over 50000

B. Names of employees in Sales or salary over 50000

C. All employees with salary over 50000

D. Syntax error due to missing OR

Solution

1. Step 1: Analyze the WHERE clause conditions

   The query filters rows where department equals 'Sales' AND salary is greater than 50000.

2. Step 2: Understand the AND effect

   Only employees meeting both conditions will be selected, so names returned are from Sales with salary above 50000.

3. Final Answer:

   Names of employees in Sales with salary over 50000 -> Option A

4. Quick Check:

   AND filters both conditions true [OK]

Hint: AND means both conditions must be true to select rows [OK]

Common Mistakes:

• Thinking OR is used instead of AND
• Assuming it returns all employees with salary > 50000
• Believing syntax error occurs without OR

4. Identify the error in this SQL query:

SELECT * FROM products WHERE price > 100 AND category 'Electronics';

medium

A. AND should be OR

B. Missing comparison operator (=) before 'Electronics'

C. price > 100 is invalid syntax

D. Missing FROM keyword

Solution

1. Step 1: Check the WHERE clause syntax

   The condition after AND is missing a comparison operator before 'Electronics'. It should be category = 'Electronics'.

2. Step 2: Verify other parts

   price > 100 is correct, AND is valid, and FROM is present.

3. Final Answer:

   Missing comparison operator (=) before 'Electronics' -> Option B

4. Quick Check:

   Comparison operator needed before value [OK]

Hint: Each condition needs a comparison operator like = [OK]

Common Mistakes:

• Omitting = in conditions
• Replacing AND with OR incorrectly
• Ignoring syntax errors in WHERE clause

5. You have a table orders with columns order_id, customer_id, status, and total. Write a query to find all orders with status 'shipped' AND total greater than 100, but exclude orders where customer_id is NULL. Which query is correct?

hard

A. SELECT * FROM orders WHERE status = 'shipped' AND total > 100 AND customer_id != NULL;

B. SELECT * FROM orders WHERE status = 'shipped' OR total > 100 AND customer_id IS NOT NULL;

C. SELECT * FROM orders WHERE status = 'shipped' AND total > 100 OR customer_id IS NOT NULL;

D. SELECT * FROM orders WHERE status = 'shipped' AND total > 100 AND customer_id IS NOT NULL;

Solution

1. Step 1: Combine conditions with AND

   We want orders where status is 'shipped' AND total is greater than 100 AND customer_id is not NULL.

2. Step 2: Use correct syntax for NULL check

   In SQL, to check for NULL, use IS NOT NULL, not != NULL.

3. Final Answer:

   SELECT * FROM orders WHERE status = 'shipped' AND total > 100 AND customer_id IS NOT NULL; -> Option D

4. Quick Check:

   Use AND and IS NOT NULL for correct filtering [OK]

Hint: Use IS NOT NULL to exclude NULLs with AND [OK]

Common Mistakes:

• Using OR instead of AND
• Using != NULL instead of IS NOT NULL
• Mixing AND and OR without parentheses