Logical Operators in SQL

Introduction

Logical operators combine, modify, or negate SQL conditions. A single comparison can find products above a price, but production questions usually require several facts at once: a product must be active and in stock, an order may be pending or paid, and every result must belong to the authenticated tenant. AND, OR, and NOT express those relationships.

SELECT product_id, product_name, price
FROM products
WHERE is_active = TRUE
  AND available_quantity > 0
  AND (category = 'BOOKS' OR category = 'COURSES');

The symbols are simple, but their behavior depends on NULL, operator precedence, grouping, data types, and query context. A misplaced OR can bypass tenant security, an unsafe NOT IN can return no rows, and an assumption about left-to-right evaluation can cause errors. Reliable SQL treats logical expressions as executable business rules.

Main SQL Logical Operators

OperatorPurposeGeneral effect
ANDRequires all connected conditionsNarrows the qualifying set
ORAllows one or more connected conditionsBroadens the qualifying set
NOTNegates a conditionSelects its logical opposite, subject to NULL

These effects are conceptual rather than guaranteed performance rules. The optimizer may transform equivalent expressions and choose different physical plans. The first requirement is correct semantics; execution tuning follows after the intended truth set is established.

Understanding AND

AND evaluates to TRUE only when every connected condition is TRUE. It is appropriate when each rule is mandatory. A report of completed high-value orders for one tenant needs all three conditions.

SELECT order_id, customer_id, total_amount
FROM orders
WHERE tenant_id = :tenant_id
  AND status = 'COMPLETED'
  AND total_amount >= 1000.00;

Adding an AND condition normally narrows or preserves the result because fewer rows can satisfy more requirements. However, redundant conditions can add noise, contradictory conditions can make the set empty, and nullable expressions can evaluate to UNKNOWN rather than TRUE or FALSE.

AND Truth Table

ABA AND B
TRUETRUETRUE
TRUEFALSEFALSE
FALSETRUEFALSE
FALSEFALSEFALSE

SQL also has UNKNOWN, discussed later. With ordinary known booleans, one FALSE condition is enough to make the whole AND expression FALSE. This makes AND natural for validation and authorization gates in which every requirement must hold.

AND with Ranges and Dates

A bounded range commonly uses two comparisons joined by AND. For timestamps, a half-open range includes its start and excludes the next boundary, avoiding fractional-second gaps and overlaps.

SELECT event_id, created_at
FROM events
WHERE created_at >= :day_start
  AND created_at <  :next_day_start;

The same pattern represents age bands, price windows, and effective-dated records. Clarify whether each edge is inclusive. BETWEEN includes both ends, which can be suitable for exact numeric ranges but is often less convenient for adjacent timestamp periods.

AND for Authorization and Tenancy

Security conditions must remain mandatory. Identifying a record does not prove the caller may access it. Scope reads and mutations with authenticated ownership and tenant information.

SELECT document_id, title, body
FROM documents
WHERE document_id = :document_id
  AND tenant_id = :authenticated_tenant_id
  AND owner_id = :authenticated_user_id
  AND deleted_at IS NULL;

Derive tenant and owner values from trusted identity, not request fields. The same mandatory scope belongs in UPDATE and DELETE. A missing security predicate is an object-level authorization defect even if the SQL is parameterized.

AND in Conditional Updates

AND makes state changes conditional on current facts. An inventory reservation should identify the product and require enough available quantity in the same atomic statement.

UPDATE products
SET available_quantity = available_quantity - :requested_quantity
WHERE product_id = :product_id
  AND is_active = TRUE
  AND available_quantity >= :requested_quantity;

Require exactly one affected row. Zero can mean the product is absent, inactive, or understocked. This pattern avoids a race between a separate availability SELECT and an unconditional UPDATE.

Understanding OR

OR evaluates to TRUE when at least one connected condition is TRUE. It expresses alternatives: customers can match an email or phone, an order may be paid or shipped, and a session may be expired or revoked.

SELECT customer_id, display_name
FROM customers
WHERE email = :search_value
   OR phone_number = :search_value;

