Database Management SystemsSQL Queries

Logical Operators

LevelBeginner

Duration55 mins

TopicSQL Queries

5 / 5

Complex Conditions

Beyond Simple Filters

Real-world business logic rarely fits into simple conditions. A fraud detection system needs to check: (transaction amount > $10,000 AND new device) OR (multiple failed attempts AND foreign IP AND outside business hours) OR (flagged merchant AND velocity > 5 transactions/minute). A CRM report requires: customers who (purchased in last 90 days OR have active subscription) AND (are in US/Canada/UK) AND NOT (marked as churned OR payment failed).

These complex conditions are where your understanding of AND, OR, NOT, and precedence comes together. Mastering complex conditions means translating intricate business requirements into precise, efficient SQL that handles edge cases, scales to large datasets, and remains maintainable for future developers.

This page synthesizes everything we've learned into patterns for building sophisticated Boolean expressions.

What You Will Learn

By the end of this page, you will master complex condition construction: translating business rules to SQL, building multi-level Boolean expressions, combining conditions across tables, handling edge cases with NULL and empty sets, and structuring complex logic for maintainability and performance.

Anatomy of Complex Conditions

Complex conditions are built from simple conditions combined through logical operators. Understanding their structure helps you build and debug them systematically.

Structural Components:

Atomic Conditions: The simplest building blocks—single comparisons
- column = value
- column BETWEEN x AND y
- column IN (list)
- column IS NULL
Conjunctions (AND groups): Conditions that must all be true
- (A AND B AND C)
Disjunctions (OR groups): Alternative condition sets
- (A OR B OR C)
Negations: Inverted conditions or groups
- NOT (A)
- NOT (A AND B)
Nested Groups: Groups within groups
- ((A AND B) OR (C AND D)) AND E

