WHERE with OR operator in SQL - Deep Dive

Overview - WHERE with OR operator

What is it?

The WHERE clause in SQL is used to filter rows from a table based on specified conditions. The OR operator allows you to combine multiple conditions so that if any one of them is true, the row is included in the result. This helps you select data that meets at least one of several criteria. It is a way to broaden your search in a database query.

Why it matters

Without the OR operator, you would only be able to filter rows that meet all conditions simultaneously, which is often too restrictive. The OR operator lets you find data that matches any of several conditions, making queries more flexible and useful. This is important in real life when you want to find records that fit multiple possible scenarios, like customers from different cities or products in different categories.

Where it fits

Before learning WHERE with OR, you should understand basic SQL SELECT statements and simple WHERE clauses with single conditions. After mastering OR, you can learn about combining AND and OR together, using parentheses for grouping, and more advanced filtering techniques like IN, BETWEEN, and subqueries.

Mental Model

Core Idea

The OR operator in a WHERE clause lets you select rows that meet at least one of multiple conditions, expanding your search to include any matching criteria.

Think of it like...

Imagine you are looking for friends who like either pizza OR ice cream. You don't need them to like both, just one is enough to invite them. The OR operator works the same way in filtering data.

SELECT * FROM table
WHERE condition1 OR condition2 OR condition3;

Result includes rows where any condition is true:

┌─────────────┐
│ Row matches │
├─────────────┤
│ condition1  │
│ OR          │
│ condition2  │
│ OR          │
│ condition3  │
└─────────────┘

If any condition is true, row is included.

Build-Up - 6 Steps

1

Foundation

Basic WHERE Clause Filtering

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

The WHERE clause filters rows based on one condition. For example, to find all employees in the 'Sales' department: SELECT * FROM employees WHERE department = 'Sales';

Result

Returns all rows where the department is exactly 'Sales'.

Understanding single-condition filtering is the foundation for combining multiple conditions later.

2

Foundation

Introduction to OR Operator

Concept: Learn that OR combines two conditions so that if either is true, the row is included.

The OR operator lets you check multiple conditions. For example, to find employees in 'Sales' or 'Marketing': SELECT * FROM employees WHERE department = 'Sales' OR department = 'Marketing';

Result

Returns rows where department is either 'Sales' or 'Marketing'.

OR expands your search to include rows matching any of the conditions, not just all.

3

Intermediate

Multiple OR Conditions Together

🤔Before reading on: Do you think adding more OR conditions narrows or broadens the result? Commit to your answer.

Concept: You can combine many OR conditions to include rows matching any of them.

You can chain OR operators to check many values. For example: SELECT * FROM products WHERE category = 'Books' OR category = 'Electronics' OR category = 'Toys'; This returns products in any of these categories.

Result

Rows matching any category listed are included.

Knowing that OR conditions accumulate to broaden results helps you build flexible queries.

4

Intermediate

Mixing OR with AND Conditions

🤔Before reading on: Does AND or OR have higher priority in SQL? Predict which runs first.

Concept: AND and OR can be combined, but AND has higher priority unless parentheses change order.

Example: SELECT * FROM employees WHERE department = 'Sales' OR department = 'Marketing' AND status = 'Active'; This means: WHERE department = 'Sales' OR (department = 'Marketing' AND status = 'Active'); Use parentheses to control logic: WHERE (department = 'Sales' OR department = 'Marketing') AND status = 'Active';

Result

Without parentheses, 'Marketing' employees must be 'Active' but 'Sales' employees are included regardless of status. With parentheses, only active employees in either department are included.

Understanding operator precedence prevents logic errors in queries combining AND and OR.

5

Advanced

Using OR with NULL Values

🤔Before reading on: Does OR return true if one condition is true and the other is NULL? Commit your guess.

Concept: NULL represents unknown, and OR with NULL follows three-valued logic affecting results.

Example: SELECT * FROM orders WHERE shipped_date IS NULL OR status = 'Pending'; If shipped_date is NULL, that condition is true. If status is 'Pending', that condition is true. OR returns true if any condition is true, even if the other is NULL. But if both conditions are NULL or false, the row is excluded.

Result

Rows with unknown shipped_date or pending status are included.

Knowing how NULL interacts with OR helps avoid unexpected missing or included rows.

6

Expert

Performance Impact of OR in Queries

🤔Before reading on: Do you think OR conditions always slow down queries? Commit your answer.

Concept: OR can cause slower queries because it may prevent efficient use of indexes unless carefully structured.

Databases often use indexes to find rows quickly. When OR combines conditions on different columns, indexes may not be used effectively. Example: SELECT * FROM employees WHERE department = 'Sales' OR salary > 70000; This might scan more rows than a query with only one condition. To optimize, sometimes UNION queries or indexed views are better.

Result

Queries with OR can be slower, especially on large tables without proper indexing.

Understanding OR's impact on performance guides writing efficient queries in production.

Under the Hood