Adding an OR branch usually broadens or preserves the result. That makes OR useful for search and alternative eligibility, but dangerous when it accidentally escapes a mandatory authorization, tenant, or soft-delete filter.

OR Truth Table

ABA OR B
TRUETRUETRUE
TRUEFALSETRUE
FALSETRUETRUE
FALSEFALSEFALSE

With known values, only two FALSE conditions produce FALSE. In business language, OR should correspond to explicit alternatives. If every rule must be true, OR is the wrong connector even if it produces more expected sample rows.

OR versus IN

When one expression is compared with several exact values, IN is generally clearer than repeating OR.

-- Repeated OR.
WHERE status = 'PAID'
   OR status = 'SHIPPED'
   OR status = 'DELIVERED'

-- Clearer equivalent for known non-NULL values.
WHERE status IN ('PAID', 'SHIPPED', 'DELIVERED')

OR remains appropriate when alternatives involve different columns, operators, or grouped rules. IN cannot replace email = :value OR phone = :value because the left-hand expressions differ. NULL semantics still require care with IN and especially NOT IN.

OR with Different Business Alternatives

Complex OR logic often contains complete groups rather than isolated comparisons. A transaction might require review when it is either a large international payment or a smaller payment from a blocked device.

WHERE (amount >= 10000.00 AND country_code <> :home_country)
   OR (amount >= 1000.00 AND device_risk = 'BLOCKED')

Grouping each alternative makes the policy visible and testable. Flattening all four conditions with OR would implement a different and much broader rule.

Understanding NOT

NOT negates a condition: TRUE becomes FALSE, FALSE becomes TRUE, and UNKNOWN remains UNKNOWN. It can negate equality, membership, ranges, patterns, NULL predicates, or existence checks.

WHERE NOT (status = 'CANCELLED')

WHERE status NOT IN ('CANCELLED', 'FAILED')

WHERE NOT EXISTS (
  SELECT 1
  FROM order_holds AS h
  WHERE h.order_id = o.order_id
)

A positive expression is often easier to understand than deeply nested NOT. Prefer naming allowed states directly when the list is stable and clearer than naming everything disallowed.

NOT Truth Table and NULL

ANOT A
TRUEFALSE
FALSETRUE
UNKNOWNUNKNOWN

This last row explains why negating a comparison does not automatically include NULL. If status is NULL, both status = 'ACTIVE' and NOT (status = 'ACTIVE') are UNKNOWN. WHERE retains only TRUE, so the row appears in neither result unless NULL is addressed explicitly.

SQL Three-Valued Logic

SQL conditions evaluate to TRUE, FALSE, or UNKNOWN. UNKNOWN usually arises from comparing missing information represented by NULL. WHERE, HAVING, and inner-join filters keep TRUE rows; FALSE and UNKNOWN are excluded.

ExpressionResult
TRUE AND UNKNOWNUNKNOWN
FALSE AND UNKNOWNFALSE
TRUE OR UNKNOWNTRUE
FALSE OR UNKNOWNUNKNOWN
NOT UNKNOWNUNKNOWN

One known FALSE is enough to settle AND; one known TRUE is enough to settle OR. Otherwise UNKNOWN remains unresolved. These rules must be considered wherever nullable columns participate in compound conditions.

Writing Explicit NULL Logic

Use IS NULL and IS NOT NULL rather than equality with NULL. If a business rule treats missing values as one alternative, say so explicitly.

SELECT employee_id, manager_id, employment_status
FROM employees
WHERE employment_status <> 'TERMINATED'
   OR employment_status IS NULL;

This query intentionally includes unknown status. Another policy may reject unknown status. NULL behavior is a domain choice, not a syntax patch. Prefer NOT NULL constraints when the value must always be known.

NOT IN and Nullable Subqueries

NOT IN is a frequent source of defects. If its list or subquery contains NULL, comparisons against that unknown value can make the whole predicate UNKNOWN and return no rows unexpectedly.

-- Risky when banned_customers.customer_id can contain NULL.
WHERE c.customer_id NOT IN (
  SELECT customer_id FROM banned_customers
)

