Logical Operators - AND, OR, NOT

Combine conditions using AND, OR, and NOT operators.

14 min read
Beginner

Introduction to Logical Operators

You've learned how to filter data with WHERE and comparison operators. But what if you need to combine multiple conditions? What if you want products that are BOTH cheap AND in stock? Or employees in Engineering OR Sales?

That's where logical operators come in! They let you combine multiple conditions to create powerful, flexible queries.

Logical Operators
Operator
Purpose
Example
ANDBoth conditions must be TRUE`price < 100 AND stock > 0`
ORAt least one condition must be TRUE`category = 'A' OR category = 'B'`
NOTReverses a condition`NOT category = 'Books'`

Let's work with our employees table:

employees
id
name
department
salary
years_exp
status
1AliceEngineering850005active
2BobMarketing650003active
3CharlieEngineering950008active
4DianaSales700004inactive
5EveMarketing720006active
6FrankSales680002active
7GraceEngineering780004active

AND Operator - Both Conditions Must Be True

The AND operator combines two or more conditions. All conditions must be TRUE for a row to be included.

Returns TRUE only if both conditions are TRUE. If any condition is FALSE, the entire expression is FALSE.

Syntax:

WHERE condition1 AND condition2
sql
-- Find Engineering employees with high salaries
SELECT name, department, salary
FROM employees
WHERE department = 'Engineering'
  AND salary > 80000;

Only rows where BOTH conditions are true are returned. Grace (Engineering, 78000) is excluded because her salary is not > 80000.

Multiple AND conditions:

sql
-- Active employees in Sales with experience > 3 years
SELECT name, department, years_exp, status
FROM employees
WHERE department = 'Sales'
  AND years_exp > 3
  AND status = 'active';

No rows match ALL three conditions. Diana has experience > 3 but is inactive. Frank is active but has only 2 years experience.

How AND works - Truth Table:

AND Truth Table
Condition 1
Condition 2
Result
TRUETRUETRUE ✓
TRUEFALSEFALSE
FALSETRUEFALSE
FALSEFALSEFALSE

Both conditions must be TRUE for the result to be TRUE.

OR Operator - At Least One Must Be True

The OR operator returns TRUE if at least one condition is TRUE. It's more permissive than AND.

Returns TRUE if any condition is TRUE. Only returns FALSE if all conditions are FALSE.

Syntax:

WHERE condition1 OR condition2
sql
-- Find employees in Engineering OR Sales
SELECT name, department, salary
FROM employees
WHERE department = 'Engineering'
   OR department = 'Sales';

Any row matching EITHER condition is included. Marketing employees are excluded because they match neither condition.

Combining multiple OR conditions:

sql
-- High earners OR very experienced employees
SELECT name, salary, years_exp
FROM employees
WHERE salary > 80000
   OR years_exp >= 6;
  • Alice qualifies because salary > 80000 (even though years_exp < 6)
  • Charlie qualifies for both reasons
  • Eve qualifies because years_exp >= 6 (even though salary <= 80000)

How OR works - Truth Table:

OR Truth Table
Condition 1
Condition 2
Result
TRUETRUETRUE ✓
TRUEFALSETRUE ✓
FALSETRUETRUE ✓
FALSEFALSEFALSE

As long as ONE condition is TRUE, the result is TRUE.

NOT Operator - Reversing Conditions

The NOT operator reverses a condition. It turns TRUE into FALSE and FALSE into TRUE.

Inverts the result of a condition. If the condition is TRUE, NOT makes it FALSE, and vice versa.

sql
-- Find employees NOT in Engineering
SELECT name, department
FROM employees
WHERE NOT department = 'Engineering';

This is equivalent to WHERE department != 'Engineering', but NOT can be used with more complex conditions.

NOT with other operators:

sql
-- Find employees NOT earning between 70000 and 80000
SELECT name, salary
FROM employees
WHERE NOT (salary BETWEEN 70000 AND 80000);

NOT with IN:

sql
-- Employees NOT in these departments
SELECT name, department
FROM employees
WHERE NOT department IN ('Marketing', 'Sales');

This is the same as WHERE department NOT IN ('Marketing', 'Sales').

Style tip: While NOT department = 'X' works, most developers prefer department != 'X' for simple comparisons. Use NOT for complex conditions where it improves readability.

Combining AND and OR

You can combine AND and OR in the same query, but operator precedence matters!

Precedence Rule: AND is evaluated BEFORE OR (like multiplication before addition in math).

sql
-- This might not do what you expect!
SELECT name, department, salary
FROM employees
WHERE department = 'Engineering'
   OR department = 'Sales'
  AND salary > 75000;

