Chapter 4: Filtering Data with WHERE & Comparison Operators

Welcome to Chapter 4. In relational databases, tables often contain thousands or millions of records. Retrieving every row across the network is slow, wasteful, and exhausts server memory. The WHERE clause enables you to filter rows based on precise conditional expressions.

In this chapter, we will master comparison operators, execute efficient range and set searches, and understand boolean precedence to avoid subtle logical bugs.

🔍 4.1 Master Comparison Operators Table

Comparison operators compare a column value against a constant literal, expression, or another column:

💡 4.2 Inclusive Range Filtering with BETWEEN … AND …

The BETWEEN operator checks whether a value falls within an inclusive range (meaning the start and end boundary values are included):

-- Select employees whose salary is between 5000 and 10000 (inclusive)
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 5000 AND 10000
ORDER BY salary DESC;

You can also use BETWEEN on dates:

-- Find employees hired during the 1990s
SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE hire_date BETWEEN '1990-01-01' AND '1999-12-31'
ORDER BY hire_date ASC;

📜 4.3 Set Membership with IN (…)

Instead of stringing together multiple verbose OR conditions, the IN operator evaluates whether a column matches any element in a comma-separated list:

-- Verbose syntax using OR:
SELECT first_name, last_name, department_id
FROM employees
WHERE department_id = 20 OR department_id = 30 OR department_id = 60;

-- Clean, idiomatic syntax using IN:
SELECT first_name, last_name, department_id
FROM employees
WHERE department_id IN (20, 30, 60);

🔮 4.4 Boolean Logic: AND, OR, NOT & Operator Precedence

Complex queries require combining multiple conditions using boolean operators:

• AND: Evaluates to TRUE only if both conditions are true.
• OR: Evaluates to TRUE if at least one condition is true.
• NOT: Negates the condition immediately following it.

⚠ Critical Precedence Warning: In SQL, AND has higher operator precedence than OR! Without parentheses, MySQL evaluates all AND conditions before any OR conditions, leading to serious security and logical errors.

-- DANGEROUS: Because AND takes precedence, this selects ALL employees in dept 30,
-- regardless of their salary!
SELECT first_name, salary, department_id
FROM employees
WHERE department_id = 20 OR department_id = 30 AND salary > 10000;

-- SAFE & CORRECT: Explicit parentheses guarantee correct evaluation order:
SELECT first_name, salary, department_id
FROM employees
WHERE (department_id = 20 OR department_id = 30) AND salary > 10000;

❓ Frequently Asked Questions (FAQ)

1. Can I use column aliases in the WHERE clause?

No. In SQL query execution order, the WHERE clause executes before the SELECT clause produces column aliases. If you write SELECT salary * 12 AS annual_sal FROM employees WHERE annual_sal > 50000;, MySQL will raise an Unknown column ‘annual_sal’ error. You must repeat the expression in the WHERE clause: WHERE salary * 12 > 50000.

2. How does NOT IN handle NULL values?

If a set passed to NOT IN (val1, val2, NULL) contains a NULL, the entire expression evaluates to UNKNOWN, returning zero rows! Always ensure your NOT IN candidate list contains no nulls, or use NOT EXISTS.