AND, OR, NOT logical operators in MySQL - Deep Dive

Overview - AND, OR, NOT logical operators

What is it?

AND, OR, and NOT are logical operators used in databases to combine or modify conditions in queries. They help decide which rows to select based on multiple rules. AND means all conditions must be true, OR means at least one condition must be true, and NOT reverses the truth of a condition. These operators let you filter data precisely.

Why it matters

Without these logical operators, you could only filter data by one simple condition at a time. This would make it hard to find exactly what you want in large databases. Using AND, OR, and NOT lets you ask complex questions like 'show me all customers from city A AND who bought product B, but NOT those who returned it.' This makes data searching powerful and efficient.

Where it fits

Before learning these operators, you should understand basic SQL SELECT queries and simple WHERE clauses. After mastering them, you can learn about more advanced filtering like BETWEEN, IN, and complex nested conditions. These operators are foundational for writing effective database queries.

Mental Model

Core Idea

Logical operators AND, OR, and NOT combine or invert conditions to filter database rows precisely.

Think of it like...

Think of AND, OR, and NOT like traffic lights controlling which cars (data rows) can pass through an intersection (query). AND is like needing green lights on all roads to go, OR is green on any road, and NOT is a red light blocking certain cars.

┌─────────────┐   ┌─────────────┐   ┌─────────────┐
│ Condition A │   │ Condition B │   │ Condition C │
└─────┬───────┘   └─────┬───────┘   └─────┬───────┘
      │                 │                 │
      │                 │                 │
      │                 │                 │
      ▼                 ▼                 ▼
   ┌───────────────┐  ┌───────────────┐  ┌───────────────┐
   │   AND (A & B) │  │    OR (A | B) │  │    NOT (¬A)   │
   └───────────────┘  └───────────────┘  └───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Basic WHERE Clause

Concept: Learn how to filter rows using a single condition in a WHERE clause.

In SQL, the WHERE clause filters rows based on a condition. For example, SELECT * FROM customers WHERE city = 'Paris'; returns only customers from Paris.

Result

Only rows where city equals 'Paris' are shown.

Knowing how to filter with one condition is the base for combining multiple conditions using logical operators.

2

Foundation

Introduction to AND Operator

Concept: AND requires all combined conditions to be true for a row to be selected.

Example: SELECT * FROM orders WHERE status = 'shipped' AND amount > 100; This returns orders that are both shipped and have an amount greater than 100.

Result

Rows meeting both conditions appear; others are excluded.

AND narrows down results by requiring multiple conditions to be true simultaneously.

3

Intermediate

Using OR Operator for Flexibility

🤔Before reading on: do you think OR requires all conditions to be true or just one? Commit to your answer.

Concept: OR selects rows where at least one condition is true.

Example: SELECT * FROM products WHERE category = 'Books' OR category = 'Games'; This returns products in either category.

Result

Rows matching any condition are included.

OR broadens the selection by allowing multiple alternative conditions.

4

Intermediate

Applying NOT Operator to Invert Conditions

🤔Before reading on: does NOT select rows that meet the condition or exclude them? Commit to your answer.

Concept: NOT reverses the truth of a condition, selecting rows that do NOT meet it.

Example: SELECT * FROM users WHERE NOT active; returns users who are not active.

Result

Rows where the condition is false are selected.

NOT helps exclude unwanted data by flipping condition logic.

5

Intermediate

Combining AND, OR, and NOT Together

🤔Before reading on: do AND and OR have the same priority, or does one evaluate before the other? Commit to your answer.

Concept: Logical operators can be combined with parentheses to control evaluation order.

Example: SELECT * FROM employees WHERE (department = 'Sales' OR department = 'Marketing') AND NOT retired; This selects employees in Sales or Marketing who are not retired.

Result

Only rows meeting the combined logic appear.

Understanding operator precedence and parentheses is key to building correct complex queries.

6

Advanced

Operator Precedence and Short-Circuit Evaluation

🤔Before reading on: does SQL evaluate AND before OR, or vice versa? Commit to your answer.

Concept: AND has higher precedence than OR, and SQL evaluates conditions left to right, stopping early if possible (short-circuit).

In WHERE clauses, AND conditions are evaluated before OR unless parentheses change order. For example, A OR B AND C is treated as A OR (B AND C). SQL stops checking conditions once the result is known.

Result

Queries behave predictably based on operator precedence and evaluation order.

Knowing precedence prevents logical errors and improves query efficiency.

7

Expert

NULL Values Impact on Logical Operators

🤔Before reading on: does NULL behave like false in logical operations? Commit to your answer.

Concept: In SQL, NULL represents unknown, and logical operators with NULL can result in UNKNOWN, affecting query results.

For example, TRUE AND NULL returns NULL (unknown), and WHERE clauses treat NULL as false. This means rows with NULL in conditions may be excluded unexpectedly unless handled explicitly.

Result

Queries may exclude rows with NULL unless IS NULL or COALESCE is used.

Understanding NULL's effect on logic avoids subtle bugs and ensures accurate filtering.

Under the Hood

SQL evaluates logical operators by checking each condition's truth value: TRUE, FALSE, or UNKNOWN (NULL). AND returns TRUE only if all conditions are TRUE; OR returns TRUE if any condition is TRUE; NOT flips TRUE to FALSE and vice versa, but NULL remains UNKNOWN. The database engine uses short-circuit evaluation to stop checking once the result is determined, improving performance.

