SQL AND, OR, NOT Operators

When filtering data using the WHERE clause, sometimes a single condition is not enough. SQL provides three logical operators — AND, OR, and NOT — to combine or reverse conditions and create more specific filters.

Think of them like everyday decision-making: "I want a phone that is cheap AND has a good camera." Or "I will go to the market if it does not rain OR if someone gives me an umbrella." These are the same logic patterns.

The Reference Table

All examples use the following Students table:

The AND Operator

The AND operator combines two conditions and returns only rows where both conditions are true. If even one condition is false, the row is excluded.

Syntax

SELECT column1, column2
FROM table_name
WHERE condition1 AND condition2;

Example: Students From Delhi Who Are 20 Years Old

SELECT * FROM Students
WHERE City = 'Delhi' AND Age = 20;

Result:

Only rows that satisfy both conditions — City is Delhi and Age is 20 — are returned.

AND Operator Truth Table

The OR Operator

The OR operator returns rows where at least one of the conditions is true. A row is excluded only if all conditions are false.

Syntax

SELECT column1, column2
FROM table_name
WHERE condition1 OR condition2;

Example: Students From Delhi OR Mumbai

SELECT * FROM Students
WHERE City = 'Delhi' OR City = 'Mumbai';

Result:

OR Operator Truth Table

The NOT Operator

The NOT operator reverses a condition. If a condition is true, NOT makes it false, and vice versa. It is used to exclude rows that match a specific condition.

Syntax

SELECT column1, column2
FROM table_name
WHERE NOT condition;

Example: Students Who Are NOT From Delhi

SELECT * FROM Students
WHERE NOT City = 'Delhi';

Result:

Combining AND, OR, and NOT Together

These operators can be combined in a single WHERE clause. When mixing them, parentheses should be used to make the logic clear and control the order of evaluation.

Example: Students From Delhi OR Chennai, But NOT Younger Than 20

SELECT * FROM Students
WHERE (City = 'Delhi' OR City = 'Chennai')
AND NOT Age < 20;

Result:

Arjun Nair from Chennai is excluded because his age is 19, which is less than 20.

Operator Precedence

When multiple operators are combined without parentheses, SQL evaluates them in this order:

1. NOT — evaluated first
2. AND — evaluated second
3. OR — evaluated last

Using parentheses is always recommended to avoid confusion and ensure the intended logic is applied.

Example Showing Why Parentheses Matter

-- Without parentheses (AND is evaluated before OR)
SELECT * FROM Students
WHERE City = 'Delhi' OR City = 'Mumbai' AND Age > 21;

-- This is interpreted as:
-- WHERE City = 'Delhi' OR (City = 'Mumbai' AND Age > 21)

-- With parentheses (OR is forced to evaluate first)
SELECT * FROM Students
WHERE (City = 'Delhi' OR City = 'Mumbai') AND Age > 21;

The two queries above produce different results. Always use parentheses when combining AND and OR to make the logic clear.

Key Points to Remember

• AND — both conditions must be true for the row to be included.
• OR — at least one condition must be true for the row to be included.
• NOT — reverses the result of a condition.
• Use parentheses when combining operators to control evaluation order and avoid unexpected results.
• AND has higher priority than OR when no parentheses are used.

Summary

AND, OR, and NOT are logical operators that extend the filtering power of the WHERE clause. They allow building complex conditions to find exactly the rows needed from a table. Using parentheses when combining these operators is a best practice that prevents logic errors and improves readability.