SQL - AND, OR, NOT Operators | Application Architect

Key Insights

SQL logical operators (AND, OR, NOT) filter query results by combining multiple conditions, with AND requiring all conditions true, OR requiring at least one, and NOT inverting conditions
Operator precedence follows NOT > AND > OR, but explicit parentheses prevent logic errors and make complex queries maintainable
Performance optimization requires understanding index usage—compound conditions can leverage composite indexes while OR operations often trigger full table scans

Understanding Logical Operators in SQL

Logical operators form the backbone of conditional filtering in SQL queries. These operators—AND, OR, and NOT—allow you to construct complex WHERE clauses that precisely target the data you need. While conceptually simple, their practical application requires understanding operator precedence, performance implications, and common pitfalls.

Every logical operator evaluates to a boolean result: TRUE, FALSE, or NULL. The NULL case is particularly important in SQL since NULL represents unknown values, and any comparison with NULL produces NULL rather than TRUE or FALSE.

The AND Operator

The AND operator returns TRUE only when all conditions evaluate to TRUE. Use AND when records must satisfy multiple criteria simultaneously.

-- Find active premium customers in California
SELECT customer_id, name, state, account_status, tier
FROM customers
WHERE state = 'CA'
  AND account_status = 'active'
  AND tier = 'premium';

This query returns only customers meeting all three conditions. If any condition fails, the record is excluded.

AND operations with multiple conditions:

-- Complex filtering with multiple AND conditions
SELECT order_id, order_date, total_amount, customer_id
FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date < '2024-02-01'
  AND total_amount > 100.00
  AND total_amount < 5000.00
  AND shipping_status = 'delivered';

When working with NULL values, remember that NULL AND TRUE equals NULL, not FALSE:

-- This might not behave as expected
SELECT product_id, product_name, discontinued
FROM products
WHERE price > 50
  AND discontinued = FALSE;

-- Better: explicitly handle NULL
SELECT product_id, product_name, discontinued
FROM products
WHERE price > 50
  AND (discontinued = FALSE OR discontinued IS NULL);

The OR Operator

The OR operator returns TRUE when at least one condition evaluates to TRUE. Use OR to match records satisfying any of several criteria.

-- Find customers in multiple states
SELECT customer_id, name, state
FROM customers
WHERE state = 'CA'
   OR state = 'NY'
   OR state = 'TX';

-- More efficient alternative using IN
SELECT customer_id, name, state
FROM customers
WHERE state IN ('CA', 'NY', 'TX');

The IN operator is syntactic sugar for multiple OR conditions and typically performs better with indexes.

OR with different column conditions:

-- Find high-value customers or recent purchasers
SELECT c.customer_id, c.name, c.lifetime_value, o.last_order_date
FROM customers c
LEFT JOIN (
    SELECT customer_id, MAX(order_date) as last_order_date
    FROM orders
    GROUP BY customer_id
) o ON c.customer_id = o.customer_id
WHERE c.lifetime_value > 10000
   OR o.last_order_date > CURRENT_DATE - INTERVAL '30 days';

The NOT Operator

The NOT operator inverts a condition’s boolean value. TRUE becomes FALSE, FALSE becomes TRUE, and NULL remains NULL.

-- Find all products except discontinued ones
SELECT product_id, product_name, category
FROM products
WHERE NOT discontinued;

-- Equivalent to
SELECT product_id, product_name, category
FROM products
WHERE discontinued = FALSE;

NOT with IN and BETWEEN:

-- Exclude specific categories
SELECT product_id, product_name, category
FROM products
WHERE category NOT IN ('Electronics', 'Appliances');

-- Exclude a price range
SELECT product_id, product_name, price
FROM products
WHERE price NOT BETWEEN 50 AND 100;

NOT with subqueries to find non-matching records:

-- Find customers who never placed an order
SELECT customer_id, name, email
FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id 
    FROM orders
    WHERE customer_id IS NOT NULL
);

-- More efficient with NOT EXISTS
SELECT c.customer_id, c.name, c.email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id
);

Operator Precedence and Parentheses