-- Safer anti-existence form.
WHERE NOT EXISTS (
  SELECT 1
  FROM banned_customers AS b
  WHERE b.customer_id = c.customer_id
)

NOT EXISTS asks whether a matching related row exists and is usually the clearest anti-join expression. A NOT NULL constraint on the subquery key also makes intent stronger.

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), not (A OR B) AND C. Relying on memory makes reviews fragile.

-- Intended: active tenant records in either allowed status.
WHERE tenant_id = :tenant_id
  AND is_active = TRUE
  AND (status = 'PAID' OR status = 'SHIPPED')

Parentheses communicate the business grouping and protect it when conditions change. Use them around mixed AND and OR expressions even when default precedence would currently produce the same result.

The Security Risk of Misgrouped OR

Consider a query intended to return one tenant's public or owner-accessible documents.

-- Unsafe grouping: public documents from every tenant can qualify.
WHERE tenant_id = :tenant_id
  AND owner_id = :user_id
   OR visibility = 'PUBLIC'

-- Tenant scope is mandatory for both alternatives.
WHERE tenant_id = :tenant_id
  AND (owner_id = :user_id OR visibility = 'PUBLIC')

The first expression follows AND precedence and allows any public row, regardless of tenant. Similar mistakes can bypass deleted flags, regional restrictions, and account status. Security predicates should be visually separated from grouped alternatives and tested with cross-tenant data.

Logical Operators in WHERE and HAVING

WHERE applies conditions to rows before grouping. HAVING applies conditions to groups after aggregate calculation. Both support AND, OR, and NOT, but they operate at different logical stages.

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(total_amount) AS lifetime_value
FROM orders
WHERE status = 'COMPLETED'
  AND created_at >= :year_start
GROUP BY customer_id
HAVING COUNT(*) >= 5
   AND SUM(total_amount) >= 5000.00;

Do not move a row-level filter into HAVING merely to make a query compile. It can change results and make aggregation process unnecessary rows.

Logical Operators in JOIN Conditions

JOIN ON defines how rows match. Multiple key components commonly use AND. OR joins can be valid but often require careful modeling and performance review.

SELECT o.order_id, s.shipment_id
FROM orders AS o
LEFT JOIN shipments AS s
  ON s.order_id = o.order_id
 AND s.tenant_id = o.tenant_id
 AND s.cancelled_at IS NULL;

With a LEFT JOIN, moving s.cancelled_at IS NULL or another right-side condition to WHERE can change outer-join semantics. A WHERE condition that rejects NULL-extended rows can effectively turn the result into an inner join. Decide whether the condition controls matching or final filtering.

Logical Operators in CASE

CASE branches use logical conditions to classify values. Branches are tested in order and the first true condition wins, so order broad and narrow ranges carefully.

CASE
  WHEN amount >= 10000.00 AND country_code <> :home_country
    THEN 'HIGH_RISK'
  WHEN amount >= 10000.00 OR device_risk = 'ELEVATED'
    THEN 'REVIEW'
  WHEN NOT (status = 'APPROVED')
    THEN 'PENDING'
  ELSE 'STANDARD'
END

NULL conditions can evaluate to UNKNOWN and skip a branch. Add explicit NULL handling when unknown values require their own classification.

Logical Operators in CHECK Constraints

CHECK constraints can encode conditional invariants. An order delivered by shipment may require a tracking number, while pickup does not.

CHECK (
  delivery_method <> 'SHIPMENT'
  OR tracking_number IS NOT NULL
)

This is equivalent to saying that if delivery_method is SHIPMENT, tracking_number is required. CHECK generally accepts UNKNOWN, so combine logical rules with NOT NULL where missing data should not pass. Constraints protect every writer and close races that application-only validation cannot.

XOR and Exactly-One Rules

Standard SQL has no universal XOR keyword, but exclusive-or can be written as one condition or the other, but not both. For two optional destination fields, exactly one can be required with a pair of grouped alternatives.

CHECK (
     (email_address IS NOT NULL AND phone_number IS NULL)
  OR (email_address IS NULL AND phone_number IS NOT NULL)
)