anatomy_example.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
27
28
29
30
31
32
33
34
35
-- Complex condition structure breakdown
WHERE 
    -- Level 1: Top-level AND (all must be true)
    (
        -- Level 2: OR group (alternatives)
        (
            -- Level 3: AND group (first alternative)
            customer_type = 'Premium'
            AND account_age_days >= 365
        )
        OR
        (
            -- Level 3: AND group (second alternative)
            total_lifetime_value > 10000
            AND orders_count >= 10
        )
    )
    AND
    -- Level 2: Status checks (AND'd with above)
    status = 'Active'
    AND
    NOT (
        -- Level 2: Exclusion group
        is_flagged = TRUE
        OR under_review = TRUE
    )
    AND
    -- Level 2: Region check
    region IN ('US', 'CA', 'UK')
 
-- Reading this query:
-- Find customers who are: (Premium for 1+ year OR high LTV with many orders)
--                    AND: Active
--                    AND: Not flagged or under review
--                    AND: In US, Canada, or UK

Think in Levels

Complex conditions are hierarchical. The top level is usually AND (all major criteria must be met). Each criterion may contain OR groups (alternative ways to satisfy it). This 'AND of ORs' pattern (also called Disjunctive Normal Form) is very common and easy to reason about.

Translating Business Rules

Business rules are expressed in natural language, often ambiguously. Translating them to SQL requires careful parsing of logical connectives.

Language to Logic Mapping:

Natural Language to SQL Operators
Natural Language	SQL Equivalent	Example
"and", "as well as", "in addition to", "along with"	`AND`	Premium `AND` active
"or", "either...or", "alternatively"	`OR`	Gold `OR` Platinum
"not", "except", "excluding", "other than"	`NOT` / `!=` / `NOT IN`	`NOT` deleted
"both...and"	`AND`	verified `AND` confirmed
"neither...nor"	`NOT (A OR B)` or `NOT A AND NOT B`	`NOT` (spam `OR` blocked)
"unless"	`OR NOT` / conditional	show `OR NOT` hidden
"only if", "provided that"	`AND`	include if (active `AND` verified)

business_rule_translation.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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- BUSINESS RULE 1:
-- "Find customers who have spent over $1000 and either have a premium 
--  subscription or have been members for over 2 years"
 
-- Translation:
-- - "and" → AND
-- - "either...or" → OR
-- - "or have" → OR
 
SELECT * FROM customers
WHERE total_spent > 1000
  AND (
        subscription_type = 'Premium'
        OR membership_years > 2
      );
 
 
-- BUSINESS RULE 2:
-- "Show all orders except those that are cancelled or refunded, 
--  but include cancelled orders if they have active disputes"
 
-- Translation:
-- - "except those that are" → NOT IN / exclude
-- - "or" in exclusion → OR (to be negated together)
-- - "but include if" → overriding condition with OR
 
SELECT * FROM orders
WHERE (
        -- Base exclusion
        status NOT IN ('Cancelled', 'Refunded')
      )
   OR (
        -- Override: cancelled with dispute
        status = 'Cancelled' AND has_active_dispute = TRUE
      );
 
 
-- BUSINESS RULE 3:
-- "Employees eligible for bonus: those in Sales with quota achievement 
--  over 100%, or any employee with tenure over 10 years, but not those
--  with performance warnings in the current year"
 
SELECT * FROM employees
WHERE (
        -- Path 1: Sales with quota
        (department = 'Sales' AND quota_achievement > 100)
        OR
        -- Path 2: Long tenure
        (tenure_years > 10)
      )
  AND -- Exclusion
      NOT EXISTS (
        SELECT 1 FROM performance_warnings pw
        WHERE pw.employee_id = employees.employee_id
        AND pw.warning_year = EXTRACT(YEAR FROM CURRENT_DATE)
      );

Ambiguous 'Or' in English

English 'or' can be inclusive (A or B or both) or exclusive (A or B but not both). SQL's OR is always inclusive. If the business rule means exclusive or, you need: (A OR B) AND NOT (A AND B). Always clarify ambiguous requirements!

Multi-Table Complex Conditions

When conditions span multiple tables, you combine JOINs with complex WHERE clauses, or use subqueries and EXISTS for related data conditions:

multi_table_conditions.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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
-- Pattern 1: JOIN with complex conditions on multiple tables
SELECT DISTINCT c.customer_id, c.name, c.email
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE 
    -- Customer conditions
    (c.customer_type = 'Premium' OR c.total_spent > 5000)
    AND c.status = 'Active'
    -- Order conditions
    AND o.order_date >= '2024-01-01'
    AND o.status = 'Completed'
    -- Product conditions (bought electronics or computers)
    AND (p.category = 'Electronics' OR p.category = 'Computers');
 
 
-- Pattern 2: EXISTS for "has related records matching condition"
SELECT c.customer_id, c.name
FROM customers c
WHERE c.status = 'Active'
  AND (
        -- Has recent large order
        EXISTS (
            SELECT 1 FROM orders o
            WHERE o.customer_id = c.customer_id
            AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
            AND o.total > 500
        )
        OR
        -- Has active subscription
        EXISTS (
            SELECT 1 FROM subscriptions s
            WHERE s.customer_id = c.customer_id
            AND s.status = 'Active'
            AND s.ends_at > CURRENT_DATE
        )
      );
 
 
-- Pattern 3: NOT EXISTS for "has no related records"
SELECT p.product_id, p.name
FROM products p
WHERE p.status = 'Active'
  AND p.created_at < CURRENT_DATE - INTERVAL '90 days'
  -- Never been ordered
  AND NOT EXISTS (
        SELECT 1 FROM order_items oi
        WHERE oi.product_id = p.product_id
      )
  -- Not in any wishlist
  AND NOT EXISTS (
        SELECT 1 FROM wishlist_items wi
        WHERE wi.product_id = p.product_id
      );
 
 
-- Pattern 4: Correlated subqueries for aggregate conditions
SELECT c.customer_id, c.name
FROM customers c
WHERE 
    -- Has placed enough orders
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) >= 5
    AND
    -- Average order value is high
    (SELECT AVG(total) FROM orders o WHERE o.customer_id = c.customer_id) > 200
    AND
    -- Recent activity
    (SELECT MAX(order_date) FROM orders o WHERE o.customer_id = c.customer_id) 
        >= CURRENT_DATE - INTERVAL '90 days';

EXISTS vs JOIN for Conditions

Use EXISTS when you only need to check if related records exist (boolean check). Use JOIN when you need data from the related table in your results. EXISTS often performs better because it can stop at the first matching row.

Handling NULL in Complex Conditions

NULL values in complex conditions require careful handling. The three-valued logic propagates through the entire expression:

null_in_complex.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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- Problem: NULL values can cause unexpected exclusions
 
