OR Operator in SQL
Introduction
The OR operator combines SQL conditions when one or more alternatives may satisfy a rule. A support ticket may be urgent because its severity is critical or because a high-value customer opened it. A user search may match email or phone. An account may be visible when it is active or trial, provided every result still belongs to the authenticated tenant.
SELECT ticket_id, subject, severity
FROM support_tickets
WHERE severity = 'CRITICAL'
OR customer_tier = 'PREMIUM';
OR usually broadens a result set, which makes it useful for search and alternative eligibility but sensitive in authorization and destructive statements. Correct use depends on parentheses, AND precedence, NULL, data types, indexes, and the exact meaning of "at least one."
Basic OR Syntax
The basic form places OR between complete conditions. More alternatives can be added by repeating it.
SELECT column_list
FROM table_name
WHERE condition_one
OR condition_two
OR condition_three;
The complete predicate is TRUE when any condition is TRUE. If two or all alternatives are TRUE, the row still appears once at this filtering stage. OR does not mean exactly one condition; that exclusive requirement needs additional logic.
OR Truth Table
| Condition A | Condition B | A OR B |
|---|---|---|
| TRUE | TRUE | TRUE |
| TRUE | FALSE | TRUE |
| FALSE | TRUE | TRUE |
| FALSE | FALSE | FALSE |
Only the final row is FALSE. SQL adds UNKNOWN for nullable expressions, which changes some intuitive expectations. WHERE retains only TRUE and excludes both FALSE and UNKNOWN.
OR Represents Alternatives
Use OR when the business requirement genuinely allows separate paths. An order can enter a review queue when its amount is high or its device is blocked. Each alternative should be a complete, testable condition.
SELECT order_id, total_amount, device_risk
FROM orders
WHERE total_amount >= 10000.00
OR device_risk = 'BLOCKED';
A row satisfying either condition qualifies. If the policy requires both high amount and blocked device, AND is the correct operator. Translating "either," "any," and "at least one" from precise requirements helps prevent this basic but consequential mistake.
How OR Broadens Results
Adding an OR branch usually expands or preserves the qualifying set because another path becomes available. Starting with PAID orders and adding SHIPPED orders returns the union of both status groups. This is a logical tendency, not a performance guarantee.
An alternative can be redundant when it is already covered by a broader condition. For example, price > 100 OR price > 200 simplifies to price > 100. Review overlapping branches so the policy remains understandable.
OR with Equality Conditions
Repeated equality against one expression is common for statuses, categories, departments, and codes.
WHERE status = 'PAID'
OR status = 'SHIPPED'
OR status = 'DELIVERED'
This is correct, but an IN list is generally more concise and easier to maintain.
WHERE status IN ('PAID', 'SHIPPED', 'DELIVERED')
IN expresses membership in one value set. It does not replace OR when alternatives compare different columns or use different operators.
When OR Cannot Become IN
IN expects one expression on the left. A customer lookup matching either email or phone compares two columns, so OR remains appropriate.
SELECT customer_id, display_name
FROM customers
WHERE email = :search_value
OR phone_number = :search_value;
Likewise, quantity < reorder_level OR expires_at < CURRENT_TIMESTAMP combines different business facts and cannot become one IN expression. Avoid forcing unlike conditions into a structure that changes meaning.
OR with Different Operators
Alternatives can use different comparisons. A payment alert might identify a high amount, a foreign country, or a missing verification timestamp.
WHERE amount >= :high_amount
OR country_code <> :home_country
OR verified_at IS NULL
This broad rule flags any of the three. In practice, global requirements such as tenant, date window, or active status should be connected with AND outside the grouped OR alternatives.
OR with NULL
OR is often used to include a known condition or missing information deliberately. A subscription can be active when its end is in the future or when no end has been assigned.
WHERE valid_from <= :as_of_time
AND (valid_to > :as_of_time OR valid_to IS NULL)
The inner OR defines two acceptable end states; the outer AND keeps the start requirement mandatory. Use IS NULL rather than equality with NULL. Decide whether missing data truly represents an allowed open end rather than automatically including it.
OR and SQL Three-Valued Logic
| A | B | A OR B |
|---|---|---|
| TRUE | UNKNOWN | TRUE |
| FALSE | UNKNOWN | UNKNOWN |
| UNKNOWN | UNKNOWN | UNKNOWN |
One known TRUE settles OR as TRUE. If every known condition is FALSE and another is UNKNOWN, the result remains UNKNOWN. Since WHERE keeps only TRUE, UNKNOWN rows disappear unless another OR branch is true or NULL is handled explicitly.
Explicitly Including Unknown Values
Suppose a review report needs customers with a low score or no score yet. The missing case must be written explicitly.
WHERE review_score < :minimum_score
OR review_score IS NULL
Without the IS NULL branch, a missing score compared with the threshold becomes UNKNOWN and is excluded. Another business rule may intentionally exclude missing scores. NULL behavior should be specified, not guessed.
OR with LIKE for Multi-Field Search
Search interfaces commonly match one term against several fields.
SELECT customer_id, display_name, email
FROM customers
WHERE display_name LIKE :pattern
OR email LIKE :pattern
OR phone_number LIKE :pattern;
Escape wildcard characters if users expect literal matching, use a deliberate case and collation policy, and bound result size. Leading-wildcard searches across many columns can be expensive; full-text search or a dedicated search index may be more appropriate at scale.
Combining OR with Mandatory Filters
Search alternatives usually operate inside global visibility and authorization rules. Parentheses keep the OR group subordinate to mandatory AND filters.
WHERE tenant_id = :tenant_id
AND deleted_at IS NULL
AND is_active = TRUE
AND (
display_name LIKE :pattern
OR email LIKE :pattern
OR phone_number LIKE :pattern
)
Without the final parentheses, one search branch can escape tenant, deleted, or active scope because AND has higher precedence than OR.
Operator Precedence
NOT generally has higher precedence than AND, and AND has higher precedence than OR. Therefore A OR B AND C is interpreted as A OR (B AND C). It is not interpreted left to right as (A OR B) AND C.
Use parentheses to show business meaning whenever AND and OR are mixed. Even when default precedence produces the intended result, explicit grouping reduces review mistakes and protects the expression when future conditions are added.
A Critical Security Grouping Example
-- Unsafe: every public document may qualify across tenants.
WHERE tenant_id = :tenant_id
AND owner_id = :user_id
OR visibility = 'PUBLIC'
-- Correct: tenant scope applies to both alternatives.
WHERE tenant_id = :tenant_id
AND (owner_id = :user_id OR visibility = 'PUBLIC')
The unsafe predicate is interpreted as (tenant AND owner) OR public. A public record outside the tenant passes. Similar errors can escape ownership, soft-delete, regional, or status restrictions.
OR and Authorization
Authorization sometimes permits alternative paths: the caller owns the record or has an administrative role. Those alternatives must remain inside every global security boundary.
WHERE tenant_id = :authenticated_tenant_id
AND document_id = :document_id
AND deleted_at IS NULL
AND (
owner_id = :authenticated_user_id
OR :authenticated_role = 'ADMIN'
)
The role parameter must come from trusted identity, not a request body. In many systems, policy is better enforced in a service or row-level security layer, but SQL still needs consistent scope.
OR and Soft Delete
A missing-parentheses OR can accidentally reveal deleted rows. Keep the delete filter outside the alternative group.
WHERE tenant_id = :tenant_id
AND deleted_at IS NULL
AND (status = 'ACTIVE' OR status = 'TRIAL')
Since the alternatives compare one column, IN is clearer: status IN ('ACTIVE', 'TRIAL'). Centralizing ordinary visibility rules reduces repetition, but privileged recovery queries should remain explicit and separately authorized.
OR with EXISTS and NOT EXISTS
A customer might qualify when either a completed order exists or no onboarding task remains.
WHERE EXISTS (
SELECT 1 FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.status = 'COMPLETED'
)
OR NOT EXISTS (
SELECT 1 FROM onboarding_tasks AS t
WHERE t.customer_id = c.customer_id
AND t.completed_at IS NULL
)
Each branch is a complete relationship test. In real queries, wrap this group with tenant and visibility requirements. EXISTS is usually clearer than counting related rows when only existence matters.
OR in UPDATE Statements
OR can allow an update from several accepted states. An order cancellation might be legal while PENDING or PAID.
UPDATE orders
SET status = 'CANCELLED',
cancelled_at = CURRENT_TIMESTAMP
WHERE order_id = :order_id
AND tenant_id = :tenant_id
AND (status = 'PENDING' OR status = 'PAID');
An IN list makes the status alternatives clearer. Check that exactly one row changed. A zero count can indicate missing, unauthorized, or no longer cancellable state.
OR in DELETE Statements
OR is particularly sensitive in DELETE because any true branch selects a row for removal. An expired-session cleanup may remove sessions whose expiration passed or that were revoked.
DELETE FROM sessions
WHERE tenant_id = :tenant_id
AND (expires_at <= CURRENT_TIMESTAMP OR revoked_at IS NOT NULL);
Preview the same predicate with SELECT, count targets, validate global scope, use bounded batches for large tables, and understand foreign-key or audit behavior. Never add an unreviewed OR branch to a destructive query.
OR in JOIN Conditions
An OR join matches when either relationship path succeeds, but it can create duplicates and difficult plans. For example, a contact might match by customer ID or a legacy external ID.
JOIN contacts AS c
ON c.customer_id = o.customer_id
OR c.external_id = o.legacy_contact_id
Review whether two matches can occur for one row and whether the data model should normalize the alternatives. Separate queries combined with UNION may sometimes be clearer, but rewriting changes duplicate and performance behavior and must be tested.
OR in HAVING and CASE
HAVING can retain groups meeting either aggregate threshold.
SELECT customer_id, COUNT(*) AS order_count, SUM(total_amount) AS value
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 20
OR SUM(total_amount) >= 10000.00;
CASE branches can contain OR to represent alternative classification criteria. Branch order remains important because the first TRUE branch wins. Explicitly handle NULL when unknown facts should receive their own classification.
OR in CHECK Constraints
OR can express conditional validity. A shipment tracking number is required only when delivery_method is SHIPMENT.
CHECK (
delivery_method <> 'SHIPMENT'
OR tracking_number IS NOT NULL
)
The first branch permits every non-shipment method; the second permits shipment only with a tracking number. CHECK commonly accepts UNKNOWN, so define NOT NULL for fields whose absence should never pass.
OR Means At Least One, Not Exactly One
OR allows A, B, or both. If a record must provide exactly one contact method, ordinary OR is insufficient because both non-NULL values would satisfy it.
CHECK (
(email_address IS NOT NULL AND phone_number IS NULL)
OR (email_address IS NULL AND phone_number IS NOT NULL)
)
This expresses exclusive-or using two complete alternatives. Test all four combinations: neither, email only, phone only, and both.
OR for Temporal and Date Rules
OR commonly represents open-ended dates, multiple scheduling windows, or alternative expiration conditions. Parentheses show which time alternatives belong together.
WHERE starts_at <= CURRENT_TIMESTAMP
AND (ends_at > CURRENT_TIMESTAMP OR ends_at IS NULL)
AND (region_code = :region OR region_code = 'GLOBAL')
This promotion has started, has not ended or has no end, and is regional or global. Each OR group remains subordinate to mandatory start and tenant or visibility rules.
OR in Keyset Pagination
When a database does not support tuple comparison, keyset pagination over a non-unique timestamp uses OR to continue after the last composite cursor.
WHERE tenant_id = :tenant_id
AND (
created_at < :last_created_at
OR (created_at = :last_created_at AND order_id < :last_order_id)
)
ORDER BY created_at DESC, order_id DESC
FETCH FIRST :page_size ROWS ONLY;
The second branch handles timestamp ties. Matching index order and exact grouping are essential to avoid skipped or duplicated rows.
Overlapping and Redundant OR Ranges
Ranges can overlap or subsume each other. score < 50 OR score < 70 simplifies to score < 70. price < 100 OR price >= 100 is true for every known non-NULL price, though NULL remains UNKNOWN.
Simplify redundant logic when it improves clarity, but preserve explicit NULL behavior and business documentation. Apparently equivalent classical transformations can surprise when UNKNOWN participates.
OR with NOT and De Morgan's Law
De Morgan's laws connect negated AND and OR groups. For known booleans, NOT (A AND B) corresponds to NOT A OR NOT B, while NOT (A OR B) corresponds to NOT A AND NOT B.
SQL NULL adds UNKNOWN, so transformations must be tested with nullable input. Prefer the form that states the business rule most clearly rather than rewriting merely to remove NOT.
OR Does Not Guarantee Evaluation Order
Do not depend on OR evaluating left to right or stopping after a true condition. SQL is declarative, and the optimizer can reorder or transform predicates.
-- Do not use one branch as a procedural safety guard.
WHERE :input_is_valid = TRUE
OR risky_conversion(raw_value) = :target
Make every expression safe for any row using validated storage, explicit safe conversion functions where supported, CASE, or preprocessing. Short-circuit behavior observed in one plan is not a contract.
OR and Query Optimization
OR may be implemented with multiple index probes, bitmap operations, predicate expansion, or a scan. A same-column OR often becomes an IN lookup. Different-column alternatives can be harder because one index may not support every branch.
OR is not automatically slow. Performance depends on selectivity, statistics, indexes, result size, and the database optimizer. Start with correct logic, inspect the actual plan, and measure representative data before rewriting.
OR versus UNION
Sometimes separate branch queries combined with UNION or UNION ALL enable better access paths. The rewrite is not automatically equivalent.
SELECT customer_id FROM customers WHERE email = :value
UNION
SELECT customer_id FROM customers WHERE phone_number = :value;
UNION removes duplicates, while UNION ALL preserves them. A single OR filter returns one base row once, even if both conditions are true. A UNION ALL rewrite can duplicate it, and UNION adds deduplication cost. Verify result semantics before comparing performance.
OR and Index Design
Alternatives on the same indexed column can often use that index efficiently. Alternatives across different columns may benefit from separate indexes if each branch is selective and the optimizer can combine them. Broad branches can make a scan cheaper.
Composite indexes should follow complete query patterns, including mandatory tenant and status conditions around the OR group. Avoid creating an index for every branch without considering write overhead, storage, and actual workload.
Sargability and Case-Insensitive Search
Functions on searched columns can make every OR branch less searchable.
WHERE LOWER(display_name) LIKE :pattern
OR LOWER(email) LIKE :pattern
Depending on the database, use a suitable case-insensitive collation, normalized search columns, expression indexes, or full-text search. Match the design to search semantics; do not sacrifice correctness for an index or add indexes without measuring maintenance cost.
OR and SQL Injection
The classic injection payload appends an always-true OR condition to bypass authentication or filtering. It works when applications concatenate untrusted text into SQL. Use parameters so input remains a value and cannot become an operator.
SELECT user_id, password_hash
FROM users
WHERE username = :username
AND account_status = 'ACTIVE';
Do not accept raw filter expressions from a request. Map supported fields and operators through server-side allowlists, and keep tenant and authorization predicates mandatory regardless of user-selected search options.
Dynamic Search Conditions
Optional filters can use OR to ignore a condition when its parameter is absent.
WHERE tenant_id = :tenant_id
AND (:status IS NULL OR status = :status)
AND (:category_id IS NULL OR category_id = :category_id)
This is convenient, but large databases may produce parameter-sensitive plans. Generating only supplied predicates from a strict allowlist can be more efficient while still parameterizing values. Never let clients submit an arbitrary OR fragment.
OR for Error Detection and Alerting
Alternative invalid states are naturally expressed with OR. A data-quality report might flag missing email, invalid date order, or negative balance.
WHERE email IS NULL
OR valid_to <= valid_from
OR balance < 0
The query returns any record violating at least one rule. For diagnosis, calculate separate CASE flags so reviewers can see every failed rule rather than only knowing that the combined OR is true.
Translating Requirements into OR Groups
Start with the business sentence and identify global requirements separately from alternatives. In "show active tenant products that are public or owned by the caller," active and tenant are global requirements; public and owner are alternatives. That structure directly produces mandatory AND conditions around one parenthesized OR group.
WHERE tenant_id = :tenant_id
AND is_active = TRUE
AND deleted_at IS NULL
AND (is_public = TRUE OR owner_id = :authenticated_user_id)
Do not translate word by word without grouping. Natural language can contain implied scope, exceptions, and ambiguous "or" meanings. Write sample rows that should pass and fail before finalizing the predicate. Include a row that satisfies each alternative outside the global scope; it must still fail.
Subsumed and Duplicate OR Conditions
One OR branch subsumes another when every row matched by the narrower branch already matches the broader branch. For numeric values, amount > 100 OR amount > 500 simplifies to amount > 100. Repeating the exact same branch changes nothing but makes maintenance harder.
Subsumption can be less obvious across status sets and ranges. status IN ('NEW', 'OPEN') OR status = 'NEW' contains a redundant equality. Overlapping date ranges can often merge into one wider range, provided their inclusive and exclusive boundaries touch correctly. Simplify only after checking NULL, data types, and domain meaning; sometimes separate branches are retained intentionally to document distinct alert reasons.
Database Filtering versus Application Filtering
Do not retrieve broad data and apply OR conditions only in application memory when the database can filter correctly. Application-side filtering transfers unnecessary rows, weakens pagination and aggregate accuracy, can expose sensitive data before filtering, and prevents the optimizer from using indexes.
The database predicate should enforce tenant, ownership, visibility, and business alternatives. The application may still perform presentation filtering or rules requiring external context, but security must not depend on discarding unauthorized rows after retrieval. When external rules are unavoidable, fetch the smallest securely authorized candidate set first.
OR, Auditing, and Explainable Decisions
A combined OR tells whether at least one branch was true, but not which branch caused eligibility. Fraud, alerting, and compliance systems often need an explanation. Project separate CASE flags or store rule identifiers alongside the decision.
SELECT transaction_id,
CASE WHEN amount >= :limit THEN 1 ELSE 0 END AS high_amount,
CASE WHEN country_code <> :home_country THEN 1 ELSE 0 END AS foreign_country,
CASE WHEN device_risk = 'BLOCKED' THEN 1 ELSE 0 END AS blocked_device
FROM transactions
WHERE amount >= :limit
OR country_code <> :home_country
OR device_risk = 'BLOCKED';
This preserves the broad selection while making each reason observable. Keep rule versions and safe input facts when decisions must be audited, but avoid logging secrets or unnecessary personal data.
Monitoring OR Queries in Production
For important OR queries, monitor latency, rows examined, rows returned, buffer reads, temporary work, and plan changes. A query returning fifty rows after examining ten million may need better indexes, a more selective mandatory filter, or a different search strategy. Broad OR branches can become unexpectedly expensive as data distribution changes.
Use actual plans and workload-specific parameters. One rare email match may use indexes efficiently while a common status alternative dominates the same query and makes a scan reasonable. Keep statistics current, set sensible page limits, and measure before introducing a UNION rewrite or specialized search system.
Practical Production Examples
-- Retryable job.
WHERE status = 'NEW'
OR (status = 'FAILED' AND retry_count < :maximum_retries)
-- Contact lookup within mandatory tenant scope.
WHERE tenant_id = :tenant_id
AND (email = :value OR phone_number = :value)
-- Product visibility.
WHERE tenant_id = :tenant_id
AND deleted_at IS NULL
AND (is_public = TRUE OR owner_id = :user_id)
-- Low inventory or expired stock.
WHERE available_quantity <= reorder_level
OR expires_at <= CURRENT_TIMESTAMP
Each example should be reviewed for NULL, boundary, ownership, and index behavior. Destructive or state-changing versions also require affected-row validation and concurrency testing.
Testing OR Conditions
For A OR B, test true/true, true/false, false/true, and false/false. Add TRUE/UNKNOWN, FALSE/UNKNOWN, and UNKNOWN/UNKNOWN when either expression can be NULL. For more branches, prove that each alternative independently qualifies and that none qualifies in the negative case.
Boundary tests should cover values immediately below, exactly at, and above each threshold. Search tests should cover a match in each field, matches in several fields, no match, NULL fields, wildcard characters, case behavior, and result limits.
Security and Destructive Testing
Create another tenant's row that matches only the OR branch and prove it remains excluded. Repeat for deleted, unauthorized, inactive, and archived records. These adversarial rows reveal missing parentheses that ordinary happy-path data cannot.
For UPDATE and DELETE, execute the predicate as SELECT first in tests, assert exact targets, and check affected-row counts. Include a row that matches each branch and one that matches both. Verify a UNION rewrite, if introduced, preserves duplicates and mutation semantics.
Common OR Mistakes
- Mixing OR with AND without parentheses that show business scope.
- Allowing an OR branch to escape tenant, ownership, or soft-delete filters.
- Using OR where every requirement should be true.
- Assuming OR means exactly one rather than at least one.
- Ignoring NULL and SQL's UNKNOWN result.
- Writing a long same-column OR list instead of a clear IN predicate.
- Forcing different-column alternatives into IN incorrectly.
- Depending on left-to-right or short-circuit evaluation.
- Assuming OR is always slow or UNION is always faster.
- Rewriting with UNION ALL without checking duplicate behavior.
- Accepting untrusted logical SQL from search requests.
- Using unbounded multi-field OR search on large tables without measuring it.
OR Operator Review Checklist
- Confirm that each branch is a genuine alternative path.
- Use IN for repeated equality against one expression.
- Parenthesize every OR group combined with mandatory AND filters.
- Keep tenant, ownership, visibility, and delete scope outside alternatives.
- Define whether NULL should qualify and write that behavior explicitly.
- Distinguish at-least-one OR from exactly-one XOR requirements.
- Never rely on written predicate order for runtime safety.
- Parameterize values and allowlist dynamic SQL structure.
- Preview and count UPDATE or DELETE targets.
- Verify JOIN and UNION duplicate semantics.
- Test each branch, all branches, no branch, NULL, and security scope.
- Inspect actual plans only after proving semantic correctness.
Interview Questions and Answers
What does OR do in SQL? It combines alternatives and is TRUE when at least one connected condition is TRUE.
What is FALSE OR FALSE? FALSE. What is TRUE OR UNKNOWN? TRUE. What is FALSE OR UNKNOWN? UNKNOWN.
Does OR mean exactly one condition? No. It allows one or several conditions to be true.
What is the difference between OR and IN? OR can connect different expressions; IN tests one expression against a set of values.
Which has higher precedence, AND or OR? AND generally does, so mixed expressions should use explicit parentheses.
How can OR cause a security defect? Incorrect grouping can let one branch bypass tenant, ownership, deleted, or role restrictions.
Does SQL guarantee OR short-circuiting? No. The optimizer may reorder or transform predicates.
Is OR always slow? No. Performance depends on selectivity, indexes, statistics, result volume, and optimizer behavior.
Is UNION always faster than OR? No, and UNION or UNION ALL can change duplicate behavior.
How should OR be tested? Test every branch independently, simultaneous matches, no match, NULL combinations, boundaries, authorization, and destructive scope.
Key Takeaway
The SQL OR operator expresses that at least one alternative is sufficient. It powers flexible searches, alternative eligibility, open-ended time rules, status choices, and exception detection. Because OR broadens access, every alternative must remain inside mandatory security and visibility boundaries.
RELIABLE OR = GENUINE ALTERNATIVES
+ EXPLICIT PARENTHESES
+ MANDATORY SECURITY SCOPE
+ DEFINED NULL BEHAVIOR
+ CLEAR AT-LEAST-ONE MEANING
+ SAFE PARAMETERIZATION
+ VERIFIED DUPLICATE SEMANTICS
+ COMPLETE BRANCH TESTS
Use OR to encode real choices, not to make unexpected rows appear. Group alternatives visibly, test every branch and unknown value, and optimize only after the result set is proven correct.