AND Operator in SQL
Introduction
The AND operator combines SQL conditions when every condition must be satisfied. It turns several individual comparisons into one mandatory rule. A product can be returned only when it belongs to the requested tenant, is active, has not been deleted, and has sufficient stock. If any required condition is not TRUE, the row does not qualify.
SELECT product_id, product_name, price
FROM products
WHERE tenant_id = :tenant_id
AND is_active = TRUE
AND deleted_at IS NULL
AND available_quantity > 0;
AND is used far beyond SELECT. It controls JOIN matches, aggregate filters, CASE branches, CHECK constraints, authorization, workflow transitions, inventory reservation, optimistic locking, and safe UPDATE or DELETE scope. Its production behavior also depends on NULL, operator precedence, data types, indexes, and concurrency.
Basic AND Syntax
The basic form places AND between two complete conditions. More conditions can be added by repeating the operator.
SELECT column_list
FROM table_name
WHERE condition_one
AND condition_two
AND condition_three;
Each condition must be meaningful on its own. AND is not punctuation between column names, and it is not a substitute for a JOIN or comma. Read the expression as a sentence: condition one must hold, and condition two must hold, and condition three must hold.
AND Truth Table
| Condition A | Condition B | A AND B |
|---|---|---|
| TRUE | TRUE | TRUE |
| TRUE | FALSE | FALSE |
| FALSE | TRUE | FALSE |
| FALSE | FALSE | FALSE |
Only the first row produces TRUE. With three conditions, all three must be TRUE. SQL adds UNKNOWN for nullable expressions, which is addressed later. WHERE retains only TRUE; both FALSE and UNKNOWN are filtered out.
How AND Narrows a Result Set
Every additional mandatory condition usually reduces or preserves the set of qualifying rows. Starting with all active customers and then requiring verified email produces a subset of active customers. Requiring a minimum lifetime value narrows it further.
SELECT customer_id, display_name
FROM customers
WHERE status = 'ACTIVE'
AND email_verified_at IS NOT NULL
AND lifetime_value >= 1000.00;
This conceptual narrowing is useful during debugging: execute a read-only query incrementally and observe which condition removes unexpected rows. However, adding conditions does not automatically make a query faster or more secure. The conditions must be correct, selective, indexable where appropriate, and impossible for alternative logic to bypass.
AND with Equality and Comparison Operators
AND commonly connects equality, inequality, and boundary comparisons. Each expression can use a different column and operator.
SELECT employee_id, employee_name, salary
FROM employees
WHERE department_code = 'IT'
AND employment_status = 'ACTIVE'
AND salary >= 80000.00
AND salary < 150000.00;
The result contains active IT employees whose salary lies in a half-open band. The lower boundary is included and the upper boundary is excluded. Write the intended boundaries in plain language before choosing >, >=, <, or <=.
AND for Numeric Ranges
Two comparisons joined by AND create a range. Half-open ranges are useful when adjacent bands must not overlap. The following price band includes 100.00 but excludes 500.00.
WHERE price >= 100.00
AND price < 500.00
BETWEEN is inclusive at both ends and can be suitable for closed ranges. Explicit comparisons make mixed inclusive and exclusive boundaries visible. Use exact numeric types such as DECIMAL for money and test values immediately below, at, and above each boundary.
AND for Date and Timestamp Ranges
A full calendar day stored in a timestamp column should usually be queried as a half-open range. Equality with midnight matches only one instant, and a guessed end such as 23:59:59 can miss fractional seconds.
SELECT order_id, created_at, total_amount
FROM orders
WHERE created_at >= :day_start
AND created_at < :next_day_start;
Calculate boundaries in the required business time zone and compare against the database storage convention. The same AND pattern supports weeks, months, reporting periods, and partition pruning when the partition key is compared directly.
AND with NULL Predicates
Use IS NULL and IS NOT NULL for missing values. A catalog query can require an active record and no deletion timestamp.
WHERE is_active = TRUE
AND deleted_at IS NULL
AND approved_at IS NOT NULL
This means all three facts are mandatory: active, not soft-deleted, and approved. Do not write deleted_at = NULL; ordinary comparisons with NULL evaluate to UNKNOWN. If a field must never be missing, a NOT NULL constraint is stronger than repeating an application filter.
Reading Multiple AND Conditions
Long expressions become safer when formatted one condition per line and arranged conceptually. Put tenant and ownership scope first, followed by identity, visibility, state, and business thresholds. This is a readability convention, not an execution order guarantee.
WHERE tenant_id = :tenant_id
AND account_id = :account_id
AND owner_id = :authenticated_user_id
AND deleted_at IS NULL
AND status = 'ACTIVE'
AND balance >= :minimum_balance
During review, read every line with "must also." If one condition is optional, it should not be represented as an unconditional AND.
AND with IN, BETWEEN, and LIKE
AND can combine specialized predicates. IN expresses allowed values of one expression, BETWEEN expresses an inclusive range, and LIKE performs pattern matching.
SELECT product_id, product_name, price
FROM products
WHERE tenant_id = :tenant_id
AND category_id IN (:category_one, :category_two)
AND price BETWEEN :minimum_price AND :maximum_price
AND product_name LIKE :name_pattern;
All three predicates must be TRUE. Pattern input should be parameterized and wildcards handled according to the intended search policy. BETWEEN includes both boundaries; use explicit comparisons when that is not the intended range.
AND with EXISTS and NOT EXISTS
EXISTS checks whether a related row satisfying its subquery conditions exists. A customer can be selected only when active and associated with at least one completed order.
SELECT c.customer_id, c.display_name
FROM customers AS c
WHERE c.status = 'ACTIVE'
AND EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.status = 'COMPLETED'
);
NOT EXISTS expresses absence and is safer than NOT IN when the subquery key can contain NULL. Correlation conditions inside the subquery also use AND when each relationship and state requirement is mandatory.
AND in Real Business Rules
A mature predicate mirrors business eligibility rather than merely combining convenient columns. Premium membership might require an active account, verified identity, qualifying annual spend, and no compliance hold. Each fact needs a reliable source and defined NULL behavior.
Do not hide policy ambiguity inside SQL. Clarify whether spend exactly at the threshold qualifies, whether a missing verification date means unverified, and whether temporary holds disqualify the account. Translate those answers into explicit conditions and boundary tests.
AND for Multi-Tenant Security
Tenant scope is a mandatory condition for every tenant-owned read and mutation unless row-level security or another proven mechanism enforces it. An identifier alone may not be globally unique and never proves authorization.
SELECT invoice_id, invoice_number, balance_due
FROM invoices
WHERE tenant_id = :authenticated_tenant_id
AND invoice_id = :invoice_id
AND customer_id = :authorized_customer_id;
Use tenant and ownership values from authenticated server context, not client-submitted fields. Test with records having the same local identifier across different tenants where the model permits it.
AND for Authorization
Authorization often requires several facts: the caller belongs to the organization, owns the object or has an allowed role, the object is visible, and the action is valid in the current state. AND expresses mandatory gates but does not replace a complete authorization design.
Parameterization prevents values from becoming SQL syntax; it does not establish permission. Least-privilege database credentials, service-level policy, audit records, and consistent predicates remain necessary. A query can be injection-safe while exposing another user's row.
AND with OR Requires Parentheses
AND normally has higher precedence than OR. Parentheses should still show the business grouping whenever both operators appear. A tenant-scoped search for active or trial accounts needs the tenant condition to apply to both alternatives.
WHERE tenant_id = :tenant_id
AND deleted_at IS NULL
AND (status = 'ACTIVE' OR status = 'TRIAL')
Without parentheses, an OR branch can escape tenant or soft-delete scope. Using status IN ('ACTIVE', 'TRIAL') is clearer when the alternatives compare one expression with exact values.
A Dangerous Missing-Parentheses Example
-- Unsafe: every public document can qualify.
WHERE tenant_id = :tenant_id
AND owner_id = :user_id
OR visibility = 'PUBLIC'
-- Correct: tenant scope remains mandatory.
WHERE tenant_id = :tenant_id
AND (owner_id = :user_id OR visibility = 'PUBLIC')
The unsafe expression follows AND precedence as (tenant AND owner) OR public. A public document from another tenant therefore passes. Security testing must include records that match only the OR branch outside the authorized tenant.
AND with NOT
NOT can negate one mandatory condition while AND connects it to others. Positive forms are often clearer, but NOT EXISTS and NOT NULL predicates are important.
WHERE tenant_id = :tenant_id
AND status NOT IN ('CANCELLED', 'FAILED')
AND NOT EXISTS (
SELECT 1
FROM account_holds AS h
WHERE h.account_id = a.account_id
AND h.released_at IS NULL
)
Remember that NOT IN can behave unexpectedly when its list or subquery contains NULL. For anti-relationships, NOT EXISTS communicates the intent directly.
AND in UPDATE Statements
In UPDATE, every AND condition becomes part of the eligibility rule for changing a row. Combine stable identity, tenant, ownership, current state, and visibility where 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 = 'PENDING'
AND cancelled_at IS NULL;
Require exactly one affected row. Zero means at least one mandatory fact was not true; it should not automatically be reported as a successful cancellation.
AND as a Business-State Guard
Conditional updates turn current state into an atomic guard. A separate SELECT followed by an unconditional UPDATE creates a race because state may change between statements. Putting the expected state in the mutation predicate lets the database decide against the current row.
This pattern supports approval, cancellation, shipment, job claiming, and seat reservation. If several related changes must occur together, use a transaction and preserve the same state assumptions across the workflow.
Atomic Inventory Control
Inventory reservation should require the correct product, active availability, and sufficient quantity in one statement.
UPDATE products
SET available_quantity = available_quantity - :quantity
WHERE product_id = :product_id
AND is_active = TRUE
AND available_quantity >= :quantity;
The arithmetic uses the current locked row rather than an old value read by the application. A CHECK constraint such as available_quantity >= 0 provides final integrity protection. The affected-row count tells the service whether the reservation succeeded.
AND in Optimistic Locking
Optimistic locking adds the version originally read by the caller to the update predicate. The row changes only when its identity and expected version both match.
UPDATE documents
SET body = :body,
version_no = version_no + 1,
updated_at = CURRENT_TIMESTAMP
WHERE document_id = :document_id
AND tenant_id = :tenant_id
AND version_no = :expected_version
AND deleted_at IS NULL;
Zero affected rows can indicate a stale edit, missing object, or visibility mismatch. The service can return a conflict without silently overwriting another user's work.
AND in DELETE Statements
DELETE predicates need the same precision as UPDATE. A cart-item deletion should identify both the item and the authenticated cart so an ID from another cart cannot be removed.
DELETE FROM cart_items
WHERE cart_item_id = :cart_item_id
AND cart_id = :authenticated_cart_id
AND tenant_id = :tenant_id;
Preview broad maintenance predicates with SELECT, count expected rows, and understand foreign-key effects. More AND conditions are useful only when they represent real safety boundaries; redundant conditions do not compensate for a missing tenant or ownership predicate.
AND in JOIN Conditions
Composite relationships and tenant-safe joins require several matching keys. Joining only a local ID can connect rows across tenants.
SELECT o.order_id, s.shipment_id, s.tracking_number
FROM orders AS o
JOIN shipments AS s
ON s.tenant_id = o.tenant_id
AND s.order_id = o.order_id
AND s.cancelled_at IS NULL
WHERE o.tenant_id = :tenant_id;
AND in ON defines which rows match. It can include relationship keys and state filters. Ensure suitable constraints support the intended relationship so the join cannot create unexpected duplicates.
AND with LEFT JOIN: ON versus WHERE
For an outer join, placing a right-table condition in ON preserves unmatched left rows, while placing a rejecting condition in WHERE can remove them.
SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.status = 'OPEN'
WHERE c.tenant_id = :tenant_id
AND c.status = 'ACTIVE';
This returns active customers even when no open order exists. Moving o.status = 'OPEN' to WHERE would reject NULL-extended rows and effectively require a matching order. Decide whether a condition controls matching or final filtering.
AND in HAVING and CASE
HAVING applies conditions to groups after aggregation. AND can require both minimum activity and minimum value.
SELECT customer_id, COUNT(*) AS order_count, SUM(total_amount) AS total_value
FROM orders
WHERE status = 'COMPLETED'
GROUP BY customer_id
HAVING COUNT(*) >= 5
AND SUM(total_amount) >= 5000.00;
CASE can use AND in each branch. Branches are checked in order, so place narrower classifications before broad ones. Nullable conditions can become UNKNOWN and skip a branch unless handled explicitly.
AND in CHECK Constraints
A CHECK constraint can require several facts together or reject an invalid cross-column combination.
CHECK (
start_date IS NOT NULL
AND end_date IS NOT NULL
AND end_date > start_date
)
If dates are mandatory, separate NOT NULL constraints are often clearer, with CHECK focused on their relationship. CHECK behavior with UNKNOWN varies by intent: commonly UNKNOWN is accepted, so nullable fields may bypass a comparison unless nullability is constrained.
AND and SQL Three-Valued Logic
SQL conditions can be TRUE, FALSE, or UNKNOWN. UNKNOWN usually results from ordinary comparison with NULL.
| A | B | A AND B |
|---|---|---|
| TRUE | UNKNOWN | UNKNOWN |
| FALSE | UNKNOWN | FALSE |
| UNKNOWN | UNKNOWN | UNKNOWN |
A known FALSE is enough to settle the AND expression as FALSE. When the other condition is TRUE, the unknown fact remains unresolved. WHERE keeps only TRUE, so both FALSE and UNKNOWN rows disappear.
Handling Nullable Conditions Explicitly
Suppose a report requires active employees whose termination date is missing. Use termination_date IS NULL, not a comparison to NULL. If a score can be NULL and missing scores should qualify for review, write that alternative in a grouped expression.
WHERE employment_status = 'ACTIVE'
AND (review_score < :minimum_score OR review_score IS NULL)
The inner OR defines how unknown scores are treated; the outer AND keeps active status mandatory. Without explicit NULL handling, missing scores produce UNKNOWN and are excluded.
AND Does Not Guarantee Evaluation Order
SQL is declarative, so the optimizer may reorder or transform conditions. Do not rely on the first condition protecting a later unsafe expression.
-- Do not assume denominator is checked first.
WHERE denominator <> 0
AND numerator / denominator > 10
-- Make the expression itself safe.
WHERE numerator / NULLIF(denominator, 0) > 10
Observed short-circuit behavior in one plan is not a portable guarantee. Use constraints, NULLIF, CASE where appropriate, or validated expressions with defined behavior.
AND and Query Optimization
The optimizer can evaluate predicates in any efficient order while preserving logical results. It may use one selective condition to locate rows, apply others as residual filters, or combine indexes. Written order is primarily for human clarity.
More conditions do not always mean less work. A function on a large column, implicit conversion, low-selectivity status, or correlated subquery may add cost. Measure important queries with actual execution plans and production-like data distribution.
AND with Composite Indexes
A common query with tenant equality, status equality, and a created-at range may benefit from an index on (tenant_id, status, created_at). The leading equality columns narrow the ordered index before the range.
WHERE tenant_id = :tenant_id
AND status = 'PAID'
AND created_at >= :start_time
AND created_at < :end_time
Index order should follow real query patterns, selectivity, sorting, and write cost. Creating one index per condition is not automatically useful, and every additional index slows data changes.
Sargable AND Predicates
Direct type-compatible comparisons on indexed columns are often searchable. Wrapping the column in a function can prevent an index range seek.
-- Usually less searchable.
WHERE tenant_id = :tenant_id
AND CAST(created_at AS DATE) = :target_date
-- Usually searchable as a range.
WHERE tenant_id = :tenant_id
AND created_at >= :day_start
AND created_at < :next_day_start
Bind parameters using the actual column types. Comparing numeric IDs with text parameters can introduce conversion errors or poor access paths.
AND in Search APIs
Search endpoints often combine mandatory visibility with optional filters. Build SQL from a server-controlled allowlist while binding all values as parameters. Keep tenant and authorization conditions outside optional branches so they cannot disappear.
WHERE tenant_id = :tenant_id
AND deleted_at IS NULL
AND (:category_id IS NULL OR category_id = :category_id)
AND (:minimum_price IS NULL OR price >= :minimum_price)
This convenient static pattern can lead to parameter-sensitive plans at scale. For critical searches, selectively generating only supplied predicates may perform better, provided SQL structure remains controlled and values remain parameterized.
AND and SQL Injection
Concatenating request text into a WHERE clause allows an attacker to add logical operators and change the predicate. Parameterized statements keep input as data.
SELECT user_id, password_hash
FROM users
WHERE username = :username
AND account_status = 'ACTIVE';
Parameters do not represent dynamic column names or operators. Map those choices through strict server-side allowlists. Parameterization is essential, but authorization and least privilege must still be enforced independently.
AND for Idempotency and Business Invariants
AND can make a command apply only while its intended effect has not already occurred. A payment-capture worker, for example, can require the correct payment ID, an authorized state, a matching amount, and no prior capture timestamp. These conditions reduce duplicate effects when messages or client requests are delivered more than once.
UPDATE payments
SET status = 'CAPTURED',
captured_at = CURRENT_TIMESTAMP
WHERE payment_id = :payment_id
AND status = 'AUTHORIZED'
AND amount = :expected_amount
AND captured_at IS NULL;
Zero affected rows does not by itself distinguish an already captured payment from a stale amount, missing record, or invalid state. The service must read or otherwise interpret current state according to its disclosure and retry policy. A unique idempotency key can provide stronger request-level deduplication when the operation creates new records or spans several tables.
Durable invariants should not depend only on one carefully written statement. UNIQUE, FOREIGN KEY, CHECK, and NOT NULL constraints remain the final protection across every application and concurrent session. AND describes the conditions under which this command may proceed; constraints define facts that the database must never allow any command to violate.
Contradictory and Redundant AND Conditions
An impossible range such as price > 100 AND price < 50 can never be TRUE. Likewise, status = 'ACTIVE' AND status = 'INACTIVE' is contradictory for one scalar column. These errors often arise when dynamic filters are combined without validation.
Redundant conditions such as price > 10 AND price > 20 simplify to price > 20 for known numeric values. Remove redundancy when it improves clarity, but verify NULL and type behavior before algebraic rewrites.
AND Does Not Remove Duplicate Rows
AND filters rows; it does not control join cardinality. If one customer joins to five orders, five result rows can remain even when many AND predicates are applied. DISTINCT can hide duplicates but may also hide an incorrect result grain.
Define whether the result represents customers, orders, or order items. Use EXISTS when only relationship existence matters, aggregation when one row per group is intended, and correct join keys when duplication is accidental.
Real-World Production Examples
-- Active and verified customer.
WHERE status = 'ACTIVE'
AND email_verified_at IS NOT NULL
AND deleted_at IS NULL
-- Paid orders created this month.
WHERE status = 'PAID'
AND created_at >= :month_start
AND created_at < :next_month_start
-- Valid discount at checkout.
WHERE discount_code = :code
AND starts_at <= CURRENT_TIMESTAMP
AND expires_at > CURRENT_TIMESTAMP
AND remaining_uses > 0
-- Available seat owned by the event.
WHERE seat_id = :seat_id
AND event_id = :event_id
AND reservation_id IS NULL
Each expression should also include tenant or authorization scope where the schema requires it. Examples become production-safe only when their identity, boundary, NULL, and concurrency assumptions are explicit.
Testing AND Conditions
Test the all-true path, then make each individual condition false while keeping the others true. For three conditions, verify that A alone is false, B alone is false, C alone is false, every pair without the third is false, and all three together are true. Add NULL cases for every nullable expression.
Boundary tests should cover immediately below, exactly at, and immediately above each threshold. For amount >= 100 AND amount < 500, test 99.99, 100.00, 100.01, 499.99, 500.00, 500.01, and NULL at the stored precision.
Security and Concurrency Testing
Create rows that match every business condition except tenant or ownership and prove they remain inaccessible. Add an OR alternative and confirm it cannot escape mandatory security scope. Test active, deleted, and unauthorized versions of the same logical entity.
For conditional mutations, run concurrent sessions against the same starting state. Verify inventory never becomes negative, only one worker claims a job, and only one stale version update succeeds. Assert affected-row counts and final state, not merely the absence of an exception.
Common AND Mistakes
- Assuming NULL behaves exactly like FALSE.
- Mixing AND with OR without visible parentheses.
- Allowing an OR branch to escape tenant or authorization scope.
- Assuming AND conditions execute left to right.
- Comparing incompatible types and relying on implicit conversion.
- Wrapping indexed columns in functions unnecessarily.
- Adding many conditions without validating contradictory filters.
- Believing more AND predicates automatically improve security or speed.
- Ignoring affected-row counts in UPDATE and DELETE.
- Using AND predicates to compensate for missing constraints or weak modeling.
- Moving a right-table condition from LEFT JOIN ON to WHERE unintentionally.
- Assuming filtering removes duplicate rows created by one-to-many joins.
AND Operator Review Checklist
- Confirm that every connected condition is genuinely mandatory.
- Use type-correct parameters and explicit boundary operators.
- Define the intended behavior of every nullable column.
- Keep tenant, ownership, visibility, and current-state guards mandatory.
- Parenthesize every mixed AND and OR expression by business meaning.
- Never depend on written predicate order for runtime safety.
- Check affected rows for conditional mutations.
- Use constraints to protect durable invariants across all writers.
- Review JOIN cardinality and ON-versus-WHERE placement.
- Keep indexed columns searchable and avoid implicit conversion.
- Test all-true, each-false, NULL, boundary, security, and concurrency cases.
- Inspect execution plans only after semantic correctness is proven.
Interview Questions and Answers
What does AND do in SQL? It combines conditions and returns TRUE only when every connected condition is TRUE.
What is TRUE AND FALSE? FALSE. What is TRUE AND UNKNOWN? UNKNOWN. What is FALSE AND UNKNOWN? FALSE.
Can AND combine more than two conditions? Yes, and every condition must be TRUE for the complete expression to be TRUE.
Does SQL evaluate AND left to right? No guarantee exists; the optimizer may reorder or transform predicates.
Which has higher precedence, AND or OR? AND generally does, but parentheses should make intended grouping explicit.
Why use AND with a version number? To make an update succeed only when the row still has the version originally observed.
How does AND help authorization? It keeps identity, tenant, ownership, role, visibility, and state requirements mandatory in one predicate.
Can AND be used in JOIN and HAVING? Yes. It combines join requirements in ON and aggregate requirements in HAVING.
Why can more AND conditions make a query slower? Conditions can add expensive calculations or subqueries and may not be selective or indexable.
How should AND be tested? Test all conditions true, each condition false independently, NULL cases, boundaries, authorization scope, and concurrent mutation outcomes.
Key Takeaway
The SQL AND operator expresses that every connected fact is mandatory. It is the foundation of precise filters, secure tenant scope, valid time windows, atomic state changes, inventory protection, optimistic locking, and cross-column integrity. Its correctness depends on explicit boundaries, NULL behavior, parentheses, trusted parameters, and complete tests.
RELIABLE AND = ALL CONDITIONS REQUIRED
+ EXPLICIT BOUNDARIES
+ DEFINED NULL BEHAVIOR
+ MANDATORY SECURITY SCOPE
+ VISIBLE OR GROUPING
+ ATOMIC STATE GUARDS
+ VERIFIED AFFECTED ROWS
+ COMPLETE COMBINATION TESTS
Use AND to encode genuine requirements, not as decoration. Read each condition as "must also be true," verify its source and type, and confirm that the complete expression remains correct under missing data, unauthorized records, and concurrent changes.