-- Table: employees (some have NULL commission)
-- Goal: Find employees in Sales with high salary OR high commission
 
-- NAIVE APPROACH (loses employees with NULL commission)
SELECT * FROM employees
WHERE department = 'Sales'
  AND (salary > 100000 OR commission > 10000);
 
-- For employee with salary = 80000, commission = NULL:
-- salary > 100000 → FALSE
-- commission > 10000 → UNKNOWN
-- FALSE OR UNKNOWN → UNKNOWN
-- Entire row is EXCLUDED!
 
 
-- SOLUTION 1: Explicit NULL handling in each OR branch
SELECT * FROM employees
WHERE department = 'Sales'
  AND (
        salary > 100000 
        OR (commission IS NOT NULL AND commission > 10000)
      );
 
 
-- SOLUTION 2: COALESCE to convert NULL to a value
SELECT * FROM employees
WHERE department = 'Sales'
  AND (salary > 100000 OR COALESCE(commission, 0) > 10000);
 
 
-- SOLUTION 3: Separate the NULL case explicitly
SELECT * FROM employees
WHERE department = 'Sales'
  AND (
        salary > 100000
        OR commission > 10000
        -- Optional: include NULL commission with other qualifying criteria
        OR (commission IS NULL AND salary > 80000)
      );
 
 
-- Complex example: Multiple nullable columns
SELECT * FROM products
WHERE status = 'Active'
  AND (
        -- Standard discount
        (discount_percent IS NOT NULL AND discount_percent >= 20)
        OR
        -- Clearance (may have NULL discount)
        (clearance_flag = TRUE)
        OR
        -- Low stock alert (quantity may be NULL for new products)
        (COALESCE(stock_quantity, 0) <= 5 AND COALESCE(stock_quantity, 0) > 0)
      );

The NULL Disappearance Problem

In complex OR conditions, rows with NULL in any compared column might disappear from results even when other OR branches should include them. Always trace through your logic with sample NULL values to verify behavior.

Conditional Logic Patterns

Sometimes conditions depend on other conditions—implementing if-then logic within WHERE clauses:

Logical Implication: "If A, then B" means rows either don't have A, or have both A and B.

implication.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- "If the order is international, it must have customs documentation"
-- Expressed as: NOT international OR has_customs_docs
 
SELECT * FROM orders
WHERE status = 'Ready'
  AND (NOT is_international OR customs_docs_complete = TRUE);
 
-- This passes orders that are:
-- 1. Domestic (NOT is_international → TRUE)
-- 2. International WITH customs docs (FALSE OR TRUE → TRUE)
-- And excludes:
-- 3. International WITHOUT customs docs (FALSE OR FALSE → FALSE)
 
 
-- "If customer is new, require email verification"
SELECT * FROM customers
WHERE (
        account_age_days >= 30  -- Not new
        OR email_verified = TRUE  -- Or verified
      )
  AND status = 'Active';

Performance Optimization

Complex conditions can significantly impact query performance. Apply these optimization strategies:

Optimization Strategies

•Put most selective conditions first — Conditions that eliminate the most rows should be evaluated early (though optimizers often reorder automatically).
•Minimize OR conditions across different columns — Each OR branch may require a separate index scan.
•Use EXISTS instead of IN for large subqueries — EXISTS can short-circuit after finding one match.
•Avoid functions on indexed columns — WHERE YEAR(date_col) = 2024 can't use an index; WHERE date_col >= '2024-01-01' AND date_col < '2025-01-01' can.
•Consider partial indexes — For frequently-filtered conditions like status = 'Active', a partial index can be highly efficient.

performance_optimization.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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- BEFORE: Function on indexed column prevents index use
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2024
  AND EXTRACT(MONTH FROM order_date) = 3
  AND customer_type = 'Premium';
 
-- AFTER: Range conditions enable index use
SELECT * FROM orders
WHERE order_date >= '2024-03-01'
  AND order_date < '2024-04-01'
  AND customer_type = 'Premium';
 
 
-- BEFORE: OR across columns = multiple scans
SELECT * FROM products
WHERE product_id = 12345
   OR sku = 'ABC-123'
   OR name LIKE 'Widget%';
 
