Logical Operators - Learning Module

Loading content...

0/241

AND Operator

The Power of Combining Conditions

When querying databases, single conditions are rarely sufficient. Real-world data retrieval almost always requires multiple conditions that must all be true simultaneously. A human resources system doesn't just find employees—it finds active employees in the engineering department hired after 2020 with a salary above $80,000. An e-commerce platform doesn't just list products—it finds in-stock products in the electronics category priced between $100 and $500 with ratings above 4 stars.

The AND operator is the fundamental building block for expressing this kind of conjunctive logic in SQL. It allows you to specify that every condition in your query must evaluate to TRUE for a row to be included in the result set.

Understanding AND thoroughly—including its truth table semantics, behavior with NULL values, and performance characteristics—is essential for writing precise, efficient queries.

What You Will Learn

By the end of this page, you will master the AND operator completely: its formal semantics derived from Boolean algebra, its behavior in SQL's three-valued logic with NULLs, its practical applications for multi-condition filtering, and optimization strategies for AND-heavy queries.

The AND Operator Fundamentals

The AND operator performs logical conjunction—a fundamental operation from Boolean algebra. In SQL, AND combines two or more conditions and returns TRUE only when all conditions are TRUE.

Formal Definition:

condition1 AND condition2 → TRUE if and only if:
  - condition1 evaluates to TRUE, AND
  - condition2 evaluates to TRUE

This definition extends naturally to any number of conditions:

condition1 AND condition2 AND condition3 AND ... AND conditionN → TRUE
if and only if ALL conditions evaluate to TRUE

basic_and_syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Basic AND Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2;
 
-- Example: Find employees in Engineering department earning over $80,000
SELECT employee_id, first_name, last_name, department, salary
FROM employees
WHERE department = 'Engineering'
  AND salary > 80000;
 
-- Multiple AND conditions
SELECT product_id, name, price, stock_quantity
FROM products
WHERE category = 'Electronics'
  AND price >= 100
  AND price <= 500
  AND stock_quantity > 0
  AND rating >= 4.0;

Formatting Convention

When chaining multiple AND conditions, place each condition on a new line with AND at the beginning. This improves readability and makes it easy to add, remove, or comment out individual conditions during development and debugging.

Truth Table Analysis

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

Binary Truth Table (Classical Logic):

In classical Boolean algebra with only TRUE and FALSE values:

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

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

This property has profound implications:

Each additional AND condition can only reduce (or maintain) the result set size
The more AND conditions you add, the fewer rows typically match
AND acts as a filter cascade, progressively narrowing results

Why Understanding Truth Tables Matters

Truth tables aren't just theoretical—they help you predict query behavior. If you have 1,000 rows, a single condition might match 200 rows. Adding an AND condition can only further reduce this (matching perhaps 50 rows). Understanding this helps you structure efficient queries and anticipate result sizes.

AND with Three-Valued Logic (NULL)

SQL extends classical two-valued Boolean logic with a third value: UNKNOWN, which arises when expressions involve NULL. This creates three-valued logic (3VL), which profoundly affects how AND behaves.

Why NULL Creates UNKNOWN:

NULL represents an unknown, missing, or inapplicable value. When you compare something to NULL, the result isn't TRUE or FALSE—it's UNKNOWN because we cannot determine the truth without knowing the actual value.

-- This expression evaluates to UNKNOWN, not TRUE or FALSE
NULL = 5     → UNKNOWN
NULL > 10    → UNKNOWN
NULL = NULL  → UNKNOWN

AND Truth Table with Three-Valued Logic
Condition A	Condition B	A AND B	Explanation
TRUE	TRUE	TRUE	Both known to be true
TRUE	UNKNOWN	UNKNOWN	Can't determine—depends on unknown value
TRUE	FALSE	FALSE	One false is sufficient
UNKNOWN	TRUE	UNKNOWN	Can't determine—depends on unknown value
UNKNOWN	UNKNOWN	UNKNOWN	Both values unknown
UNKNOWN	FALSE	FALSE	One false is sufficient
FALSE	TRUE	FALSE	One false is sufficient
FALSE	UNKNOWN	FALSE	One false is sufficient
FALSE	FALSE	FALSE	Both false

Critical Rule: FALSE AND UNKNOWN = FALSE

This makes logical sense: if we know one condition is FALSE, the conjunction must be FALSE regardless of the other value. AND requires all conditions to be TRUE, so a single FALSE is decisive.

