Logical Operators: AND, OR, NOT0%

Logical Operators: AND, OR, NOT

Beginner12 min read•Updated: 2026-09-12

Logical Operators: Combining Predicates with AND, OR, and NOT

Real-world queries rarely filter by a single attribute. You will often need to retrieve records meeting multiple simultaneous criteria (e.g., "Orders placed in Maharashtra AND total > ₹5,000 AND NOT refunded"). Combining multiple conditions is accomplished using the SQL Logical Operators: AND, OR, and NOT.

1. The `AND` Operator

The AND operator returns TRUE only if both conditions on either side evaluate to TRUE:

1-- Find active employees earning more than ₹60,000 in Engineering:
2SELECT employee_id, full_name, salary, department
3FROM employees
4WHERE department = 'Engineering' 
5  AND salary > 60000.00 
6  AND is_active = TRUE;

2. The `OR` Operator

The OR operator returns TRUE if at least one of the conditions evaluates to TRUE:

1-- Find customers located in Delhi OR Mumbai:
2SELECT customer_id, full_name, city
3FROM customers
4WHERE city = 'Delhi' OR city = 'Mumbai';

3. The `NOT` Operator

The NOT operator inverts the boolean truth value of a condition (turning TRUE into FALSE and FALSE into TRUE):

1-- Find all products that are NOT discontinued:
2SELECT product_id, product_name
3FROM products
4WHERE NOT is_discontinued;
5 
6-- Find customers who do NOT live in Bangalore:
7SELECT customer_id, full_name, city
8FROM customers
9WHERE NOT (city = 'Bangalore');

4. Operator Precedence: The Parentheses Trap!

In SQL, logical operators do not evaluate simply from left to right. They adhere to strict Operator Precedence:

 Operator Precedence (Highest to Lowest):
 1. Parentheses: ( ... )
 2. Comparison Operators: =, <>, <, >, <=, >=
 3. NOT
 4. AND  <-- Evaluated BEFORE OR!
 5. OR   <-- Evaluated LAST!

The Disaster Scenario:

Suppose your manager asks: "List all customers from Delhi or Mumbai who have a balance over ₹50,000."

1-- WRONG (Logical Bug due to AND precedence!):
2SELECT * FROM customers
3WHERE city = 'Delhi' OR city = 'Mumbai' AND account_balance > 50000;

Why this query fails:

Because AND has higher precedence than OR, MySQL interprets this as: "Give me anyone from Mumbai with balance > ₹50,000 ... OR ANYONE AT ALL FROM DELHI REGARDLESS OF THEIR BALANCE!"

The Correct Query (Always Use Parentheses!):

1SELECT * FROM customers
2WHERE (city = 'Delhi' OR city = 'Mumbai') 
3  AND account_balance > 50000;

5. Truth Table with SQL Three-Valued Logic

Condition A	Condition B	A AND B	A OR B	NOT A
TRUE	TRUE	TRUE	TRUE	FALSE
TRUE	FALSE	FALSE	TRUE	FALSE
TRUE	UNKNOWN	UNKNOWN	TRUE	FALSE
FALSE	UNKNOWN	FALSE	UNKNOWN	TRUE
UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN

6. Best Practices & Common Pitfalls

Always Use Explicit Parentheses: Never rely on default operator precedence when mixing AND and OR in the same query. Enclosing sub-conditions in parentheses clarifies your intent to other developers and prevents catastrophic filtering bugs.
Index Considerations with OR: Queries using OR across multiple unindexed columns often force MySQL to perform a full table scan because standard B-Trees cannot easily satisfy split OR conditions in a single index pass. Prefer using UNION or IN where applicable.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which logical operator has higher evaluation precedence in standard SQL?

Question 2

In the expression WHERE city = 'Pune' OR city = 'Goa' AND status = 'VIP', how does the database engine evaluate the logic?

Question 3

What is the result of TRUE OR UNKNOWN according to SQL three-valued logic?

Question 4

Which operator inverts the truth value of a condition?

Question 5

Why should parentheses always be used when combining AND and OR clauses?

Next Lesson

Range Filtering with BETWEEN

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

Related Lessons

Previous Lesson	Next Lesson
Comparison Operators in SQL	Range Filtering with BETWEEN

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

Range Filtering with BETWEEN

Logical Operators: AND, OR, NOT

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Logical Operators: Combining Predicates with AND, OR, and NOT

1. The `AND` Operator

The AND operator returns TRUE only if both conditions on either side evaluate to TRUE:

1-- Find active employees earning more than ₹60,000 in Engineering:
2SELECT employee_id, full_name, salary, department
3FROM employees
4WHERE department = 'Engineering' 
5  AND salary > 60000.00 
6  AND is_active = TRUE;