For several fields, counting non-NULL expressions may be clearer if the database supports the necessary syntax. Always test all combinations rather than only the two valid ones.

Logical Operators in UPDATE and DELETE

Compound predicates determine which rows are changed. A mutation should combine identity, tenant, ownership, expected state, and concurrency conditions as required.

UPDATE orders
SET status = 'CANCELLED',
    cancelled_at = CURRENT_TIMESTAMP
WHERE order_id = :order_id
  AND tenant_id = :tenant_id
  AND customer_id = :customer_id
  AND status IN ('PENDING', 'PAID');

Check the affected count. Be especially cautious with OR in DELETE because one broad branch can select far more data than intended. Preview broad predicates with SELECT, verify counts, and use an appropriate transaction and recovery plan.

Temporal and Effective-Dated Logic

An active time window typically requires that the start has arrived and the end has not passed, while a NULL end can represent no expiration.

WHERE valid_from <= :as_of_time
  AND (valid_to > :as_of_time OR valid_to IS NULL)

The outer AND requires both halves of the active-window rule. The inner OR allows an explicit future end or an open-ended record. Document inclusive and exclusive boundaries to prevent overlap between adjacent versions. The same pattern supports subscriptions, promotions, assignments, and policy history.

Workflow and State Logic

State changes should use current-state predicates rather than treating status as an unrestricted field. A shipment command may allow PAID or PACKED orders, require a verified address, and reject holds.

UPDATE orders
SET status = 'SHIPPED', shipped_at = CURRENT_TIMESTAMP
WHERE order_id = :order_id
  AND status IN ('PAID', 'PACKED')
  AND address_verified = TRUE
  AND hold_reason IS NULL;

One affected row means the transition won its concurrency race. Zero means the current facts no longer satisfy the rule. Related history and side effects should be written in the same transaction where atomicity is required.

Optimistic Locking with Logical Conditions

Optimistic locking adds the version observed by the caller to the other mandatory predicates. This prevents a stale edit from silently replacing a newer change.

UPDATE profiles
SET display_name = :display_name,
    version_no = version_no + 1
WHERE profile_id = :profile_id
  AND tenant_id = :tenant_id
  AND version_no = :expected_version
  AND deleted_at IS NULL;

Every AND condition is part of success. Do not report zero affected rows as an ordinary successful update; distinguish conflict, missing record, and authorization according to the application's disclosure policy.

De Morgan's Laws

De Morgan's laws describe transformations of negated groups: NOT (A AND B) corresponds to (NOT A) OR (NOT B), while NOT (A OR B) corresponds to (NOT A) AND (NOT B). They can simplify logic or help explain an opposite policy.

NOT (status = 'ACTIVE' OR status = 'PENDING')

-- For known non-NULL values, conceptually equivalent to:
status <> 'ACTIVE' AND status <> 'PENDING'

NULL still produces UNKNOWN, so algebra learned from two-valued logic does not remove the need to test nullable inputs. Use a rewritten form only when it remains clearer and its NULL behavior matches the domain.

Translate Requirements into Logical Groups

Complex logic becomes safer when translated from structured business language. Identify mandatory global rules first, then group alternatives. Suppose a searchable product must belong to the tenant, be active, not deleted, and either match the category or have a matching tag.

WHERE p.tenant_id = :tenant_id
  AND p.is_active = TRUE
  AND p.deleted_at IS NULL
  AND (
        p.category_id = :category_id
        OR EXISTS (
          SELECT 1
          FROM product_tags AS pt
          WHERE pt.product_id = p.product_id
            AND pt.tag_id = :tag_id
        )
      )

Formatting the expression vertically reveals scope and makes review easier. During analysis, name groups such as "visible product" and "search match" before translating them into SQL.

Predicate Evaluation Order Is Not Guaranteed

SQL is declarative. The optimizer can reorder, combine, or transform predicates as long as the result remains logically equivalent. Do not depend on AND evaluating from left to right or use one condition as a procedural guard for an unsafe expression.

-- Unsafe assumption: denominator check may not run first.
WHERE denominator <> 0
  AND numerator / denominator > 10