But: TRUE AND UNKNOWN = UNKNOWN

This is trickier: we know one condition is TRUE, but the other is unknown. The conjunction might be TRUE (if the unknown value is TRUE) or FALSE (if it's FALSE). We simply can't tell.

and_with_null.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Table: employees
-- | emp_id | name    | department | commission |
-- |--------|---------|------------|------------|
-- | 1      | Alice   | Sales      | 5000       |
-- | 2      | Bob     | Sales      | NULL       |  -- Commission unknown
-- | 3      | Charlie | Engineering| NULL       |  -- No commission (N/A)
-- | 4      | Diana   | Sales      | 3000       |
 
-- Query: Find Sales employees with commission > 2000
SELECT emp_id, name, commission
FROM employees
WHERE department = 'Sales'
  AND commission > 2000;
 
-- Analysis:
-- Alice:   'Sales' = 'Sales' → TRUE  AND  5000 > 2000 → TRUE   = TRUE  ✓
-- Bob:     'Sales' = 'Sales' → TRUE  AND  NULL > 2000 → UNKNOWN = UNKNOWN (excluded)
-- Charlie: 'Engineering' = 'Sales' → FALSE AND ... = FALSE (excluded)
-- Diana:   'Sales' = 'Sales' → TRUE  AND  3000 > 2000 → TRUE   = TRUE  ✓
 
-- Result: Only Alice and Diana returned
-- Bob is EXCLUDED because TRUE AND UNKNOWN = UNKNOWN, not TRUE

NULL Exclusion in AND Queries

Rows with NULL values in columns used in AND conditions are typically excluded from results unless explicitly handled. This is a common source of bugs—developers expect rows with NULL to appear when they match other conditions, but UNKNOWN results in exclusion. Always consider: 'What if this column is NULL?'

Short-Circuit Evaluation

Many programming languages implement short-circuit evaluation for AND: if the first operand is FALSE, the second operand is never evaluated because the result is already determined.

Does SQL short-circuit AND?

The SQL standard does not mandate short-circuit evaluation, and behavior varies across database systems:

PostgreSQL, MySQL, SQL Server: May short-circuit, but the optimizer may also reorder conditions for efficiency
Oracle: Historically evaluated all conditions; modern versions may optimize

Why This Matters:

If you write a query assuming left-to-right short-circuit evaluation, you might be surprised when a database evaluates conditions in a different order—potentially causing errors or unexpected behavior.

short_circuit_pitfall.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- Dangerous assumption: relying on short-circuit evaluation
-- Suppose 'value' column sometimes contains non-numeric strings
 
-- WRONG: Assumes left-to-right short-circuit
SELECT *
FROM mixed_data
WHERE is_numeric = TRUE 
  AND CAST(value AS INTEGER) > 100;
 
-- If the database evaluates CAST() first (before checking is_numeric),
-- this query will ERROR on non-numeric values!
 
-- SAFER: Use CASE to guarantee evaluation order
SELECT *
FROM mixed_data
WHERE CASE 
        WHEN is_numeric = TRUE THEN CAST(value AS INTEGER) > 100
        ELSE FALSE
      END;
 
-- BEST: Separate with a CTE or subquery
WITH numeric_only AS (
    SELECT * FROM mixed_data WHERE is_numeric = TRUE
)
SELECT * FROM numeric_only
WHERE CAST(value AS INTEGER) > 100;

Never Rely on Condition Order

Database optimizers may reorder AND conditions for performance. A condition you placed 'first' for safety might be evaluated last. If one condition guards against errors in another, use CASE expressions or CTEs to guarantee evaluation order.

Practical Applications

AND enables sophisticated data filtering that mirrors real-world business requirements. Let's examine common patterns:

Range queries require both lower and upper bounds, naturally using AND:

range_filtering.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Price range
SELECT product_name, price
FROM products
WHERE price >= 50.00 AND price <= 200.00;
 
-- Equivalent using BETWEEN (which is AND under the hood)
SELECT product_name, price
FROM products
WHERE price BETWEEN 50.00 AND 200.00;
 
-- Date range with explicit AND
SELECT order_id, order_date, total
FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date < '2024-04-01';
 
-- Age range for eligibility
SELECT applicant_id, name, age
FROM applicants
WHERE age >= 21 AND age <= 65;

Performance Considerations

AND conditions directly impact query performance. Understanding how databases process AND helps you write efficient queries:

AND Performance Principles

•Index Utilization — If columns in AND conditions have indexes, the database can use them to quickly narrow results. Composite indexes covering multiple AND columns are especially efficient.
•Selectivity Order — Database optimizers often evaluate the most selective (restrictive) conditions first to minimize rows processed. A condition matching 1% of rows is evaluated before one matching 50%.
•Index Intersection — Some databases can use multiple single-column indexes, intersecting their results. This is less efficient than a composite index but better than full table scans.
•Range Scan Termination — For range conditions with AND (like BETWEEN), indexes enable efficient range scans that stop early once bounds are exceeded.

index_optimization.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Scenario: Frequent query pattern
SELECT * FROM orders
WHERE customer_id = 12345
  AND order_date >= '2024-01-01'
  AND status = 'Shipped';
 
-- Optimal: Create a composite index matching AND conditions
CREATE INDEX idx_orders_customer_date_status 
ON orders(customer_id, order_date, status);
 
-- This index enables efficient lookup:
-- 1. Seek directly to customer_id = 12345
-- 2. Within those, range scan order_date >= '2024-01-01'
-- 3. Filter by status = 'Shipped'
 
-- Index column order matters!
-- Place equality conditions (=) before range conditions (>=, <=, BETWEEN)
-- Because range conditions "consume" the remaining index

Index Design for AND Queries

When designing indexes for queries with multiple AND conditions, include columns in order of: (1) equality conditions first, (2) range conditions second, (3) most selective conditions earlier. This maximizes index efficiency by allowing the database to seek rather than scan.

Common Mistakes with AND

Even experienced developers make these AND-related errors:

Common Errors

•Forgetting NULL behavior — Expecting rows with NULL to match when they evaluate to UNKNOWN
•Impossible conditions — Writing WHERE status = 'Active' AND status = 'Pending' which can never be true
•Redundant conditions — Adding conditions already implied by others, cluttering the query
•Mixing AND/OR without parentheses — Getting unexpected results due to operator precedence

Best Practices

•Always consider NULL — Use IS NOT NULL when NULL values should exclude rows
•Verify logical consistency — Ensure conditions can actually all be true together
•Eliminate redundancy — Review conditions for overlap or implication
•Use parentheses — Make precedence explicit when mixing AND with OR

common_mistakes.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- MISTAKE 1: Impossible condition
SELECT * FROM products
WHERE category = 'Electronics' AND category = 'Clothing';
-- This returns NOTHING! A product can't be in two categories simultaneously.
 
-- MISTAKE 2: Forgetting NULL
SELECT * FROM employees
WHERE department = 'Sales' AND bonus > 0;
-- Employees with NULL bonus are excluded! If you want them, use:
SELECT * FROM employees
WHERE department = 'Sales' AND (bonus > 0 OR bonus IS NULL);
 
-- MISTAKE 3: Redundant conditions
SELECT * FROM products
WHERE price > 100 AND price > 50;
-- The second condition is redundant—if price > 100, it's already > 50
 
-- MISTAKE 4: Precedence confusion (covered in depth in later pages)
SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing'
  AND salary > 50000;
-- This is actually: Sales OR (Marketing AND salary > 50000)
-- NOT: (Sales OR Marketing) AND salary > 50000

Summary: Mastering AND

The AND operator is deceptively simple but forms the backbone of precise data querying. Let's consolidate the key insights:

Key Takeaways

•AND performs logical conjunction — Returns TRUE only when ALL conditions are TRUE. It's the most restrictive logical operator.
•Three-valued logic with NULL — Comparisons involving NULL yield UNKNOWN. TRUE AND UNKNOWN = UNKNOWN, which excludes the row from results.
•FALSE is decisive — FALSE AND anything = FALSE. This enables potential short-circuit optimization.
•Don't rely on evaluation order — Database optimizers may reorder conditions. Use CASE or CTEs when order matters for safety.
•Each AND condition narrows results — Adding AND conditions can only reduce or maintain result set size, never increase it.
•Composite indexes help AND queries — Design indexes with equality conditions first, then range conditions, ordered by selectivity.

What's Next:

The AND operator enables precise conjunction, but many queries require specifying alternative conditions—'either this OR that.' The next page explores the OR operator, which provides disjunction: matching rows that satisfy any of the specified conditions.

Page Complete

You now understand the AND operator comprehensively—from its Boolean algebra foundations through SQL's three-valued logic to practical performance optimization. Next, we'll explore OR, which opens up query possibilities by allowing alternative matching conditions.