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 ACondition BA OR B
TRUETRUETRUE
TRUEFALSETRUE
FALSETRUETRUE
FALSEFALSEFALSE

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

ABA OR B
TRUEUNKNOWNTRUE
FALSEUNKNOWNUNKNOWN
UNKNOWNUNKNOWNUNKNOWN

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

  1. Confirm that each branch is a genuine alternative path.
  2. Use IN for repeated equality against one expression.
  3. Parenthesize every OR group combined with mandatory AND filters.
  4. Keep tenant, ownership, visibility, and delete scope outside alternatives.
  5. Define whether NULL should qualify and write that behavior explicitly.
  6. Distinguish at-least-one OR from exactly-one XOR requirements.
  7. Never rely on written predicate order for runtime safety.
  8. Parameterize values and allowlist dynamic SQL structure.
  9. Preview and count UPDATE or DELETE targets.
  10. Verify JOIN and UNION duplicate semantics.
  11. Test each branch, all branches, no branch, NULL, and security scope.
  12. 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.