AND, OR, NOT logical operators in MySQL - Step-by-Step Execution

Concept Flow - AND, OR, NOT logical operators

Start with condition1

↓

Evaluate condition2

↓

Apply AND operator

↓

Apply OR operator

↓

Apply NOT operator

↓

Result: TRUE or FALSE

↓

Use result to filter rows

Logical operators combine or modify conditions to filter database rows based on TRUE or FALSE results.

Execution Sample

MySQL

SELECT * FROM employees
WHERE (department = 'Sales' AND salary > 50000)
OR NOT (active = 1);

This query selects employees who are in Sales with salary over 50000, or who are not active.

Execution Table

Step	Condition Evaluated	Value 1	Value 2	Operator	Result
1	department = 'Sales'	Sales	-	-	TRUE
2	salary > 50000	60000	-	-	TRUE
3	TRUE AND TRUE	TRUE	TRUE	AND	TRUE
4	active = 1	0	-	-	FALSE
5	NOT FALSE	FALSE	-	NOT	TRUE
6	TRUE OR TRUE	TRUE	TRUE	OR	TRUE
7	Row included in result	-	-	-	YES

💡 All conditions evaluated; final result TRUE means row is included.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	Final
department = 'Sales'	unknown	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
salary > 50000	unknown	unknown	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
active = 1	unknown	unknown	unknown	unknown	FALSE	FALSE	FALSE	FALSE
AND result	unknown	unknown	unknown	TRUE	TRUE	TRUE	TRUE	TRUE
NOT result	unknown	unknown	unknown	unknown	unknown	TRUE	TRUE	TRUE
OR result	unknown	unknown	unknown	unknown	unknown	unknown	TRUE	TRUE

Key Moments - 3 Insights

Why does the NOT operator change FALSE to TRUE in step 5?

NOT reverses the condition result. Since active = 1 is FALSE (active is 0), NOT FALSE becomes TRUE as shown in step 5.

Why is the AND operator result TRUE in step 3?

AND returns TRUE only if both conditions are TRUE. department = 'Sales' and salary > 50000 are both TRUE, so AND result is TRUE in step 3.

How does the OR operator affect the final result in step 6?

OR returns TRUE if either side is TRUE. Here, AND result is TRUE and NOT result is TRUE, so OR result is TRUE, including the row.

Visual Quiz - 3 Questions

Look at the execution table, what is the result of 'salary > 50000' at step 2?

A TRUE

B FALSE

C UNKNOWN

D NULL

💡 Hint

Check the 'Value 1' column at step 2 showing salary 60000 > 50000.

At which step does the NOT operator change the condition result?

A Step 4

B Step 5

C Step 3

D Step 6

💡 Hint

Look for the step where 'NOT' is listed as the operator.

If 'active' was 1 instead of 0, what would be the OR result at step 6?

A UNKNOWN

B FALSE

C TRUE

D NULL

💡 Hint

If active=1, then active=1 condition is TRUE, NOT TRUE is FALSE, but AND result is TRUE, so OR TRUE OR FALSE is TRUE.

Concept Snapshot

AND, OR, NOT combine conditions in SQL WHERE clauses.
AND requires both conditions TRUE.
OR requires at least one TRUE.
NOT reverses TRUE/FALSE.
Used to filter rows based on multiple criteria.

Full Transcript

This visual execution shows how SQL logical operators AND, OR, and NOT work step-by-step. We start by evaluating simple conditions like department = 'Sales' and salary > 50000. Then AND combines these two conditions, returning TRUE only if both are TRUE. Next, we evaluate active = 1, which is FALSE in this example. The NOT operator reverses this FALSE to TRUE. Finally, OR combines the AND result and the NOT result. Since both are TRUE, the OR result is TRUE, so the row is included in the query result. The variable tracker shows how each condition's value changes at each step. Key moments clarify common confusions about how NOT reverses values and how AND and OR combine conditions. The quiz tests understanding of these steps by asking about specific condition results and operator effects.

Practice

1. Which logical operator in MySQL requires all conditions to be true for the whole expression to be true?

easy

A. XOR

B. OR

C. NOT

D. AND

Solution

1. Step 1: Understand AND operator behavior

   The AND operator returns true only if every condition joined by it is true.

2. Step 2: Compare with other operators

   OR requires at least one true condition, NOT reverses truth, XOR is exclusive or.

3. Final Answer:

   AND -> Option D

4. Quick Check:

   AND means all true = A [OK]

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

Common Mistakes:

