🔍 MySQL WHERE Clause: Using AND, OR, NOT Operators with Real Examples

Filtering data in MySQL isn’t just about comparing values — often, you need to combine multiple conditions. That’s where logical operators like AND, OR, and NOT come into play.

In this tutorial, we’ll walk through how these operators work with WHERE clauses using real-world examples based on an employee and department database schema.

📌 What Are Logical Operators in SQL?

Logical operators are used to combine two or more conditions in a WHERE clause. MySQL evaluates them and returns rows that meet the combined logic.

🔗 Table Overview

We will use the following tables:

✅ departments

CREATE TABLE departments (
  department_id INTEGER PRIMARY KEY,
  department_name VARCHAR(30),
  location_id INTEGER
);

✅ employees

CREATE TABLE employees (
  employee_id INTEGER,
  first_name VARCHAR(20),
  last_name VARCHAR(25),
  email VARCHAR(25),
  phone_number VARCHAR(20),
  hire_date DATE,
  job_id VARCHAR(10),
  salary INTEGER,
  commission_pct INTEGER,
  manager_id INTEGER,
  department_id INTEGER,
  CONSTRAINT pk_emp PRIMARY KEY (employee_id),
  CONSTRAINT fk_deptno FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

🔹 1. AND Operator in MySQL

The AND operator is used when all conditions must be true.

🧠 Syntax:

SELECT * FROM table_name
WHERE condition1 AND condition2;

✅ Example 1: Find employees with a salary over ₹50,000 and in department 1.

SELECT * FROM employees
WHERE salary > 50000 AND department_id = 1;

🟢 Explanation: Returns employees in department 1 who earn more than ₹50,000.

🔸 2. OR Operator in MySQL

The OR operator returns rows if any one of the conditions is true.

🧠 Syntax:

SELECT * FROM table_name
WHERE condition1 OR condition2;

✅ Example 2: Get employees from department 2 or earning more than ₹80,000.

SELECT * FROM employees
WHERE department_id = 2 OR salary > 80000;

🟢 Explanation: It will return:

• All employees in department 2,
• And all other employees who earn more than ₹80,000 (from any department).

🔻 3. NOT Operator in MySQL

The NOT operator negates a condition, i.e., returns rows where the condition is false.

🧠 Syntax:

SELECT * FROM table_name
WHERE NOT condition;

✅ Example 3: Show employees not in department 3.

SELECT * FROM employees
WHERE NOT department_id = 3;

🟢 Explanation: Excludes employees belonging to department 3.

⚙ Combining AND, OR, and NOT

You can combine these logical operators using parentheses to control evaluation order.

✅ Complex Example:

SELECT * FROM employees
WHERE department_id = 1 AND (salary > 60000 OR NOT job_id = 'HR_REP');

🟢 Explanation:

• Employee must be in department 1,
• And either earn more than ₹60,000, or not have the job role 'HR_REP'.

🚀 More Practical Queries

Here are additional queries to practice:

🔹 Employees earning between ₹40,000 and ₹60,000 in department 2

SELECT * FROM employees
WHERE department_id = 2 AND salary BETWEEN 40000 AND 60000;

🔹 Employees whose job is 'IT_PROG' or 'HR_REP' but not in department 1

SELECT * FROM employees
WHERE (job_id = 'IT_PROG' OR job_id = 'HR_REP') AND NOT department_id = 1;

🔹 Employees not managed by manager with ID 1001

SELECT * FROM employees
WHERE NOT manager_id = 1001;

📝 Summary Table

🧠 Pro Tips

• Always use parentheses when mixing AND and OR to avoid logic errors.
• Use NOT carefully — especially with IN and LIKE.
• These logical operators work with subqueries and JOINs too.

🏁 Final Thoughts

Mastering AND, OR, and NOT in MySQL lets you write more flexible and powerful WHERE clauses. These operators help you filter exactly the data you want — whether you’re building dashboards, reports, or data-driven web apps.

🔥 Stay tuned with Acesoftech Academy for more hands-on SQL tutorials and full-stack development tips!