-- AFTER: Consider UNION for independent indexed conditions
SELECT * FROM products WHERE product_id = 12345
UNION
SELECT * FROM products WHERE sku = 'ABC-123'
UNION
SELECT * FROM products WHERE name LIKE 'Widget%';
 
 
-- BEFORE: IN with large subquery
SELECT * FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE amount > 1000);
 
-- AFTER: EXISTS (often faster, especially for large subqueries)
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id AND o.amount > 1000
);
 
 
-- Composite index recommendation for common complex query:
-- Query: WHERE status = 'Active' AND type IN (...) AND date >= X
-- Index: CREATE INDEX idx_status_type_date ON table(status, type, date);

Always Check Execution Plans

Use EXPLAIN or EXPLAIN ANALYZE to see how the database actually executes your complex query. Look for sequential scans on large tables—they often indicate missing indexes or conditions that can't use existing indexes.

Maintainability Patterns

Complex conditions can become unreadable and unmaintainable. Use these patterns to keep logic clear:

maintainability.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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- Pattern 1: Use CTEs to name complex logic
WITH 
    premium_customers AS (
        SELECT customer_id FROM customers
        WHERE customer_type = 'Premium'
           OR (total_lifetime_value > 10000 AND membership_years >= 2)
    ),
    active_subscribers AS (
        SELECT customer_id FROM subscriptions
        WHERE status = 'Active' AND ends_at > CURRENT_DATE
    ),
    recent_purchasers AS (
        SELECT DISTINCT customer_id FROM orders
        WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
    )
SELECT c.*
FROM customers c
WHERE c.customer_id IN (SELECT customer_id FROM premium_customers)
   OR c.customer_id IN (SELECT customer_id FROM active_subscribers)
   OR c.customer_id IN (SELECT customer_id FROM recent_purchasers);
 
 
-- Pattern 2: CASE with named boolean expressions
SELECT *
FROM loan_applications la
WHERE CASE 
        -- Name each eligibility path
        WHEN credit_score >= 750 THEN TRUE  -- Path: Excellent Credit
        WHEN credit_score >= 650 
             AND annual_income >= 60000 
             AND employment_years >= 2 THEN TRUE  -- Path: Good Credit + Stable Income
        WHEN has_co_signer = TRUE 
             AND co_signer_credit_score >= 700 THEN TRUE  -- Path: Co-signed
        ELSE FALSE
      END;
 
 
-- Pattern 3: Views for reusable complex conditions
CREATE VIEW eligible_customers AS
SELECT c.*,
       CASE WHEN customer_type = 'Premium' OR total_spent > 5000 
            THEN TRUE ELSE FALSE END AS is_high_value,
       CASE WHEN last_purchase_date >= CURRENT_DATE - INTERVAL '90 days'
            THEN TRUE ELSE FALSE END AS is_recently_active
FROM customers c
WHERE status = 'Active' AND NOT is_blocked;
 
-- Now queries are simple:
SELECT * FROM eligible_customers
WHERE is_high_value AND is_recently_active;
 
 
-- Pattern 4: Comments explaining business logic
SELECT * FROM orders
WHERE 
    -- ELIGIBILITY: Order must be shippable
    status IN ('Paid', 'Processing')
    AND shipping_address_verified = TRUE
    
    -- PRIORITIZATION: Express or high-value gets priority
    AND (
        shipping_method = 'Express'  -- Customer paid for priority
        OR total >= 500              -- High-value order
        OR customer_tier = 'VIP'     -- VIP always prioritized
    )
    
    -- EXCLUSIONS: Don't ship to restricted regions
    AND shipping_country NOT IN ('XX', 'YY', 'ZZ')
    AND NOT under_fraud_review;

Maintainability Guidelines

•Name complex logic with CTEs — Break large conditions into named, testable parts
•Use Views for reusable filters — Define common eligibility/status logic once
•Comment the 'why', not the 'what' — Explain business rationale, not SQL syntax
•Group related conditions visually — Use line breaks and indentation intentionally
•Test each branch independently — Verify edge cases for every OR path

Summary: Mastering Complex Conditions

Complex conditions are where SQL logical operators come to life, expressing real business requirements. Let's consolidate the key insights:

Key Takeaways

