AND, OR, NOT Operators

Learning Focus

Use this lesson to understand AND, OR, NOT Operators with practical syntax and examples.

Concept Overview

Definition:

The AND, OR, and NOT operators in MySQL are logical operators used to filter query results based on multiple conditions.

Why It's Important:

These operators enable precise data filtering by combining multiple conditions in WHERE clauses. This is essential when working with complex datasets, allowing developers and analysts to query exactly the data they need.

Where It Fits:

These operators are part of SQL's conditional logic system and are most often used within the WHERE, HAVING, and JOIN clauses to build meaningful queries in everyday database operations.

---

Basic Syntax & Rules

Basic Syntax:

SELECT column1, column2
FROM table_name
WHERE condition1 AND/OR/NOT condition2;

Explanation of Operators:

• AND: All conditions must be true
• OR: At least one condition must be true
• NOT: Reverses the logical value (e.g., NOT TRUE becomes FALSE)

Example Breakdown:

-- Using AND
SELECT * FROM employees
WHERE department = 'Sales' AND status = 'Active';

-- Using OR
SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

-- Using NOT
SELECT * FROM employees
WHERE NOT status = 'Inactive';

Limitations/Considerations:

• Use parentheses () to group conditions and control evaluation order.
• Be careful of NULL values-logical operators may behave unexpectedly.

---

Step-by-Step Examples

Sample Data: customers Table

id	name	city	active	purchases
1	Alice	New York	1	5
2	Bob	Los Angeles	0	3
3	Carol	Chicago	1	10
4	Dave	New York	1	2
5	Eve	Chicago	0	7

Example 1: AND

-- Active customers from Chicago
SELECT name, city
FROM customers
WHERE city = 'Chicago' AND active = 1;

Output:

name	city
Carol	Chicago

Example 2: OR

-- Customers from New York or with more than 8 purchases
SELECT name, purchases
FROM customers
WHERE city = 'New York' OR purchases > 8;

Output:

name	purchases
Alice	5
Carol	10
Dave	2

Example 3: NOT

-- Customers who are NOT active
SELECT name, active
FROM customers
WHERE NOT active = 1;

Output:

name	active
Bob	0
Eve	0

Example 4: Combined with Parentheses

-- Active customers in either New York OR Chicago
SELECT name, city
FROM customers
WHERE active = 1 AND (city = 'New York' OR city = 'Chicago');

Output:

name	city
Carol	Chicago
Dave	New York

---

Practical Use Cases

1. E-commerce Filtering

Retrieve products that are either in-stock or discounted:

SELECT * FROM products
WHERE stock > 0 OR discount > 0;

2. Employee Management

Find employees who are full-time and in the engineering or design team:

SELECT * FROM employees
WHERE status = 'Full-Time' AND (department = 'Engineering' OR department = 'Design');

3. Subscription Services

Select users who haven't cancelled and are from key cities:

SELECT * FROM users
WHERE NOT status = 'Cancelled' AND city IN ('NY', 'SF');

4. Marketing Analytics

Target customers with high purchases or long tenure:

SELECT * FROM customers
WHERE purchases > 50 OR signup_year < 2020;

5. Healthcare Records

Identify patients who are smokers but NOT diabetic:

SELECT * FROM patients
WHERE smoker = 1 AND NOT diabetic = 1;

---

Common Mistakes & Troubleshooting

Mistake 1: Incorrect Grouping Without Parentheses

-- Wrong: Filters incorrectly
WHERE city = 'NY' OR city = 'SF' AND active = 1;

Fix:

-- Right: Active users in either NY or SF
WHERE (city = 'NY' OR city = 'SF') AND active = 1;

Mistake 2: Misunderstanding NOT Logic

-- Wrong: Returns nothing if misunderstood
WHERE NOT city = 'NY' OR city = 'LA';

Fix:

Use parentheses to clarify intent:

WHERE NOT (city = 'NY' OR city = 'LA');

Mistake 3: Neglecting NULL Values

-- NULL values can bypass filters
WHERE NOT active;

Fix:

Use explicit checks:

WHERE active = 0;

Tip:

Use EXPLAIN or break complex conditions into subqueries for debugging.

---

Best Practices

• Always use parentheses to group complex conditions
• Be cautious with NULLs in boolean logic
• Use indexed columns in conditions for better performance
• Prefer explicit comparisons (=, <>) over boolean shorthand
• Use EXPLAIN to optimize complex queries

---

Hands-On Practice

Sample Data Setup

CREATE TABLE employees (
  id INT, name VARCHAR(50), department VARCHAR(50),
  active BOOLEAN, experience INT
);

INSERT INTO employees VALUES
(1, 'John', 'Engineering', 1, 5),
(2, 'Sara', 'Design', 0, 2),
(3, 'Tom', 'Engineering', 1, 8),
(4, 'Lucy', 'HR', 1, 3),
(5, 'Mark', 'Design', 1, 6);

Exercise 1: Easy

Q: List active employees.

-- Solution:
SELECT * FROM employees WHERE active = 1;

Exercise 2: Medium

Q: List employees from Design with more than 4 years experience.

-- Solution:
SELECT * FROM employees
WHERE department = 'Design' AND experience > 4;

Exercise 3: Hard

Q: Find employees who are active and either in Engineering or have more than 5 years experience.

-- Solution:
SELECT * FROM employees
WHERE active = 1 AND (department = 'Engineering' OR experience > 5);

---

Connection to Other Concepts

Related Concepts	How It Connects
WHERE clause	Primary context where logical operators are used
JOIN operations	Filters join results using combined conditions
IN, BETWEEN	Alternative or complementary to OR / AND
CASE statements	More advanced conditional logic
HAVING clause	Used with GROUP BY for conditionally grouped data

Prerequisites: Understanding of SELECT, WHERE, comparison operators (=, <, >).

Next Steps: Learn about CASE, IF, HAVING, and EXISTS for more advanced logic.

---

Visual Learning Diagram

flowchart TD
  A[SELECT Basics] --> B[WHERE Clause]
  B --> C((AND / OR / NOT))
  C --> D{Combining Conditions}
  D --> E[Advanced Filtering: CASE, HAVING]
  D --> F[JOIN with Conditions]
  C --> G[Performance Tips]
  F --> H[Subqueries with Conditions]
  G --> I[Query Optimization]

  %% Styling
  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  class A,B,C,D,E,F,G,H,I allNodes
  class C stroke-width:4px

---

Common Pitfalls

Pitfall	Consequence	Prevention
Executing queries without validating sample rows	Logic errors reach production data or reports	Start with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schema	Queries fail due to missing columns/indexes	Verify structure with DESCRIBE table_name; and adapt query design

Quick Reference

SHOW DATABASES;
USE your_database;
SHOW TABLES;
SELECT * FROM your_table LIMIT 10;

What's Next

• Next: Like Operator - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.