SQL AND, OR, and NOT Operators
When working with SQL, you'll often need to filter data based on one or more conditions. The AND, OR, and NOT operators are logical operators that help you combine or modify conditions in a WHERE clause.
These operators allow you to create more powerful and flexible SQL queries, making it easier to retrieve exactly the records you need.
In this tutorial, you'll learn how the SQL AND, OR, and NOT operators work, when to use each one, and how to combine them effectively with practical examples.
What Are SQL Logical Operators?
Logical operators are keywords used to combine multiple conditions in SQL.
The three most commonly used logical operators are:
| Operator | Description |
|---|---|
| AND | Returns rows only if all conditions are true. |
| OR | Returns rows if at least one condition is true. |
| NOT | Reverses a condition by returning rows where the condition is false. |
These operators are most commonly used with the WHERE clause.
Sample Customers Table
The examples in this tutorial use the following table.
| CustomerID | FirstName | Country | Age | Salary |
|---|---|---|---|---|
| 1 | John | USA | 28 | 3500 |
| 2 | Alice | Canada | 34 | 4800 |
| 3 | David | Kenya | 30 | 4200 |
| 4 | Grace | Tanzania | 25 | 3100 |
| 5 | James | Uganda | 40 | 6000 |
| 6 | Sophia | Kenya | 27 | 3900 |
SQL AND Operator
The AND operator returns rows only when every condition is true.
Syntax
SELECT column_name
FROM table_name
WHERE condition1
AND condition2;
Example 1
Retrieve customers from Kenya who are older than 25.
SELECT *
FROM Customers
WHERE Country = 'Kenya'
AND Age > 25;
Result
| FirstName | Country | Age |
|---|---|---|
| David | Kenya | 30 |
| Sophia | Kenya | 27 |
Example 2
Find customers earning more than 4,000 and older than 30.
SELECT *
FROM Customers
WHERE Salary > 4000
AND Age > 30;
Result
| FirstName | Salary |
|---|---|
| Alice | 4800 |
| James | 6000 |
SQL OR Operator
The OR operator returns rows when at least one condition is true.
Syntax
SELECT column_name
FROM table_name
WHERE condition1
OR condition2;
Example 1
Retrieve customers from Kenya or Tanzania.
SELECT *
FROM Customers
WHERE Country = 'Kenya'
OR Country = 'Tanzania';
Result
| FirstName | Country |
|---|---|
| David | Kenya |
| Grace | Tanzania |
| Sophia | Kenya |
Example 2
Find customers younger than 26 or earning more than 5,000.
SELECT *
FROM Customers
WHERE Age < 26
OR Salary > 5000;
SQL NOT Operator
The NOT operator reverses a condition.
Syntax
SELECT column_name
FROM table_name
WHERE NOT condition;
Example
Retrieve customers who are not from the USA.
SELECT *
FROM Customers
WHERE NOT Country = 'USA';
Combining AND and OR
You can combine multiple logical operators in one query.
Example
Retrieve customers from Kenya who are older than 25, or customers from Uganda.
SELECT *
FROM Customers
WHERE (Country = 'Kenya' AND Age > 25)
OR Country = 'Uganda';
Result
| FirstName | Country |
|---|---|
| David | Kenya |
| Sophia | Kenya |
| James | Uganda |
Using parentheses makes the query easier to read and ensures SQL evaluates the conditions in the intended order.
Using NOT with AND
Retrieve customers who are not from Kenya and are older than 30.
SELECT *
FROM Customers
WHERE NOT Country = 'Kenya'
AND Age > 30;
Using NOT with OR
Retrieve customers who are neither from Canada nor the USA.
SELECT *
FROM Customers
WHERE NOT (Country = 'Canada'
OR Country = 'USA');
Operator Precedence
SQL evaluates logical operators in this order:
NOTANDOR
For example:
SELECT *
FROM Customers
WHERE Country = 'Kenya'
OR Country = 'Uganda'
AND Age > 30;
This query evaluates the AND condition before the OR.
To avoid confusion, use parentheses:
SELECT *
FROM Customers
WHERE (Country = 'Kenya'
OR Country = 'Uganda')
AND Age > 30;
Practical Examples
Find customers between the ages of 25 and 35.
SELECT *
FROM Customers
WHERE Age >= 25
AND Age <= 35;
Find customers from Kenya or Tanzania with salaries above 3,500.
SELECT *
FROM Customers
WHERE (Country = 'Kenya'
OR Country = 'Tanzania')
AND Salary > 3500;
Exclude customers younger than 18.
SELECT *
FROM Customers
WHERE NOT Age < 18;
Common Mistakes
Forgetting Parentheses
Incorrect:
SELECT *
FROM Customers
WHERE Country = 'Kenya'
OR Country = 'Uganda'
AND Salary > 4000;
This may return unexpected results because AND is evaluated before OR.
Correct:
SELECT *
FROM Customers
WHERE (Country = 'Kenya'
OR Country = 'Uganda')
AND Salary > 4000;Using Too Many OR Conditions
Instead of:
SELECT *
FROM Customers
WHERE Country = 'Kenya'
OR Country = 'Uganda'
OR Country = 'Tanzania';
Use:
SELECT *
FROM Customers
WHERE Country IN ('Kenya', 'Uganda', 'Tanzania');
The IN operator is shorter, easier to read, and easier to maintain.
Best Practices
Use
ANDwhen every condition must be true.Use
ORwhen any condition can be true.Use
NOTto exclude matching records.Add parentheses when combining multiple logical operators.
Keep conditions simple and readable.
Test complex queries with a
SELECTstatement before using the same conditions inUPDATEorDELETEstatements.
Conclusion
The SQL AND, OR, and NOT operators are essential tools for filtering and retrieving data from relational databases. They allow you to combine multiple conditions, exclude specific records, and build flexible queries that return exactly the information you need.
By understanding how these logical operators work—and by using parentheses to control evaluation order—you can write more accurate, readable, and efficient SQL queries. As your SQL skills grow, these operators will become a key part of your everyday database work.