•Complex conditions are hierarchical — Top-level AND with OR subgroups (Disjunctive Normal Form) is the clearest pattern.
•Translate business language carefully — Map 'and', 'or', 'except', 'unless' to correct operators. Clarify ambiguities.
•Use EXISTS for multi-table conditions — More flexible and often more efficient than JOINs for existence checks.
•Handle NULL explicitly — NULL propagates UNKNOWN through complex expressions. Use IS NOT NULL, COALESCE, or explicit branches.
•CASE enables conditional logic — Different criteria can apply based on row values within the same WHERE clause.
•Optimize for performance — Watch OR across columns, avoid functions on indexed columns, check execution plans.
•Prioritize maintainability — Use CTEs, Views, comments, and clear formatting. Your future self will thank you.

Module Complete!

You've now mastered SQL logical operators from the ground up: AND for conjunction, OR for disjunction, NOT for negation, operator precedence for evaluation order, and complex conditions that bring everything together. These skills are fundamental to every SQL query you'll write—from simple filters to sophisticated business logic.

Module Complete: Logical Operators

You've completed the Logical Operators module! You now understand AND, OR, NOT, operator precedence, and complex condition patterns at a professional level. These operators form the foundation of all SQL query filtering—every WHERE clause you write will use these concepts. Continue to the next module to explore ORDER BY for result sorting.

5 / 5

Loading learning content...

Database Management SystemsSQL Queries

Logical Operators

LevelBeginner

Duration55 mins

TopicSQL Queries

5 / 5

Complex Conditions

Beyond Simple Filters

This page synthesizes everything we've learned into patterns for building sophisticated Boolean expressions.

What You Will Learn

Anatomy of Complex Conditions

Complex conditions are built from simple conditions combined through logical operators. Understanding their structure helps you build and debug them systematically.

Structural Components:

Atomic Conditions: The simplest building blocks—single comparisons
- column = value
- column BETWEEN x AND y
- column IN (list)
- column IS NULL
Conjunctions (AND groups): Conditions that must all be true
- (A AND B AND C)
Disjunctions (OR groups): Alternative condition sets
- (A OR B OR C)
Negations: Inverted conditions or groups
- NOT (A)
- NOT (A AND B)
Nested Groups: Groups within groups
- ((A AND B) OR (C AND D)) AND E

