8.3 Logical Operators (AND, OR, NOT)

Level: 8 | Version: v1.1 | Author: Meptrasoft

Overview

Logical operators are used to combine or reverse conditions in a WHERE clause.

Operator	Meaning	Example
`AND`	All conditions must be true	`salary > 30000 AND dept_id = 10`
`OR`	At least one condition must be true	`dept_id = 10 OR dept_id = 20`
`NOT`	Reverses a condition	`NOT dept_id = 10`

AND → Both
OR → Either
NOT → Reverse

SQL Logical Operators — AND, OR, NOT: Combine or reverse conditions to filter rows — Figure 1: AND, OR, and NOT combine or reverse conditions used to filter rows.

Practice Table

This topic reuses the employees table from 8.1 The WHERE Clause.

emp_id	emp_name	dept_id	salary
1	John Smith	10	45000
2	Emma Brown	20	60000
3	Samuel Reed	10	32000
4	Anderson	30	55000
5	Sarah Wilson	20	48000
6	Tom Watson	10	51000

AND

AND is used when all conditions must be true.

SELECT *
FROM employees
WHERE salary > 30000
  AND dept_id = 10;

This returns employees who:

belong to department 10
and have a salary greater than 30000

AND → All conditions must be true

OR

OR is used when at least one condition must be true.

SELECT *
FROM employees
WHERE dept_id = 10
   OR dept_id = 20;

This returns employees from department 10 or 20.

OR → At least one condition must be true

NOT

NOT reverses a condition.

SELECT *
FROM employees
WHERE NOT dept_id = 10;

This returns employees whose department is not 10.

A common equivalent form is:

SELECT *
FROM employees
WHERE dept_id <> 10;

NOT → Reverse the condition

Combining Operators

Logical operators can be combined.

SELECT *
FROM employees
WHERE dept_id = 10
  AND salary > 40000;

You can also use parentheses to make the intended logic clear:

SELECT *
FROM employees
WHERE (dept_id = 10 OR dept_id = 20)
  AND salary > 45000;

Here, the department must be 10 or 20, and the salary must be greater than 45000.

Use parentheses when combining multiple AND/OR conditions to make the logic explicit.

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
`AND` means either condition can be true	All conditions must be true
`OR` means all conditions must be true	At least one condition must be true
`NOT` removes rows permanently	It only reverses the condition in the query
Complex AND/OR conditions need no parentheses	Parentheses can make the intended logic clear and avoid mistakes
Logical operators change stored data	They normally filter the query result

Placement Quick Points

AND → ALL CONDITIONS
OR  → AT LEAST ONE
NOT → REVERSE

AND combines conditions that must all be true.
OR combines conditions where at least one must be true.
NOT reverses a condition.
Parentheses are useful when combining multiple AND and OR conditions.
Logical operators are commonly used with WHERE.

Interview Questions

What are logical operators in SQL?

Logical operators combine or reverse conditions used in SQL expressions.

What is the difference between AND and OR?

AND → All conditions must be true
OR  → At least one condition must be true

What does NOT do?

NOT reverses the result of a condition.

Why are parentheses useful in logical conditions?

They make the intended grouping of conditions clear, especially when AND and OR are used together.

Can AND, OR, and NOT be combined?

Yes.

SELECT *
FROM employees
WHERE (dept_id = 10 OR dept_id = 20)
  AND salary > 45000;

Practice & Hands-On Exercises

Using the employees table:

Find employees from department 10 and with salary greater than 40000:

SELECT * FROM employees WHERE dept_id = 10 AND salary > 40000;

Find employees from department 10 or 20:

SELECT * FROM employees WHERE dept_id = 10 OR dept_id = 20;

Find employees not belonging to department 10:

SELECT * FROM employees WHERE NOT dept_id = 10;

Find employees from department 10 or 20 with salary greater than 45000:

SELECT * FROM employees WHERE (dept_id = 10 OR dept_id = 20) AND salary > 45000;

Explain the difference between AND, OR, and NOT.
Explain why parentheses are useful in combined conditions.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

AND → BOTH
OR  → EITHER
NOT → REVERSE

Logical operators combine conditions in SQL: AND requires all conditions, OR requires at least one, and NOT reverses a condition.

End of Logical Operators (AND, OR, NOT)