Logical Operators - Learning Module

Loading content...

0/252

OR Operator

Expressing Alternatives in SQL

While AND requires all conditions to be true simultaneously, many real-world queries need to express alternatives—conditions where any one of several possibilities qualifies a row for inclusion. A customer service system finds tickets that are either urgent OR overdue. A product search returns items that are in the Electronics category OR in the Computers category. An HR system identifies employees who report to Manager A OR Manager B.

The OR operator provides this capability through logical disjunction. Understanding OR is essential for building flexible queries, but it comes with important considerations: different truth table behavior, significant performance implications, and critical interaction rules with AND.

What You Will Learn

By the end of this page, you will master the OR operator: its disjunctive semantics, behavior with NULL in three-valued logic, performance characteristics (including common pitfalls), and best practices for writing clear, efficient OR conditions.

The OR Operator Fundamentals

The OR operator performs logical disjunction—a fundamental operation from Boolean algebra. In SQL, OR combines two or more conditions and returns TRUE when at least one condition is TRUE.

Formal Definition:

condition1 OR condition2 → TRUE if:
  - condition1 evaluates to TRUE, OR
  - condition2 evaluates to TRUE, OR
  - BOTH evaluate to TRUE

This extends to multiple conditions:

condition1 OR condition2 OR ... OR conditionN → TRUE
if AT LEAST ONE condition evaluates to TRUE

The Fundamental Difference from AND:

AND is restrictive: all must be true → narrows results
OR is expansive: any can be true → broadens results

basic_or_syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Basic OR Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2;
 
-- Example: Find employees in Sales OR Marketing departments
SELECT employee_id, first_name, last_name, department
FROM employees
WHERE department = 'Sales'
   OR department = 'Marketing';
 
-- Multiple OR conditions
SELECT ticket_id, subject, priority, status
FROM support_tickets
WHERE priority = 'Critical'
   OR priority = 'High'
   OR status = 'Overdue'
   OR escalated = TRUE;
 
-- OR with different columns
SELECT order_id, customer_id, total, status
FROM orders
WHERE total > 10000           -- High-value orders
   OR customer_id = 1001      -- VIP customer
   OR express_shipping = TRUE; -- Rush orders

OR vs IN Clause

When checking if a column equals any of several values, prefer the IN clause over multiple ORs. department IN ('Sales', 'Marketing', 'Support') is cleaner than department = 'Sales' OR department = 'Marketing' OR department = 'Support'. The database treats them equivalently, but IN is more readable and maintainable.

Truth Table Analysis

Understanding OR requires internalizing its truth table—the complete specification of how OR evaluates based on its operands.

Binary Truth Table (Classical Logic):

In classical Boolean algebra with only TRUE and FALSE values:

OR Truth Table (Two-Valued Logic)
Condition A	Condition B	A OR B
TRUE	TRUE	TRUE
TRUE	FALSE	TRUE
FALSE	TRUE	TRUE
FALSE	FALSE	FALSE

Key Insight: OR is the most permissive logical operator. Only one combination—both operands FALSE—produces a FALSE result. Every other combination yields TRUE.

Comparison with AND:

AND vs OR: Fundamental Contrast
Aspect	AND (Conjunction)	OR (Disjunction)
TRUE result requires	ALL conditions TRUE	AT LEAST ONE condition TRUE
FALSE result requires	AT LEAST ONE condition FALSE	ALL conditions FALSE
Effect on result set	Narrows/restricts	Expands/broadens
Conceptual model	Intersection	Union
Strictness	Most restrictive	Most permissive

Set Theory Perspective