anatomy_example.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
27
28
29
30
31
32
33
34
35
-- Complex condition structure breakdown
WHERE 
    -- Level 1: Top-level AND (all must be true)
    (
        -- Level 2: OR group (alternatives)
        (
            -- Level 3: AND group (first alternative)
            customer_type = 'Premium'
            AND account_age_days >= 365
        )
        OR
        (
            -- Level 3: AND group (second alternative)
            total_lifetime_value > 10000
            AND orders_count >= 10
        )
    )
    AND
    -- Level 2: Status checks (AND'd with above)
    status = 'Active'
    AND
    NOT (
        -- Level 2: Exclusion group
        is_flagged = TRUE
        OR under_review = TRUE
    )
    AND
    -- Level 2: Region check
    region IN ('US', 'CA', 'UK')
 
-- Reading this query:
-- Find customers who are: (Premium for 1+ year OR high LTV with many orders)
--                    AND: Active
--                    AND: Not flagged or under review
--                    AND: In US, Canada, or UK

Think in Levels

Translating Business Rules

Business rules are expressed in natural language, often ambiguously. Translating them to SQL requires careful parsing of logical connectives.

Language to Logic Mapping:

Natural Language to SQL Operators
Natural Language	SQL Equivalent	Example
"and", "as well as", "in addition to", "along with"	`AND`	Premium `AND` active
"or", "either...or", "alternatively"	`OR`	Gold `OR` Platinum
"not", "except", "excluding", "other than"	`NOT` / `!=` / `NOT IN`	`NOT` deleted
"both...and"	`AND`	verified `AND` confirmed
"neither...nor"	`NOT (A OR B)` or `NOT A AND NOT B`	`NOT` (spam `OR` blocked)
"unless"	`OR NOT` / conditional	show `OR NOT` hidden
"only if", "provided that"	`AND`	include if (active `AND` verified)

business_rule_translation.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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- BUSINESS RULE 1:
-- "Find customers who have spent over $1000 and either have a premium 
--  subscription or have been members for over 2 years"
 
-- Translation:
-- - "and" → AND
-- - "either...or" → OR
-- - "or have" → OR
 
SELECT * FROM customers
WHERE total_spent > 1000
  AND (
        subscription_type = 'Premium'
        OR membership_years > 2
      );
 
 
-- BUSINESS RULE 2:
-- "Show all orders except those that are cancelled or refunded, 
--  but include cancelled orders if they have active disputes"
 
-- Translation:
-- - "except those that are" → NOT IN / exclude
-- - "or" in exclusion → OR (to be negated together)
-- - "but include if" → overriding condition with OR
 
SELECT * FROM orders
WHERE (
        -- Base exclusion
        status NOT IN ('Cancelled', 'Refunded')
      )
   OR (
        -- Override: cancelled with dispute
        status = 'Cancelled' AND has_active_dispute = TRUE
      );
 
 
-- BUSINESS RULE 3:
-- "Employees eligible for bonus: those in Sales with quota achievement 
--  over 100%, or any employee with tenure over 10 years, but not those
--  with performance warnings in the current year"
 
SELECT * FROM employees
WHERE (
        -- Path 1: Sales with quota
        (department = 'Sales' AND quota_achievement > 100)
        OR
        -- Path 2: Long tenure
        (tenure_years > 10)
      )
  AND -- Exclusion
      NOT EXISTS (
        SELECT 1 FROM performance_warnings pw
        WHERE pw.employee_id = employees.employee_id
        AND pw.warning_year = EXTRACT(YEAR FROM CURRENT_DATE)
      );

Ambiguous 'Or' in English

Multi-Table Complex Conditions

When conditions span multiple tables, you combine JOINs with complex WHERE clauses, or use subqueries and EXISTS for related data conditions:

multi_table_conditions.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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
-- Pattern 1: JOIN with complex conditions on multiple tables
SELECT DISTINCT c.customer_id, c.name, c.email
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE 
    -- Customer conditions
    (c.customer_type = 'Premium' OR c.total_spent > 5000)
    AND c.status = 'Active'
    -- Order conditions
    AND o.order_date >= '2024-01-01'
    AND o.status = 'Completed'
    -- Product conditions (bought electronics or computers)
    AND (p.category = 'Electronics' OR p.category = 'Computers');
 
 
-- Pattern 2: EXISTS for "has related records matching condition"
SELECT c.customer_id, c.name
FROM customers c
WHERE c.status = 'Active'
  AND (
        -- Has recent large order
        EXISTS (
            SELECT 1 FROM orders o
            WHERE o.customer_id = c.customer_id
            AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
            AND o.total > 500
        )
        OR
        -- Has active subscription
        EXISTS (
            SELECT 1 FROM subscriptions s
            WHERE s.customer_id = c.customer_id
            AND s.status = 'Active'
            AND s.ends_at > CURRENT_DATE
        )
      );
 
 
-- Pattern 3: NOT EXISTS for "has no related records"
SELECT p.product_id, p.name
FROM products p
WHERE p.status = 'Active'
  AND p.created_at < CURRENT_DATE - INTERVAL '90 days'
  -- Never been ordered
  AND NOT EXISTS (
        SELECT 1 FROM order_items oi
        WHERE oi.product_id = p.product_id
      )
  -- Not in any wishlist
  AND NOT EXISTS (
        SELECT 1 FROM wishlist_items wi
        WHERE wi.product_id = p.product_id
      );
 
 
-- Pattern 4: Correlated subqueries for aggregate conditions
SELECT c.customer_id, c.name
FROM customers c
WHERE 
    -- Has placed enough orders
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) >= 5
    AND
    -- Average order value is high
    (SELECT AVG(total) FROM orders o WHERE o.customer_id = c.customer_id) > 200
    AND
    -- Recent activity
    (SELECT MAX(order_date) FROM orders o WHERE o.customer_id = c.customer_id) 
        >= CURRENT_DATE - INTERVAL '90 days';

EXISTS vs JOIN for Conditions

Handling NULL in Complex Conditions

NULL values in complex conditions require careful handling. The three-valued logic propagates through the entire expression:

null_in_complex.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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- Problem: NULL values can cause unexpected exclusions
 
-- Table: employees (some have NULL commission)
-- Goal: Find employees in Sales with high salary OR high commission
 
-- NAIVE APPROACH (loses employees with NULL commission)
SELECT * FROM employees
WHERE department = 'Sales'
  AND (salary > 100000 OR commission > 10000);
 