SQL evaluates logical operators in this order: NOT first, then AND, then OR. Without parentheses, this precedence can produce unexpected results.

-- Ambiguous query - which interpretation is correct?
SELECT product_id, product_name, category, in_stock
FROM products
WHERE category = 'Electronics'
   OR category = 'Computers'
  AND in_stock = TRUE;

-- Due to precedence, this actually means:
-- category = 'Electronics' OR (category = 'Computers' AND in_stock = TRUE)
-- Returns: all Electronics (regardless of stock) + in-stock Computers

Always use parentheses for clarity:

-- Explicit grouping - get in-stock items from either category
SELECT product_id, product_name, category, in_stock
FROM products
WHERE (category = 'Electronics' OR category = 'Computers')
  AND in_stock = TRUE;

Complex real-world example:

-- Find orders needing attention
SELECT o.order_id, o.order_date, o.status, o.total_amount, o.priority
FROM orders o
WHERE (
    -- High-value orders that are delayed
    (o.total_amount > 1000 AND o.status = 'delayed')
    OR
    -- Priority orders not yet shipped
    (o.priority = 'high' AND o.status IN ('pending', 'processing'))
  )
  AND o.order_date > CURRENT_DATE - INTERVAL '90 days'
  AND NOT (o.status = 'cancelled' OR o.status = 'refunded');

Performance Considerations

Logical operators significantly impact query performance. Database optimizers use indexes differently based on operator combinations.

AND operations can leverage composite indexes effectively:

-- Create composite index
CREATE INDEX idx_orders_date_status ON orders(order_date, status);

-- This query can use the index efficiently
SELECT order_id, order_date, status
FROM orders
WHERE order_date >= '2024-01-01'
  AND status = 'shipped';

OR operations often prevent index usage:

-- May require full table scan
SELECT customer_id, name, state, account_type
FROM customers
WHERE state = 'CA'
   OR account_type = 'premium';

-- Better: use UNION for separate index lookups
SELECT customer_id, name, state, account_type
FROM customers
WHERE state = 'CA'
UNION
SELECT customer_id, name, state, account_type
FROM customers
WHERE account_type = 'premium';

NOT IN with subqueries can be slow with large datasets:

-- Potentially slow with large subquery results
SELECT product_id, product_name
FROM products
WHERE product_id NOT IN (SELECT product_id FROM discontinued_products);

-- Faster alternative with LEFT JOIN
SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN discontinued_products dp ON p.product_id = dp.product_id
WHERE dp.product_id IS NULL;

Practical Patterns

Filtering with date ranges and status:

-- Active subscriptions expiring soon
SELECT subscription_id, customer_id, end_date, plan_type
FROM subscriptions
WHERE status = 'active'
  AND end_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days'
  AND auto_renew = FALSE;

Multi-condition user segmentation:

-- Identify at-risk customers
SELECT c.customer_id, c.name, c.segment
FROM customers c
WHERE (
    -- No recent activity
    c.last_login_date < CURRENT_DATE - INTERVAL '60 days'
    OR
    -- Declining engagement
    c.monthly_active_days < 5
  )
  AND c.subscription_status = 'active'
  AND c.lifetime_value > 500
  AND NOT EXISTS (
    SELECT 1 FROM support_tickets st
    WHERE st.customer_id = c.customer_id
      AND st.created_date > CURRENT_DATE - INTERVAL '30 days'
  );

Inventory management with complex conditions:

-- Products needing reorder
SELECT p.product_id, p.product_name, p.current_stock, p.reorder_point
FROM products p
WHERE p.current_stock <= p.reorder_point
  AND p.discontinued = FALSE
  AND (
    p.supplier_id IN (SELECT supplier_id FROM suppliers WHERE status = 'active')
    OR p.supplier_id IS NULL
  )
  AND NOT EXISTS (
    SELECT 1 FROM pending_orders po
    WHERE po.product_id = p.product_id
      AND po.status IN ('pending', 'processing')
  );

Understanding these operators and their nuances enables you to write precise, performant queries that accurately express your data requirements.