Logic and Comparison

Single conditions are rarely enough for complex data analysis. SQL provides logical operators to combine multiple conditions and specialized comparison operators to handle ranges and lists efficiently.

Definition: Logical Operators

Logical operators allow you to build complex “Boolean” expressions.

• AND: Returns TRUE only if both conditions are true.
• OR: Returns TRUE if at least one condition is true.
• NOT: Reverses the value of a condition (True becomes False, and vice-versa).

Context: Order of Precedence

Just like in math where multiplication happens before addition ($2 + 3 imes 5 = 17$), SQL has an order of precedence for logical operators:

1. NOT (Highest)
2. AND
3. OR (Lowest)

Tip

Always use parentheses. Even if you know the precedence rules, parentheses make your intent clear to other developers and prevent accidental logic bugs.

Detailed Explanation: Range and List Operators

While you could use multiple AND or OR statements, SQL provides cleaner alternatives.

1. The BETWEEN Operator

Used to filter values within a range (inclusive of the start and end values).

• Standard: Price >= 10 AND Price <= 20
• SQL Idiomatic: Price BETWEEN 10 AND 20

2. The IN Operator

Used to check if a value matches any value in a provided list.

• Standard: City = 'London' OR City = 'Paris' OR City = 'Tokyo'
• SQL Idiomatic: City IN ('London', 'Paris', 'Tokyo')

Example: Complex Logic in Action

We have a Staff table:

ID	Name	Dept	Salary	Performance
1	John	IT	60000	4
2	Jane	HR	55000	5
3	Bob	IT	90000	3
4	Mary	Sales	70000	5

Scenario: High-Potential Employees

We want IT employees earning between 50k and 100k, OR anyone with a Performance score of 5.

Complex Predicates

SELECT Name, Dept, Salary, Performance
FROM Staff
WHERE (Dept = 'IT' AND Salary BETWEEN 50000 AND 100000)
 OR Performance = 5;

Output

Name	Dept	Salary	Performance
John	IT	60000	4
Jane	HR	55000	5
Bob	IT	90000	3
Mary	Sales	70000	5

Wait, why was Bob included? Let’s check the logic:

• Bob is in IT? Yes.
• Bob’s salary is between 50k-100k? Yes (90k).
• Thus, the first part of the OR is true.

Under the Hood: Short-Circuit Evaluation

Most modern SQL engines use Short-Circuit Evaluation.

• For an AND operation: If the first condition is FALSE, the engine stops immediately because the result can never be TRUE.
• For an OR operation: If the first condition is TRUE, the engine stops immediately because the result is already guaranteed to be TRUE.

Optimization Tip

If you have two conditions in an AND clause:

1. One is a complex calculation (slow).
2. One is a simple ID check (fast).

Place the simple check first. The engine might skip the slow calculation entirely if the simple check fails.

Caution

Be careful with NOT IN and NULL values. If the list in an IN operator contains a NULL, a NOT IN check will return zero results. We will explore why in the next chapter.