-- For employee with salary = 80000, commission = NULL:
-- salary > 100000 → FALSE
-- commission > 10000 → UNKNOWN
-- FALSE OR UNKNOWN → UNKNOWN
-- Entire row is EXCLUDED!
 
 
-- SOLUTION 1: Explicit NULL handling in each OR branch
SELECT * FROM employees
WHERE department = 'Sales'
  AND (
        salary > 100000 
        OR (commission IS NOT NULL AND commission > 10000)
      );
 
 
-- SOLUTION 2: COALESCE to convert NULL to a value
SELECT * FROM employees
WHERE department = 'Sales'
  AND (salary > 100000 OR COALESCE(commission, 0) > 10000);
 
 
-- SOLUTION 3: Separate the NULL case explicitly
SELECT * FROM employees
WHERE department = 'Sales'
  AND (
        salary > 100000
        OR commission > 10000
        -- Optional: include NULL commission with other qualifying criteria
        OR (commission IS NULL AND salary > 80000)
      );
 
 
-- Complex example: Multiple nullable columns
SELECT * FROM products
WHERE status = 'Active'
  AND (
        -- Standard discount
        (discount_percent IS NOT NULL AND discount_percent >= 20)
        OR
        -- Clearance (may have NULL discount)
        (clearance_flag = TRUE)
        OR
        -- Low stock alert (quantity may be NULL for new products)
        (COALESCE(stock_quantity, 0) <= 5 AND COALESCE(stock_quantity, 0) > 0)
      );

The NULL Disappearance Problem

Conditional Logic Patterns

Sometimes conditions depend on other conditions—implementing if-then logic within WHERE clauses:

Logical Implication: "If A, then B" means rows either don't have A, or have both A and B.

implication.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- "If the order is international, it must have customs documentation"
-- Expressed as: NOT international OR has_customs_docs
 
SELECT * FROM orders
WHERE status = 'Ready'
  AND (NOT is_international OR customs_docs_complete = TRUE);
 
-- This passes orders that are:
-- 1. Domestic (NOT is_international → TRUE)
-- 2. International WITH customs docs (FALSE OR TRUE → TRUE)
-- And excludes:
-- 3. International WITHOUT customs docs (FALSE OR FALSE → FALSE)
 
 
-- "If customer is new, require email verification"
SELECT * FROM customers
WHERE (
        account_age_days >= 30  -- Not new
        OR email_verified = TRUE  -- Or verified
      )
  AND status = 'Active';

Performance Optimization

Complex conditions can significantly impact query performance. Apply these optimization strategies:

Optimization Strategies

•Put most selective conditions first — Conditions that eliminate the most rows should be evaluated early (though optimizers often reorder automatically).
•Minimize OR conditions across different columns — Each OR branch may require a separate index scan.
•Use EXISTS instead of IN for large subqueries — EXISTS can short-circuit after finding one match.
•Avoid functions on indexed columns — WHERE YEAR(date_col) = 2024 can't use an index; WHERE date_col >= '2024-01-01' AND date_col < '2025-01-01' can.
•Consider partial indexes — For frequently-filtered conditions like status = 'Active', a partial index can be highly efficient.

performance_optimization.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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- BEFORE: Function on indexed column prevents index use
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2024
  AND EXTRACT(MONTH FROM order_date) = 3
  AND customer_type = 'Premium';
 
-- AFTER: Range conditions enable index use
SELECT * FROM orders
WHERE order_date >= '2024-03-01'
  AND order_date < '2024-04-01'
  AND customer_type = 'Premium';
 
 
-- BEFORE: OR across columns = multiple scans
SELECT * FROM products
WHERE product_id = 12345
   OR sku = 'ABC-123'
   OR name LIKE 'Widget%';
 
-- AFTER: Consider UNION for independent indexed conditions
SELECT * FROM products WHERE product_id = 12345
UNION
SELECT * FROM products WHERE sku = 'ABC-123'
UNION
SELECT * FROM products WHERE name LIKE 'Widget%';
 
 
-- BEFORE: IN with large subquery
SELECT * FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE amount > 1000);
 
-- AFTER: EXISTS (often faster, especially for large subqueries)
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id AND o.amount > 1000
);
 
 
-- Composite index recommendation for common complex query:
-- Query: WHERE status = 'Active' AND type IN (...) AND date >= X
-- Index: CREATE INDEX idx_status_type_date ON table(status, type, date);