When a SQL query with WHERE and OR runs, the database engine evaluates each condition for every row. It uses logical OR to combine results: if any condition is true, the row passes. Internally, the engine may use indexes to speed up checks, but OR conditions on different columns can force full scans. The engine applies three-valued logic (true, false, unknown) when NULLs are involved, affecting which rows are included.

Why designed this way?

The OR operator was designed to allow flexible filtering by combining multiple criteria. It follows Boolean logic familiar from mathematics and programming. The three-valued logic with NULLs reflects the reality that some data is unknown, so queries must handle uncertainty. The design balances expressiveness with performance, though complex OR conditions can challenge optimization.

Query Execution Flow:

┌───────────────┐
│ Start Query   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ For each row  │
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│ Evaluate condition1          │
│ Evaluate condition2          │
│ ...                         │
│ Combine with OR (true if any)│
└──────┬──────────────────────┘
       │
       ▼
┌───────────────┐
│ Include row if │
│ OR result true │
└───────────────┘

Myth Busters - 4 Common Misconceptions

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

Common Belief: OR means all conditions must be true, like AND.

Reality: OR means only one condition needs to be true for the row to be included.

Why it matters: Misunderstanding this leads to queries that return no rows or too few rows, causing confusion and wrong data.

Quick: Does OR always run slower than AND? Commit yes or no.

Common Belief: OR is always slower than AND in queries.

Reality: OR can be slower, but with proper indexes and query design, it can be efficient. AND can also be slow if conditions are complex.

Why it matters: Assuming OR is always slow may cause unnecessary query rewrites or avoidance of useful filters.

Quick: Does OR ignore NULL values in conditions? Commit yes or no.

Common Belief: OR treats NULL as false and ignores it.

Reality: OR uses three-valued logic; NULL means unknown, which affects the final result differently than false.

Why it matters: Ignoring NULL behavior can cause missing or extra rows unexpectedly.

Quick: Can you mix AND and OR without parentheses safely? Commit yes or no.

Common Belief: You can mix AND and OR without parentheses and get the expected result.

Reality: AND has higher precedence than OR, so without parentheses, the logic may differ from what you expect.

Why it matters: Wrong logic leads to incorrect data retrieval, which can cause serious application errors.

Expert Zone

1

OR conditions on different columns often prevent the use of multiple indexes simultaneously, causing full table scans.

2

Using UNION ALL with separate queries can sometimes replace OR for better performance and clearer logic.

3

Three-valued logic with NULLs means that OR can return unknown results, so explicit NULL checks are often necessary.

When NOT to use

Avoid using OR when filtering on multiple columns with large datasets if performance is critical; instead, consider rewriting queries using UNION or indexed views. Also, avoid mixing AND and OR without clear parentheses to prevent logic errors.

Production Patterns

In production, developers often rewrite OR conditions as UNION queries to leverage indexes better. They also carefully use parentheses to ensure correct logic and add explicit NULL checks to handle unknown data. Query planners and optimizers are monitored to detect slow OR queries.

Connections

Boolean Logic

OR in SQL is a direct application of Boolean OR logic from mathematics and computer science.

Understanding Boolean logic helps grasp how SQL combines conditions and why operator precedence matters.

Set Theory

OR corresponds to the union of sets, where rows matching any condition belong to the combined set.

Seeing OR as a union operation clarifies how multiple conditions expand the result set.

Decision Making in Psychology

OR logic mimics how humans decide when any one of several options is acceptable.

Recognizing this connection helps understand why OR is intuitive for filtering choices in databases.

Common Pitfalls

#1 Mixing AND and OR without parentheses causes unexpected results.

Wrong approach:

SELECT * FROM employees WHERE department = 'Sales' OR department = 'Marketing' AND status = 'Active';

Correct approach:

SELECT * FROM employees WHERE (department = 'Sales' OR department = 'Marketing') AND status = 'Active';

Root cause:

Misunderstanding operator precedence where AND binds tighter than OR.

#2 Assuming OR conditions always use indexes efficiently.

Wrong approach:

SELECT * FROM products WHERE category = 'Books' OR price > 1000;

Correct approach:

SELECT * FROM products WHERE category = 'Books'
UNION
SELECT * FROM products WHERE price > 1000;

Root cause:

Not realizing OR on different columns can prevent index use, causing slow queries.

#3 Ignoring NULL values in OR conditions leads to missing rows.

Wrong approach:

SELECT * FROM orders WHERE shipped_date = NULL OR status = 'Pending';

Correct approach:

SELECT * FROM orders WHERE shipped_date IS NULL OR status = 'Pending';

Root cause:

Using '=' to check NULL instead of IS NULL, misunderstanding NULL semantics.

Key Takeaways

The OR operator in WHERE clauses lets you select rows that meet any one of multiple conditions, broadening your search.

AND has higher precedence than OR, so use parentheses to control logic and avoid mistakes.

NULL values affect OR conditions through three-valued logic, so explicit NULL checks are necessary.

OR conditions can slow queries if not designed carefully, especially on large tables without proper indexing.

Experienced users rewrite OR queries using UNION or add parentheses and NULL checks to ensure correctness and performance.