Why designed this way?

These operators follow classical logic adapted for SQL's three-valued logic (TRUE, FALSE, UNKNOWN) to handle missing data (NULL). Short-circuiting was added to optimize query speed by avoiding unnecessary checks. This design balances logical correctness with practical performance.

┌───────────────┐
│ Condition A   │
│ TRUE/FALSE/NULL│
└──────┬────────┘
       │
┌──────▼───────┐
│ Condition B   │
│ TRUE/FALSE/NULL│
└──────┬────────┘
       │
┌──────▼─────────────┐
│ Logical Operator    │
│ (AND, OR, NOT)      │
└──────┬─────────────┘
       │
┌──────▼─────────────┐
│ Result: TRUE/FALSE/ │
│ UNKNOWN (NULL)      │
└────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does OR require all conditions to be true to select a row? Commit yes or no.

Common Belief: OR means all conditions must be true to select a row.

Reality: OR requires only one condition to be true for the row to be selected.

Why it matters: Misunderstanding OR leads to queries that return no results or miss valid rows.

Quick: Does NOT simply mean 'false' or 'no'? Commit yes or no.

Common Belief: NOT just means false or no, so NOT TRUE is always FALSE.

Reality: NOT flips TRUE to FALSE and FALSE to TRUE, but NOT NULL remains NULL (unknown).

Why it matters: Ignoring NULL behavior causes unexpected query results, especially with missing data.

Quick: Does SQL evaluate AND and OR operators left to right without priority? Commit yes or no.

Common Belief: AND and OR have the same priority and are evaluated left to right.

Reality: AND has higher priority than OR, so it is evaluated first unless parentheses change order.

Why it matters: Wrong assumptions about precedence cause logical errors and incorrect data filtering.

Quick: Does NULL behave like FALSE in logical conditions? Commit yes or no.

Common Belief: NULL is treated as FALSE in logical operations.

Reality: NULL represents unknown, so logical operations with NULL can result in UNKNOWN, not FALSE.

Why it matters: Treating NULL as FALSE can exclude rows unintentionally, leading to incomplete query results.

Expert Zone

1

Logical operators in SQL follow three-valued logic (TRUE, FALSE, UNKNOWN), which differs from classical two-valued logic.

2

Short-circuit evaluation can improve performance but may skip side effects in user-defined functions within conditions.

3

Parentheses not only clarify logic but can also affect query optimization by the database engine.

When NOT to use

Avoid complex nested logical operators when simpler alternatives like IN, BETWEEN, or EXISTS can express the condition more clearly and efficiently. For very large datasets, consider indexed columns and query plans to optimize filtering instead of relying solely on logical operators.

Production Patterns

In real systems, logical operators are combined with indexes and query hints to optimize performance. Complex filters often use parentheses to ensure correct logic and maintain readability. Handling NULL explicitly with IS NULL or COALESCE is common to avoid surprises.

Connections

Boolean Algebra

Logical operators in SQL are practical applications of Boolean algebra principles.

Understanding Boolean algebra helps grasp how AND, OR, and NOT combine conditions logically and predict query outcomes.

Digital Circuit Design

AND, OR, and NOT correspond to basic logic gates in digital circuits.

Recognizing these operators as logic gates reveals their fundamental role in decision-making processes, both in hardware and databases.

Set Theory

Logical operators mirror set operations: AND as intersection, OR as union, NOT as complement.

Viewing query conditions as sets clarifies how data is filtered and combined, aiding complex query construction.

Common Pitfalls

#1 Incorrect use of OR without parentheses causing wrong logic.

Wrong approach:

SELECT * FROM employees WHERE department = 'Sales' OR department = 'Marketing' AND active = 1;

Correct approach:

SELECT * FROM employees WHERE (department = 'Sales' OR department = 'Marketing') AND active = 1;

Root cause:

Misunderstanding operator precedence causes AND to bind tighter than OR, changing intended logic.

#2 Assuming NOT works on multiple conditions directly.

Wrong approach:

SELECT * FROM orders WHERE NOT status = 'shipped' OR amount > 100;

Correct approach:

SELECT * FROM orders WHERE NOT (status = 'shipped' OR amount > 100);

Root cause:

NOT applies only to the immediate condition; parentheses are needed to negate combined conditions.

#3 Ignoring NULL values in logical conditions.

Wrong approach:

SELECT * FROM users WHERE active = TRUE AND last_login > '2023-01-01'; -- misses users with NULL active

Correct approach:

SELECT * FROM users WHERE (active = TRUE OR active IS NULL) AND last_login > '2023-01-01';

Root cause:

NULL is not TRUE or FALSE, so conditions exclude rows with NULL unless explicitly handled.

Key Takeaways

AND, OR, and NOT are essential logical operators that combine or invert conditions to filter database rows.

AND requires all conditions to be true, OR requires at least one, and NOT reverses the truth of a condition.

Operator precedence matters: AND is evaluated before OR unless parentheses change the order.

SQL uses three-valued logic with TRUE, FALSE, and UNKNOWN (NULL), which affects how logical operators behave.

Handling NULL explicitly is crucial to avoid unexpected query results when using logical operators.