2. The `OR` Operator

The OR operator returns TRUE if at least one of the conditions evaluates to TRUE:

1-- Find customers located in Delhi OR Mumbai:
2SELECT customer_id, full_name, city
3FROM customers
4WHERE city = 'Delhi' OR city = 'Mumbai';

3. The `NOT` Operator

The NOT operator inverts the boolean truth value of a condition (turning TRUE into FALSE and FALSE into TRUE):

1-- Find all products that are NOT discontinued:
2SELECT product_id, product_name
3FROM products
4WHERE NOT is_discontinued;
5 
6-- Find customers who do NOT live in Bangalore:
7SELECT customer_id, full_name, city
8FROM customers
9WHERE NOT (city = 'Bangalore');

4. Operator Precedence: The Parentheses Trap!

In SQL, logical operators do not evaluate simply from left to right. They adhere to strict Operator Precedence:

 Operator Precedence (Highest to Lowest):
 1. Parentheses: ( ... )
 2. Comparison Operators: =, <>, <, >, <=, >=
 3. NOT
 4. AND  <-- Evaluated BEFORE OR!
 5. OR   <-- Evaluated LAST!

The Disaster Scenario:

Suppose your manager asks: "List all customers from Delhi or Mumbai who have a balance over ₹50,000."

1-- WRONG (Logical Bug due to AND precedence!):
2SELECT * FROM customers
3WHERE city = 'Delhi' OR city = 'Mumbai' AND account_balance > 50000;

Why this query fails:

Because AND has higher precedence than OR, MySQL interprets this as: "Give me anyone from Mumbai with balance > ₹50,000 ... OR ANYONE AT ALL FROM DELHI REGARDLESS OF THEIR BALANCE!"

The Correct Query (Always Use Parentheses!):

1SELECT * FROM customers
2WHERE (city = 'Delhi' OR city = 'Mumbai') 
3  AND account_balance > 50000;

5. Truth Table with SQL Three-Valued Logic

Condition A	Condition B	A AND B	A OR B	NOT A
TRUE	TRUE	TRUE	TRUE	FALSE
TRUE	FALSE	FALSE	TRUE	FALSE
TRUE	UNKNOWN	UNKNOWN	TRUE	FALSE
FALSE	UNKNOWN	FALSE	UNKNOWN	TRUE
UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN

6. Best Practices & Common Pitfalls

Always Use Explicit Parentheses: Never rely on default operator precedence when mixing AND and OR in the same query. Enclosing sub-conditions in parentheses clarifies your intent to other developers and prevents catastrophic filtering bugs.
Index Considerations with OR: Queries using OR across multiple unindexed columns often force MySQL to perform a full table scan because standard B-Trees cannot easily satisfy split OR conditions in a single index pass. Prefer using UNION or IN where applicable.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which logical operator has higher evaluation precedence in standard SQL?

Question 2

In the expression WHERE city = 'Pune' OR city = 'Goa' AND status = 'VIP', how does the database engine evaluate the logic?

Question 3

What is the result of TRUE OR UNKNOWN according to SQL three-valued logic?

Question 4

Which operator inverts the truth value of a condition?

Question 5

Why should parentheses always be used when combining AND and OR clauses?

Next Lesson

Range Filtering with BETWEEN

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

Related Lessons

Previous Lesson	Next Lesson
Comparison Operators in SQL	Range Filtering with BETWEEN

Practice Quiz

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

Previous Next

Logical Operators: Combining Predicates with AND, OR, and NOT

1. The AND Operator

2. The OR Operator

3. The NOT Operator

4. Operator Precedence: The Parentheses Trap!

The Disaster Scenario:

Why this query fails:

The Correct Query (Always Use Parentheses!):

5. Truth Table with SQL Three-Valued Logic

6. Best Practices & Common Pitfalls

Multiple Choice Questions

Range Filtering with BETWEEN

Related Lessons

Practice Quiz

Loading Page Content...

Logical Operators: Combining Predicates with AND, OR, and NOT

1. The AND Operator

2. The OR Operator

3. The NOT Operator

4. Operator Precedence: The Parentheses Trap!

The Disaster Scenario:

Why this query fails:

The Correct Query (Always Use Parentheses!):

5. Truth Table with SQL Three-Valued Logic

6. Best Practices & Common Pitfalls

Multiple Choice Questions

Range Filtering with BETWEEN

Related Lessons

Practice Quiz

1. The `AND` Operator

2. The `OR` Operator

3. The `NOT` Operator

1. The `AND` Operator

2. The `OR` Operator

3. The `NOT` Operator