• Confusing AND with OR
• Thinking NOT requires all true
• Mixing XOR with AND

2. Which of the following is the correct syntax to select rows where age is greater than 18 and status is 'active'?

easy

A. SELECT * FROM users WHERE age > 18 AND status = 'active';

B. SELECT * FROM users WHERE NOT age > 18 AND status = 'active';

C. SELECT * FROM users WHERE age > 18 OR status = 'active';

D. SELECT * FROM users WHERE age > 18 NOT status = 'active';

Solution

1. Step 1: Identify correct logical operator for both conditions

   We want rows where both age > 18 and status = 'active' are true, so use AND.

2. Step 2: Check syntax correctness

   SELECT * FROM users WHERE age > 18 AND status = 'active'; uses AND correctly and has valid SQL syntax.

3. Final Answer:

   SELECT * FROM users WHERE age > 18 AND status = 'active'; -> Option A

4. Quick Check:

   AND joins conditions correctly = C [OK]

Hint: Use AND to combine multiple true conditions [OK]

Common Mistakes:

• Using OR instead of AND
• Incorrect NOT placement
• Syntax errors like missing AND

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

SELECT * FROM employees WHERE department = 'Sales' OR salary > 50000 AND NOT department = 'HR';

medium

A. Rows where department is 'Sales' or salary > 50000 or department is not 'HR'

B. Rows where department is 'Sales' and salary > 50000 and department is not 'HR'

C. Rows where department is 'Sales' or salary > 50000 and department is not 'HR'

D. Rows where salary > 50000 only

Solution

1. Step 1: Understand operator precedence

   AND has higher precedence than OR, so condition is: department = 'Sales' OR (salary > 50000 AND NOT department = 'HR').

2. Step 2: Interpret the conditions

   Rows match if department is 'Sales', or if salary > 50000 and department is not 'HR'.

3. Final Answer:

   Rows where department is 'Sales' or salary > 50000 and department is not 'HR' -> Option C

4. Quick Check:

   AND before OR, NOT reverses condition = B [OK]

Hint: AND binds tighter than OR; use parentheses to clarify [OK]

Common Mistakes:

• Ignoring operator precedence
• Misreading NOT placement
• Assuming OR applies to all conditions

4. Identify the error in this query:

SELECT * FROM orders WHERE NOT order_date > '2023-01-01' AND status = 'shipped' OR;

medium

A. Incorrect use of NOT operator

B. Missing condition after OR

C. AND should be OR

D. order_date comparison syntax error

Solution

1. Step 1: Check query syntax

   The query ends with OR but no condition follows it, causing syntax error.

2. Step 2: Verify other parts

   NOT and AND usage is correct; order_date comparison is valid.

3. Final Answer:

   Missing condition after OR -> Option B

4. Quick Check:

   OR must have two conditions = A [OK]

Hint: OR must always connect two conditions [OK]

Common Mistakes:

• Leaving OR without a following condition
• Misplacing NOT operator
• Confusing AND and OR usage

5. You want to select all customers who are NOT in the 'VIP' group AND have made a purchase OR have a balance greater than 1000. Which query correctly expresses this logic?

hard

A. SELECT * FROM customers WHERE NOT group = 'VIP' AND (purchases > 0 OR balance > 1000);

B. SELECT * FROM customers WHERE NOT (group = 'VIP' AND purchases > 0) OR balance > 1000;

C. SELECT * FROM customers WHERE NOT group = 'VIP' AND purchases > 0 OR balance > 1000;

D. SELECT * FROM customers WHERE NOT group = 'VIP' OR purchases > 0 AND balance > 1000;

Solution

1. Step 1: Translate the logic into conditions

   We want customers NOT in 'VIP' group AND (have purchases OR balance > 1000).

2. Step 2: Check operator precedence and parentheses

   AND has higher precedence than OR, so parentheses around (purchases > 0 OR balance > 1000) ensure correct grouping.

3. Step 3: Evaluate options

   SELECT * FROM customers WHERE NOT group = 'VIP' AND (purchases > 0 OR balance > 1000); correctly uses NOT on group, AND with grouped OR conditions. Others misplace parentheses or NOT.

4. Final Answer:

   SELECT * FROM customers WHERE NOT group = 'VIP' AND (purchases > 0 OR balance > 1000); -> Option A

5. Quick Check:

   NOT group, AND grouped OR = D [OK]

Hint: Use parentheses to group OR conditions with AND [OK]

Common Mistakes:

• Missing parentheses causing wrong logic
• Applying NOT to wrong part
• Confusing AND and OR precedence