Logical Operators in SQL: AND, OR, NOT

Table of Contents

1. Introduction
2. What Are Logical Operators in SQL?
3. The Role of Logical Operators in Filtering
4. Basic Syntax and Usage
5. Using AND to Combine Conditions
6. Using OR to Widen Conditions
7. Using NOT to Invert Conditions
8. Combining AND and OR
9. Operator Precedence: NOT, AND, OR
10. Controlling Logic with Parentheses
11. Real-World Use Case: Employee Filtering
12. Real-World Use Case: Product Search
13. Using Logical Operators in UPDATE
14. Using Logical Operators in DELETE
15. Logical Operators with NULL Checks
16. Performance Considerations
17. Common Mistakes to Avoid
18. Best Practices
19. Summary Table of Logical Use Cases
20. Summary and What’s Next

1. Introduction

SQL logical operators — AND, OR, and NOT — are essential for writing powerful and flexible filtering conditions. They allow you to combine multiple expressions in your WHERE clause to get precise control over query results.

2. What Are Logical Operators in SQL?

Logical operators are used to combine or negate conditions in a WHERE clause. You can filter data using multiple rules instead of just one.

3. The Role of Logical Operators in Filtering

Instead of writing separate queries for each condition, logical operators let you write complex filters within a single SQL statement:

sqlCopyEditSELECT * FROM employees 
WHERE department = 'IT' AND age > 30;

4. Basic Syntax and Usage

sqlCopyEditSELECT column1, column2
FROM table_name
WHERE condition1 [AND|OR|NOT] condition2;

Example:

sqlCopyEditSELECT * FROM users
WHERE city = 'Mumbai' AND is_active = TRUE;

5. Using AND to Combine Conditions

The AND operator returns true only if both conditions are true:

sqlCopyEditSELECT * FROM orders 
WHERE status = 'shipped' AND payment_method = 'UPI';

Example Output Logic:

6. Using OR to Widen Conditions

The OR operator returns true if either condition is true:

sqlCopyEditSELECT * FROM employees 
WHERE department = 'HR' OR department = 'Admin';

You can match rows from either department.

7. Using NOT to Invert Conditions

The NOT operator negates the result of a condition:

sqlCopyEditSELECT * FROM users WHERE NOT city = 'Delhi';

This returns all users not from Delhi.

More examples:

sqlCopyEditSELECT * FROM orders WHERE NOT status = 'cancelled';
SELECT * FROM customers WHERE NOT age BETWEEN 18 AND 25;

8. Combining AND and OR

You can use both AND and OR together for more advanced logic:

sqlCopyEditSELECT * FROM employees 
WHERE (department = 'HR' OR department = 'Finance') 
AND age > 30;

This selects employees from HR or Finance who are also older than 30.

9. Operator Precedence: NOT, AND, OR

SQL evaluates operators in the following order:

1. NOT
2. AND
3. OR

So this query:

sqlCopyEditSELECT * FROM users 
WHERE NOT age < 18 AND city = 'Mumbai' OR is_active = TRUE;

Is interpreted as:

sqlCopyEdit((NOT age < 18) AND (city = 'Mumbai')) OR (is_active = TRUE)

Use parentheses to avoid ambiguity.

10. Controlling Logic with Parentheses

Parentheses clarify and control precedence:

sqlCopyEditSELECT * FROM orders 
WHERE status = 'shipped' AND (payment_method = 'UPI' OR payment_method = 'Wallet');

Without parentheses, SQL may evaluate logic differently than intended.

11. Real-World Use Case: Employee Filtering

sqlCopyEditSELECT * FROM employees
WHERE department = 'IT' 
  AND (experience > 5 OR salary > 60000);

This returns IT employees who either have more than 5 years of experience or earn more than 60K.

12. Real-World Use Case: Product Search

sqlCopyEditSELECT * FROM products 
WHERE (category = 'Books' OR category = 'Stationery') 
  AND price < 500;

Use logical operators to create advanced filters on e-commerce search or dashboards.

13. Using Logical Operators in UPDATE

sqlCopyEditUPDATE users
SET status = 'inactive'
WHERE last_login < '2024-01-01' AND is_active = TRUE;

You can update based on multiple logical checks.

14. Using Logical Operators in DELETE

sqlCopyEditDELETE FROM logs 
WHERE severity = 'low' OR created_at < '2023-01-01';

This removes either old or low-priority logs.

15. Logical Operators with NULL Checks

sqlCopyEditSELECT * FROM users
WHERE phone IS NULL OR email IS NULL;

You can combine logical operators with IS NULL, IS NOT NULL.

16. Performance Considerations

• Use indexed columns in conditions to speed up filtering.
• Avoid overly complex conditions in huge datasets without proper indexing.
• Group conditions for efficient evaluation.

17. Common Mistakes to Avoid

• Forgetting parentheses when combining AND and OR
• Incorrect assumptions about precedence
• Using = for NULL comparisons (instead of IS NULL)
• Neglecting quotes around string literals

18. Best Practices

• Always group complex expressions with parentheses
• Test each condition individually before combining
• Prioritize readability — future you will thank you
• Use logical operators to reduce the number of queries

19. Summary Table of Logical Use Cases

20. Summary and What’s Next

Logical operators are indispensable in SQL for writing meaningful filters. They make your WHERE clauses far more powerful by allowing multiple conditions and combinations.