Think of AND as set intersection (rows must be in ALL condition sets) and OR as set union (rows can be in ANY condition set). If condition A matches 100 rows and condition B matches 80 rows, A AND B yields at most 80 rows, while A OR B yields at least 100 rows (possibly up to 180 if there's no overlap).

OR with Three-Valued Logic (NULL)

Like AND, OR operates within SQL's three-valued logic when NULL values are involved. The extended truth table reveals important behaviors:

OR Truth Table with Three-Valued Logic
Condition A	Condition B	A OR B	Explanation
TRUE	TRUE	TRUE	At least one true
TRUE	UNKNOWN	TRUE	One TRUE is sufficient!
TRUE	FALSE	TRUE	At least one true
UNKNOWN	TRUE	TRUE	One TRUE is sufficient!
UNKNOWN	UNKNOWN	UNKNOWN	Both values unknown
UNKNOWN	FALSE	UNKNOWN	Can't determine—depends on unknown value
FALSE	TRUE	TRUE	At least one true
FALSE	UNKNOWN	UNKNOWN	Can't determine—depends on unknown value
FALSE	FALSE	FALSE	Neither is true

Critical Rule: TRUE OR UNKNOWN = TRUE

This is the mirror of AND's rule. If we know one condition is TRUE, the disjunction must be TRUE regardless of the other value. OR only requires one TRUE to succeed, so a single TRUE is decisive.

But: FALSE OR UNKNOWN = UNKNOWN

If the known condition is FALSE, we depend entirely on the unknown value. If it's TRUE, the result is TRUE. If it's FALSE, the result is FALSE. We can't determine which.

or_with_null.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Table: products
-- | product_id | name           | discount_pct | clearance |
-- |------------|----------------|--------------|-----------|
-- | 1          | Widget A       | 20           | FALSE     |
-- | 2          | Widget B       | NULL         | TRUE      |  -- Discount unknown
-- | 3          | Widget C       | NULL         | FALSE     |  -- Discount unknown
-- | 4          | Widget D       | 15           | FALSE     |
 
-- Query: Find products with discount > 10% OR on clearance
SELECT product_id, name, discount_pct, clearance
FROM products
WHERE discount_pct > 10 OR clearance = TRUE;
 
-- Analysis:
-- Widget A: 20 > 10 → TRUE  OR  FALSE = TRUE   ✓ (included via discount)
-- Widget B: NULL > 10 → UNKNOWN  OR  TRUE = TRUE   ✓ (included via clearance)
-- Widget C: NULL > 10 → UNKNOWN  OR  FALSE = UNKNOWN (excluded)
-- Widget D: 15 > 10 → TRUE  OR  FALSE = TRUE   ✓ (included via discount)
 
-- Result: Widgets A, B, D returned
-- Widget B is INCLUDED despite NULL discount because clearance = TRUE
-- Widget C is EXCLUDED because UNKNOWN OR FALSE = UNKNOWN
 
-- Key insight: OR can "rescue" rows with NULL if another condition is TRUE

OR Can Include Rows AND Would Exclude

A row with NULL in one condition can still appear in OR results if another condition is TRUE. This is a key behavioral difference from AND. With A OR B, a row needs only one TRUE; with A AND B, both must be TRUE (and UNKNOWN fails).

Performance Implications

OR presents significant performance challenges that don't exist with AND. Understanding these is crucial for writing efficient queries:

Why OR Is Harder to Optimize

•Index Usage Limitations — Each branch of an OR may need a different index. A single composite index rarely helps OR conditions on different columns.
•No Early Termination of Rows — With AND, a FALSE in any condition eliminates the row. With OR, you may need to check ALL conditions before concluding FALSE.
•Union of Index Scans — Databases often implement OR as separate index lookups that are merged (UNION), which is less efficient than a single index scan.
•Full Table Scan Risk — If any OR branch lacks an index, the entire query might fall back to a full table scan.

or_performance.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- Scenario: Query with OR on different columns
SELECT * FROM orders
WHERE customer_id = 12345
   OR product_id = 67890
   OR order_date = '2024-01-15';
 
-- Even with indexes on customer_id, product_id, and order_date,
-- the database may:
-- 1. Scan customer_id index → gather matching row IDs
-- 2. Scan product_id index → gather matching row IDs  
-- 3. Scan order_date index → gather matching row IDs
-- 4. UNION all row IDs (remove duplicates)
-- 5. Fetch rows by ID
-- This is much more expensive than a single index scan!
 
-- Sometimes rewriting as UNION ALL with DISTINCT is clearer for optimizer:
SELECT * FROM orders WHERE customer_id = 12345
UNION
SELECT * FROM orders WHERE product_id = 67890
UNION
SELECT * FROM orders WHERE order_date = '2024-01-15';
 
-- For OR on SAME column (multiple values), IN is efficient:
-- These are optimized the same way:
SELECT * FROM orders WHERE status IN ('Pending', 'Processing', 'Shipped');
SELECT * FROM orders 
WHERE status = 'Pending' OR status = 'Processing' OR status = 'Shipped';

OR on Same Column Is Efficient

When OR conditions apply to the same column (e.g., department = 'A' OR department = 'B'), databases optimize this as a single index scan for multiple values—equivalent to the IN clause. The performance concern is primarily with OR across different columns.

Comparison: AND vs OR Index Usage

Index Utilization: AND vs OR
Scenario	AND Behavior	OR Behavior
Same column, multiple values	N/A (impossible for equality)	Single index scan (like IN)
Different columns with indexes	Can use composite index or intersect indexes	Must union multiple index scans
One column indexed, one not	May use index, then filter	Often falls back to full table scan
Range conditions	Composite index works well	Typically requires multiple scans

Practical Patterns

Let's examine common and appropriate uses of OR in SQL queries:

Matching multiple values in one column (prefer IN for clarity):

category_matching.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Using OR (valid but verbose)
SELECT product_id, name, category
FROM products
WHERE category = 'Electronics'
   OR category = 'Computers'
   OR category = 'Phones';
 
-- Using IN (preferred - cleaner)
SELECT product_id, name, category
FROM products
WHERE category IN ('Electronics', 'Computers', 'Phones');
 
-- With subquery for dynamic values
SELECT product_id, name, category
FROM products
WHERE category IN (
    SELECT category_name 
    FROM featured_categories 
    WHERE is_active = TRUE
);

OR vs UNION

Sometimes OR conditions and UNION queries achieve similar results. Understanding when to use each helps write optimal queries:

OR vs UNION Comparison
Aspect	OR in WHERE	UNION of SELECTs
Single query execution	Yes	No (multiple queries merged)
Duplicate handling	Automatic (single pass)	UNION removes, UNION ALL keeps
Different columns per condition	Not possible	Possible (with same output schema)
Different tables	Not possible	Natural use case
Index usage (different columns)	Often poor	Can use optimal index per query
Query plan visibility	Single complex plan	Clear, separate plans

or_vs_union.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- Equivalent results, different execution:
 
-- Using OR (single query)
SELECT employee_id, name, department, salary
FROM employees
WHERE department = 'Sales' OR salary > 100000;
 
-- Using UNION (multiple queries merged)
SELECT employee_id, name, department, salary
FROM employees
WHERE department = 'Sales'
UNION  -- Removes duplicates automatically
SELECT employee_id, name, department, salary
FROM employees
WHERE salary > 100000;
 
-- UNION can be faster when:
-- 1. Each condition uses a different optimal index
-- 2. The conditions are very selective (few rows each)
 
-- OR is typically better when:
-- 1. Conditions are on the same column (uses IN-style optimization)
-- 2. You're already in a complex WHERE clause
-- 3. Result needs additional filtering with AND
 
-- UNION is required when:
-- 1. Combining rows from different tables
-- 2. Applying different transformations per condition
SELECT name, 'Sales' as source FROM sales_employees WHERE metric > 100
UNION ALL
SELECT name, 'Marketing' as source FROM marketing_employees WHERE metric > 100;

Check Execution Plans

When in doubt about OR vs UNION performance, examine the execution plans for both approaches. Database optimizers vary, and the best choice depends on data distribution, indexes, and specific conditions. Use EXPLAIN (or your database's equivalent) to make data-driven decisions.

Common Mistakes with OR

OR introduces several common pitfalls that even experienced developers encounter:

or_mistakes.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- MISTAKE 1: Forgetting parentheses with AND (see "Precedence" page)
SELECT * FROM orders
WHERE status = 'Active' AND total > 100 OR total > 1000;
-- Parses as: (status = 'Active' AND total > 100) OR (total > 1000)
-- NOT as: status = 'Active' AND (total > 100 OR total > 1000)
 
-- MISTAKE 2: Using OR when AND is needed
-- "Find products that are NOT Electronics AND NOT Clothing"
-- WRONG:
SELECT * FROM products
WHERE category != 'Electronics' OR category != 'Clothing';
-- This returns ALL rows! Every category fails at least one condition.
 
-- CORRECT:
SELECT * FROM products
WHERE category != 'Electronics' AND category != 'Clothing';
-- Or equivalently:
SELECT * FROM products
WHERE category NOT IN ('Electronics', 'Clothing');
 
-- MISTAKE 3: Tautology (always-true condition)
SELECT * FROM employees
WHERE department = 'Sales' OR department != 'Sales';
-- This matches ALL rows! Always at least one is true.
 
-- MISTAKE 4: Inefficient OR across indexed columns
SELECT * FROM products
WHERE product_id = 1001 OR name = 'Widget';
-- May not use indexes efficiently. Consider UNION if large table.
 
-- MISTAKE 5: OR with NULL comparison
SELECT * FROM employees
WHERE bonus = 1000 OR bonus = NULL;  -- WRONG! = NULL is never TRUE
-- CORRECT:
SELECT * FROM employees
WHERE bonus = 1000 OR bonus IS NULL;

De Morgan's Law Pitfall

When negating compound conditions, remember De Morgan's Laws: NOT(A OR B) = (NOT A) AND (NOT B). Many developers incorrectly write NOT A OR NOT B when they mean NOT A AND NOT B. The difference is profound—one is nearly a tautology, the other is restrictive.

Common Errors

•Mixing AND/OR without parentheses — AND binds tighter; results differ from intent
•De Morgan confusion — Using OR when negating, when AND is needed
•Performance blindness — Not checking execution plan for OR queries
•NULL = NULL assumption — Using = with NULL instead of IS NULL

Best Practices

•Always use parentheses — Make grouping explicit when mixing operators
•Think in sets — Visualize OR as union, AND as intersection
•Check execution plans — Verify index usage for OR queries
•Use IN for same-column ORs — Cleaner and equally efficient

Summary: Mastering OR

The OR operator provides essential flexibility for expressing alternative conditions. Let's consolidate the key insights:

Key Takeaways

•OR performs logical disjunction — Returns TRUE when at least one condition is TRUE. It's the most permissive logical operator.
•Three-valued logic: TRUE OR UNKNOWN = TRUE — A single TRUE is decisive for OR, regardless of other values.
•OR expands result sets — Each OR condition can only add to (or maintain) the result set, never reduce it.
•Performance requires attention — OR on different columns may not use indexes efficiently. Consider UNION for optimization.
•IN is preferable for same-column OR — column IN (a, b, c) is cleaner than column = a OR column = b OR column = c.
•De Morgan's Laws apply — NOT(A OR B) = (NOT A) AND (NOT B). Crucial for correct negation.

What's Next:

AND requires all conditions to be true; OR requires at least one. But what about expressing that a condition should not be true? The next page explores the NOT operator, which provides logical negation—inverting the truth value of any condition.

Page Complete

You now understand the OR operator comprehensively—from its Boolean algebra foundations through SQL's three-valued logic to practical performance optimization. Next, we'll explore NOT, which enables expressing exclusion and negated conditions.