Always Check Execution Plans

Maintainability Patterns

Complex conditions can become unreadable and unmaintainable. Use these patterns to keep logic clear:

maintainability.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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- Pattern 1: Use CTEs to name complex logic
WITH 
    premium_customers AS (
        SELECT customer_id FROM customers
        WHERE customer_type = 'Premium'
           OR (total_lifetime_value > 10000 AND membership_years >= 2)
    ),
    active_subscribers AS (
        SELECT customer_id FROM subscriptions
        WHERE status = 'Active' AND ends_at > CURRENT_DATE
    ),
    recent_purchasers AS (
        SELECT DISTINCT customer_id FROM orders
        WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
    )
SELECT c.*
FROM customers c
WHERE c.customer_id IN (SELECT customer_id FROM premium_customers)
   OR c.customer_id IN (SELECT customer_id FROM active_subscribers)
   OR c.customer_id IN (SELECT customer_id FROM recent_purchasers);
 
 
-- Pattern 2: CASE with named boolean expressions
SELECT *
FROM loan_applications la
WHERE CASE 
        -- Name each eligibility path
        WHEN credit_score >= 750 THEN TRUE  -- Path: Excellent Credit
        WHEN credit_score >= 650 
             AND annual_income >= 60000 
             AND employment_years >= 2 THEN TRUE  -- Path: Good Credit + Stable Income
        WHEN has_co_signer = TRUE 
             AND co_signer_credit_score >= 700 THEN TRUE  -- Path: Co-signed
        ELSE FALSE
      END;
 
 
-- Pattern 3: Views for reusable complex conditions
CREATE VIEW eligible_customers AS
SELECT c.*,
       CASE WHEN customer_type = 'Premium' OR total_spent > 5000 
            THEN TRUE ELSE FALSE END AS is_high_value,
       CASE WHEN last_purchase_date >= CURRENT_DATE - INTERVAL '90 days'
            THEN TRUE ELSE FALSE END AS is_recently_active
FROM customers c
WHERE status = 'Active' AND NOT is_blocked;
 
-- Now queries are simple:
SELECT * FROM eligible_customers
WHERE is_high_value AND is_recently_active;
 
 
-- Pattern 4: Comments explaining business logic
SELECT * FROM orders
WHERE 
    -- ELIGIBILITY: Order must be shippable
    status IN ('Paid', 'Processing')
    AND shipping_address_verified = TRUE
    
    -- PRIORITIZATION: Express or high-value gets priority
    AND (
        shipping_method = 'Express'  -- Customer paid for priority
        OR total >= 500              -- High-value order
        OR customer_tier = 'VIP'     -- VIP always prioritized
    )
    
    -- EXCLUSIONS: Don't ship to restricted regions
    AND shipping_country NOT IN ('XX', 'YY', 'ZZ')
    AND NOT under_fraud_review;

Maintainability Guidelines

•Name complex logic with CTEs — Break large conditions into named, testable parts
•Use Views for reusable filters — Define common eligibility/status logic once
•Comment the 'why', not the 'what' — Explain business rationale, not SQL syntax
•Group related conditions visually — Use line breaks and indentation intentionally
•Test each branch independently — Verify edge cases for every OR path

Summary: Mastering Complex Conditions

Complex conditions are where SQL logical operators come to life, expressing real business requirements. Let's consolidate the key insights:

Key Takeaways

•Complex conditions are hierarchical — Top-level AND with OR subgroups (Disjunctive Normal Form) is the clearest pattern.
•Translate business language carefully — Map 'and', 'or', 'except', 'unless' to correct operators. Clarify ambiguities.
•Use EXISTS for multi-table conditions — More flexible and often more efficient than JOINs for existence checks.
•Handle NULL explicitly — NULL propagates UNKNOWN through complex expressions. Use IS NOT NULL, COALESCE, or explicit branches.
•CASE enables conditional logic — Different criteria can apply based on row values within the same WHERE clause.
•Optimize for performance — Watch OR across columns, avoid functions on indexed columns, check execution plans.
•Prioritize maintainability — Use CTEs, Views, comments, and clear formatting. Your future self will thank you.

Module Complete!

Module Complete: Logical Operators

5 / 5