-- Express safety in the calculation.
WHERE numerator / NULLIF(denominator, 0) > 10

Use CASE, NULLIF, validated data, constraints, or other expressions with defined safety. Short-circuit behavior observed in one plan is not a portable contract.

AND, OR, NOT, and Indexes

AND often increases selectivity. A composite index whose leading columns match tenant and status equality followed by a date range can support a common query. OR may require separate index probes, a bitmap operation, or a scan. NOT commonly matches a large part of a table and may be less selective.

These are tendencies, not rules. The optimizer considers data distribution, statistics, available indexes, expected result size, and ordering. Replacing OR with UNION can help in some cases but can also change duplicate behavior and increase work. Inspect actual execution plans before rewriting correct logic.

Sargability and Dynamic Filters

Direct comparisons on indexed columns are often searchable. Wrapping a column in functions, mixing types, or building optional-filter patterns with many OR branches can make access harder. A common dynamic pattern such as (:status IS NULL OR status = :status) is convenient but can produce unstable plans on large tables.

For high-volume search, consider generating SQL from a strict server-side allowlist of filters while keeping all values parameterized. Do not allow users to submit arbitrary logical fragments, column names, or operators. Parameters protect values; the application must control SQL structure.

Logical Operators and SQL Injection

The classic injection payload adds an always-true OR expression to bypass a filter. It succeeds only when untrusted text is concatenated into SQL. Use parameterized statements so input remains data and cannot become an operator or clause.

SELECT user_id, password_hash
FROM users
WHERE username = :username
  AND account_status = 'ACTIVE';

Parameterization does not replace authorization, password hashing, least privilege, or secure error handling. It ensures that a supplied username cannot alter the logical structure. Dynamic identifiers and sort directions must come from allowlists because ordinary value parameters do not represent SQL syntax.

Soft Delete and Alternative Active States

Soft-deleted rows require a mandatory visibility predicate in ordinary queries. If several states count as visible, group them without allowing the OR to escape the delete or tenant scope.

WHERE tenant_id = :tenant_id
  AND deleted_at IS NULL
  AND (status = 'ACTIVE' OR status = 'TRIAL')

Using IN is clearer here: status IN ('ACTIVE', 'TRIAL'). Centralize visibility rules in repository methods, views, or row-level security where appropriate, but still test that privileged and ordinary paths produce intended sets.

Common Logical Mistakes

  • Mixing AND and OR without parentheses that show business grouping.
  • Assuming predicates execute physically from left to right.
  • Ignoring UNKNOWN when nullable columns are compared.
  • Using NOT IN with a nullable list or subquery.
  • Repeating many OR equalities instead of a clear IN list.
  • Using IN when alternatives compare different columns.
  • Allowing OR to escape tenant, ownership, deleted, or active filters.
  • Writing an always-true OR or contradictory AND accidentally.
  • Building logical SQL by concatenating untrusted request text.
  • Assuming a CHECK condition that evaluates UNKNOWN rejects the row.
  • Moving right-table conditions from JOIN ON to WHERE without considering outer-join semantics.
  • Optimizing an expression before proving that it returns the correct truth set.

Testing Logical Expressions

Test every meaningful truth combination, not only one successful row. For A AND B, cover true/true, true/false, false/true, false/false, and nullable variants. For OR, prove that each alternative succeeds independently and that neither succeeds when both are false. For NOT, test known true, known false, and NULL.

Boundary values belong in the same matrix. For amount >= 100 AND amount < 500, test 99.99, 100.00, 100.01, 499.99, 500.00, 500.01, and NULL at the column's actual precision. For security logic, include records from another tenant, another owner, deleted records, and states matching each OR branch.

Testing Parentheses and Security Scope

Create data that distinguishes competing interpretations. If a tenant query allows active or trial status, include a trial record in another tenant. The correctly grouped query must reject it. Include deleted active rows to prove that the visibility predicate applies to both status alternatives.

Mutation tests should assert affected-row counts and final state under concurrent changes. A condition can be syntactically correct yet too broad. Run SELECT with the same predicate before high-risk maintenance, verify representative targets, and define an expected maximum count.