Why all Engineering employees? Because AND is evaluated first:

  1. First: department = 'Sales' AND salary > 75000 (no Sales employees earn > 75000)
  2. Then: department = 'Engineering' OR (result from step 1)

This matches all Engineering employees, not what we might have intended!

Use parentheses to control order:

sql
-- Now it does what we want
SELECT name, department, salary
FROM employees
WHERE (department = 'Engineering' OR department = 'Sales')
  AND salary > 75000;

Parentheses force the OR to evaluate first, then AND is applied. Much clearer!

Always use parentheses when combining AND and OR! Even if you understand precedence, parentheses make your intent crystal clear to other developers and prevent bugs.

Complex Filtering Examples

Let's see real-world examples combining everything we've learned:

Example 1: Employee search filter

sql
-- Senior employees (8+ years) OR high earners in key departments
SELECT name, department, salary, years_exp
FROM employees
WHERE years_exp >= 8
   OR (salary > 80000 AND department IN ('Engineering', 'Sales'));

Example 2: Active employees meeting criteria

sql
-- Active employees who are EITHER experienced OR well-paid
SELECT name, status, years_exp, salary
FROM employees
WHERE status = 'active'
  AND (years_exp >= 5 OR salary >= 75000);

Example 3: Exclusion filter

sql
-- Not junior employees in non-technical roles
SELECT name, department, years_exp
FROM employees
WHERE NOT (years_exp < 4 AND department NOT IN ('Engineering'));

Logical Operators with NULL

NULL values can cause unexpected behavior with logical operators:

employees_with_bonus
name
salary
bonus
Alice850005000
Bob65000NULL
Charlie950008000
sql
-- This might surprise you!
SELECT name, bonus
FROM employees_with_bonus
WHERE bonus > 3000 OR bonus <= 3000;

Bob is excluded! Why? NULL is neither > 3000 nor <= 3000. NULL comparisons always return NULL (not TRUE or FALSE).

Solution: Explicitly handle NULLs:

sql
SELECT name, bonus
FROM employees_with_bonus
WHERE bonus > 3000 OR bonus <= 3000 OR bonus IS NULL;

Important: When using OR with comparisons, NULL values might be excluded unexpectedly. Always consider whether you need to add OR column IS NULL.

Practical Decision Guide

When to Use Each Operator
Use Case
Operator
Example Query
Must meet ALL criteriaAND`WHERE active = 1 AND verified = 1`
Must meet ANY criteriaOR`WHERE role = 'admin' OR role = 'owner'`
Exclude specific valuesNOT / !=`WHERE NOT status = 'deleted'`
Range with both endsAND`WHERE price >= 10 AND price <= 100`
Multiple categoriesOR / IN`WHERE category IN ('A', 'B', 'C')`
Complex combinationsAND + OR`WHERE (a OR b) AND c`

Common Mistakes to Avoid

Don't make these mistakes:

  1. Forgetting parentheses with AND/OR: WHERE a OR b AND c is ambiguous!

    • Use: WHERE (a OR b) AND c or WHERE a OR (b AND c)

  2. Using AND when you mean OR: WHERE dept = 'A' AND dept = 'B' is impossible!

    • Use: WHERE dept IN ('A', 'B') or WHERE dept = 'A' OR dept = 'B'

  3. Forgetting about NULLs: WHERE col > 5 OR col <= 5 might miss NULL values

    • Add: OR col IS NULL if needed

  4. Redundant conditions: WHERE salary > 50000 AND salary > 60000

    • Simplify to: WHERE salary > 60000

  5. Using NOT unnecessarily: WHERE NOT status = 'active'

    • Clearer: WHERE status != 'active'

  6. Confusing operator precedence: AND always evaluates before OR

    • Use parentheses to be explicit!

Key Takeaways

What you learned:

✅ AND requires ALL conditions to be TRUE ✅ OR requires AT LEAST ONE condition to be TRUE ✅ NOT reverses a condition (TRUE becomes FALSE) ✅ AND is evaluated before OR (use parentheses!) ✅ Always use parentheses when combining AND and OR ✅ NULL values can cause unexpected results with logical operators ✅ Logical operators let you create powerful, flexible queries

Coming up next: We'll learn about ORDER BY and LIMIT to sort and paginate your query results!

Practice Exercise: Try these queries:

  1. Find active employees in Engineering with 5+ years experience
  2. Find employees in Marketing OR Sales with salaries over 70000
  3. Find employees NOT in Engineering AND NOT inactive
  4. Combine: (Engineering OR Sales) AND (salary > 70000 OR years > 5)
🗄️

SQL Fundamentals

Master SQL from basics to advanced topics. Learn to query, manipulate, and analyze data in relational databases.