Reading and Reviewing Complex Conditions

Format one mandatory condition per line and indent grouped alternatives. Give derived tables or common table expressions names that represent domain concepts. Remove redundant conditions only after proving equivalence with NULL behavior included.

During review, ask which filters are global, which rules are alternatives, which values can be NULL, and whether any OR branch bypasses authorization. Then ask whether the predicate remains safe in UPDATE or DELETE, whether types match, and whether the plan is appropriate for expected volume. Readability is a correctness mechanism.

Practical Production Examples

-- Overdue invoice that still has money due.
WHERE due_date < CURRENT_DATE
  AND balance_due > 0
  AND status NOT IN ('PAID', 'VOID')

-- Available product visible in the catalog.
WHERE is_active = TRUE
  AND deleted_at IS NULL
  AND (available_quantity > 0 OR backorder_allowed = TRUE)

-- Expired or administratively revoked sessions.
WHERE expires_at <= CURRENT_TIMESTAMP
   OR revoked_at IS NOT NULL

-- Customer with no completed orders.
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'COMPLETED'
)

Each example reflects a business decision about states and NULL. Production use also requires tenant scope, suitable indexes, type-correct parameters, and explicit result or affected-row expectations.

Logical Operator Review Checklist

  1. State the business rule in plain language before writing SQL.
  2. Separate mandatory global conditions from alternative groups.
  3. Use parentheses whenever AND and OR appear together.
  4. Define how every nullable expression should behave.
  5. Prefer IN for several exact values of one expression.
  6. Prefer NOT EXISTS for relationship absence and nullable anti-joins.
  7. Keep tenant, ownership, and visibility filters mandatory.
  8. Never depend on left-to-right or short-circuit predicate evaluation.
  9. Parameterize values and allowlist dynamic SQL structure.
  10. Check outer-join semantics before moving ON conditions to WHERE.
  11. Test every truth combination, boundary, NULL, and security scope.
  12. Inspect plans only after semantic correctness is established.

Interview Questions and Answers

What do SQL logical operators do? They combine or negate conditions to form a larger predicate.

When is AND true? When every connected condition is TRUE.

When is OR true? When at least one connected condition is TRUE.

What does NOT do? It negates TRUE and FALSE, while UNKNOWN remains UNKNOWN.

Which has higher precedence, AND or OR? AND generally has higher precedence, but parentheses should make intended grouping explicit.

What is SQL three-valued logic? Conditions can be TRUE, FALSE, or UNKNOWN because of NULL.

What is TRUE AND UNKNOWN? UNKNOWN. What is FALSE AND UNKNOWN? FALSE.

What is TRUE OR UNKNOWN? TRUE. What is FALSE OR UNKNOWN? UNKNOWN.

Why can NOT IN be dangerous? A NULL in its set can make the predicate UNKNOWN and unexpectedly eliminate rows.

Does SQL guarantee left-to-right AND evaluation? No. The optimizer can reorder predicates.

How can OR cause a security defect? Incorrect grouping can let an alternative bypass tenant, ownership, or visibility filters.

Can logical operators be used outside WHERE? Yes, including JOIN ON, HAVING, CASE, CHECK, UPDATE, DELETE, and other predicates.

Key Takeaway

Logical operators turn individual comparisons into executable policy. AND expresses mandatory facts, OR expresses alternatives, and NOT expresses negation, but NULL introduces UNKNOWN and precedence determines grouping. Correct logic must preserve security scope, mutation boundaries, temporal rules, and concurrency expectations.

RELIABLE LOGIC = CLEAR BUSINESS RULE
                 + MANDATORY AND CONDITIONS
                 + GROUPED OR ALTERNATIVES
                 + EXPLICIT NOT INTENT
                 + DEFINED NULL BEHAVIOR
                 + VISIBLE PARENTHESES
                 + TRUSTED SECURITY SCOPE
                 + COMPLETE TRUTH-TABLE TESTS

Write complex conditions vertically, test every branch and unknown value, and optimize only after proving that the expression returns exactly the rows the business rule allows.