Chapter 4: Filtering Data with WHERE, Comparison Operators & Boolean Logic

✦

AI Reading

Quick summary of Chapter 4

Data filtering is the cornerstone of efficient database programming. In this comprehensive chapter, you will master the WHERE clause, explore every SQL comparison operator, filter inclusive ranges with BETWEEN, evaluate multi-value sets with IN, and construct rock-solid compound filters using boolean logic.

• Predicate Execution Flow: Understand how the MySQL query engine evaluates the WHERE clause before column projection or aggregation.
• Full Comparison Spectrum: Master =, >, <, >=, <=, and both inequality operators (<> and !=).
• Range & Set Matching: Filter numeric, date, and string ranges with BETWEEN ... AND ... and simplify verbose OR conditions with IN (...).
• Boolean Precedence & Parentheses: Prevent catastrophic logic errors caused by AND taking precedence over OR.
• Sargability & Index Efficiency: Learn how to write search conditions that utilize B-Tree indexes instead of triggering slow full table scans.

In relational database systems, tables frequently store hundreds of thousands or even millions of records. Sending an entire table across the network to an application server consumes excessive bandwidth, exhausts RAM, and slows your application to a crawl. The WHERE clause is the fundamental SQL tool that allows you to specify search conditions (called predicates), instructing MySQL to inspect table rows and return only those that meet your exact business criteria.

In this chapter, we will examine every comparison operator in depth, study query execution plans, understand operator precedence, and run hands-on queries against our corporate employees and departments dataset with exact database output tables.

---

🔍 4.1 Master Comparison Operators Reference

Comparison operators test whether two expressions are equal, greater than, less than, or within a specific boundary. When MySQL evaluates a WHERE condition for a row, the expression evaluates to one of three values: TRUE, FALSE, or UNKNOWN (when NULLs are involved). Only rows evaluating to TRUE are returned.

Operator	Mathematical Meaning	Example Query Syntax	Filtering Behavior
=	Equal to	WHERE salary = 12000	Matches rows where the column exactly equals 12000.
>	Strictly greater than	WHERE salary > 10000	Matches values strictly above 10000 (excludes 10000 itself).
<	Strictly less than	WHERE salary < 5000	Matches values strictly below 5000 (excludes 5000 itself).
>=	Greater than or equal to	WHERE hire_date >= '1995-01-01'	Matches values equal to or later than January 1, 1995.
<=	Less than or equal to	WHERE salary <= 8000	Matches values up to and including 8000.
<> or !=	Not equal to	WHERE department_id <> 20	Matches all rows where department is anything other than 20.
BETWEEN ... AND ...	Inclusive boundary range	WHERE salary BETWEEN 5000 AND 10000	Matches values within [5000, 10000] including endpoints.
IN (...)	Discrete set membership	WHERE department_id IN (20, 30, 60)	Matches rows whose value exists in the specified list.

---

💻 4.2 Practical Query Walkthroughs with Real Database Output

Scenario 1: Exact Equality Matching with =

Find all employees whose job title is an IT Programmer (job_id = 'IT_PROG'):

SELECT employee_id, first_name, last_name, job_id, salary
FROM employees
WHERE job_id = 'IT_PROG'
ORDER BY salary DESC;

Live Database Output:

+-------------+------------+-----------+---------+--------+
| employee_id | first_name | last_name | job_id  | salary |
+-------------+------------+-----------+---------+--------+
|         103 | Alexander  | Hunold    | IT_PROG |   9000 |
|         104 | Bruce      | Ernst     | IT_PROG |   6000 |
|         105 | David      | Austin    | IT_PROG |   4800 |
|         106 | Valli      | Pataballa | IT_PROG |   4800 |
|         107 | Diana      | Lorentz   | IT_PROG |   4200 |
+-------------+------------+-----------+---------+--------+

---

Scenario 2: Inequality Matching with <> and !=

In standard SQL, the inequality operator is written as <>. MySQL also supports !=. Both behave identically. Select all employees who do not belong to Department 50 (Shipping):

SELECT employee_id, first_name, last_name, department_id, salary
FROM employees
WHERE department_id <> 50
ORDER BY salary DESC
LIMIT 6;

Live Database Output:

+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | department_id | salary |
+-------------+------------+-----------+---------------+--------+
|         100 | Steven     | King      |            20 |  24000 |
|         101 | Neena      | Kochhar   |            20 |  17000 |
|         102 | Lex        | De Haan   |            30 |  17000 |
|         108 | Nancy      | Greenberg |           100 |  12000 |
|         114 | Den        | Raphaely  |            30 |  11000 |
|         103 | Alexander  | Hunold    |            60 |   9000 |
+-------------+------------+-----------+---------------+--------+

---

Scenario 3: Date Comparisons with >= and <=

Date columns in MySQL follow the ISO-8601 standard format YYYY-MM-DD. You can compare dates using standard comparison operators just like numbers. Find all employees hired on or after January 1, 1998:

SELECT employee_id, first_name, last_name, hire_date, salary
FROM employees
WHERE hire_date >= '1998-01-01'
ORDER BY hire_date ASC;

Live Database Output:

+-------------+-------------+-------------+------------+--------+
| employee_id | first_name  | last_name   | hire_date  | salary |
+-------------+-------------+-------------+------------+--------+
|         118 | Guy         | Himuro      | 1998-01-02 |   2600 |
|         106 | Valli       | Pataballa   | 1998-02-05 |   4800 |
|         112 | Jose Manuel | Urman       | 1998-06-03 |   7800 |
|         126 | Irene       | Mikkilineni | 1998-11-12 |   2700 |
|         127 | James       | Landry      | 1999-01-02 |   2400 |
|         107 | Diana       | Lorentz     | 1999-02-09 |   4200 |
|         119 | Karen       | Colmenares  | 1999-04-08 |   2500 |
|         124 | Kevin       | Mourgos     | 1999-11-12 |   5800 |
|         113 | Luis        | Popp        | 1999-12-07 |   6900 |
|         128 | Steven      | Markle      | 2000-03-04 |   2200 |
+-------------+-------------+-------------+------------+--------+

---

💡 4.3 Range Filtering with BETWEEN … AND …

The BETWEEN operator tests whether a value falls within a specified lower and upper boundary. A critical characteristic of BETWEEN in SQL is that it is inclusive: the expression col BETWEEN A AND B is mathematically equivalent to col >= A AND col <= B.

-- Find employees whose salary is between 6000 and 9000 (inclusive)
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 6000 AND 9000
ORDER BY salary DESC;

Live Database Output:

+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
|         103 | Alexander  | Hunold    |   9000 |
|         109 | Daniel     | Faviet    |   9000 |
|         110 | John       | Chen      |   8200 |
|         121 | Adam       | Fripp     |   8200 |
|         120 | Matthew    | Weiss     |   8000 |
|         122 | Payam      | Kaufling  |   7900 |
|         112 | Jose Manuel| Urman     |   7800 |
|         111 | Ismael     | Sciarra   |   7700 |
|         113 | Luis       | Popp      |   6900 |
|         123 | Shanta     | Vollman   |   6500 |
|         104 | Bruce      | Ernst     |   6000 |
+-------------+------------+-----------+--------+

You can also negate ranges using NOT BETWEEN:

-- Find extreme earners: employees earning outside the 3000 to 15000 bracket
SELECT first_name, last_name, salary
FROM employees
WHERE salary NOT BETWEEN 3000 AND 15000
ORDER BY salary DESC;

---

📜 4.4 Set Membership with IN and NOT IN

When searching for records matching several discrete values, using multiple OR conditions becomes unreadable and tedious. The IN operator allows you to provide a clean list of candidates:

-- Find employees in Purchasing (30), HR (40), or IT (60)
SELECT employee_id, first_name, last_name, department_id, salary
FROM employees
WHERE department_id IN (30, 40, 60)
ORDER BY department_id ASC, salary DESC;

Live Database Output:

+-------------+------------+------------+---------------+--------+
| employee_id | first_name | last_name  | department_id | salary |
+-------------+------------+------------+---------------+--------+
|         102 | Lex        | De Haan    |            30 |  17000 |
|         114 | Den        | Raphaely   |            30 |  11000 |
|         117 | Sigal      | Tobias     |            30 |   2800 |
|         122 | Payam      | Kaufling   |            40 |   7900 |
|         106 | Valli      | Pataballa  |            40 |   4800 |
|         107 | Diana      | Lorentz    |            40 |   4200 |
|         103 | Alexander  | Hunold     |            60 |   9000 |
|         104 | Bruce      | Ernst      |            60 |   6000 |
|         105 | David      | Austin     |            60 |   4800 |
|         118 | Guy        | Himuro     |            60 |   2600 |
+-------------+------------+------------+---------------+--------+

⚠ Dangerous Trap with NOT IN and NULLs: If the candidate set passed to NOT IN contains a single NULL (e.g. department_id NOT IN (30, 40, NULL)), SQL’s Three-Valued Logic causes the entire predicate to evaluate to UNKNOWN, returning zero rows! Always ensure sets passed to NOT IN have no nulls, or use NOT EXISTS.

---

🔮 4.5 Complex Boolean Logic: AND, OR, NOT & Precedence Rules

Real-world applications combine multiple filtering criteria. SQL provides three boolean logical operators:

• AND: Evaluates to TRUE only when both conditions evaluate to true.
• OR: Evaluates to TRUE when either condition (or both) evaluates to true.
• NOT: Inverts the truth value of the condition that follows it.

The Operator Precedence Trap

Just as multiplication takes precedence over addition in basic mathematics (2 + 3 * 4 = 14, not 20), in SQL AND takes precedence over OR. Failure to understand this rule causes severe data leaks and security bugs:

-- BUSINESS GOAL: Find employees in Department 20 OR 30 who earn over 10000.

-- INCORRECT (Without Parentheses):
-- Because AND binds tighter, this is evaluated as:
-- department_id = 20 OR (department_id = 30 AND salary > 10000)
-- It returns EVERY SINGLE EMPLOYEE in Department 20 regardless of salary!
SELECT first_name, last_name, department_id, salary
FROM employees
WHERE department_id = 20 OR department_id = 30 AND salary > 10000;

-- CORRECT (With Explicit Parentheses):
-- Forces the OR condition to be evaluated first:
SELECT first_name, last_name, department_id, salary
FROM employees
WHERE (department_id = 20 OR department_id = 30) AND salary > 10000;

Live Output of the Correct Query:

+------------+-----------+---------------+--------+
| first_name | last_name | department_id | salary |
+------------+-----------+---------------+--------+
| Steven     | King      |            20 |  24000 |
| Neena      | Kochhar   |            20 |  17000 |
| Lex        | De Haan   |            30 |  17000 |
| Den        | Raphaely  |            30 |  11000 |
+------------+-----------+---------------+--------+

---

⚡ 4.6 Sargability & Index Efficiency

A query predicate is called Sargable (Search Argument Able) when the MySQL query planner can use an index on that column to perform an efficient B-Tree seek instead of scanning every single row in the table.

---

🎯 4.7 Hands-On Practice Exercises

Reinforce your understanding with these three practical challenges using our dataset:

1. Challenge 1: Select all employees whose salary is strictly between 4000 and 7000 and who belong to Department 50 or Department 60.
2. Challenge 2: Select all employees who do not work in Purchasing (30) or Shipping (50) and who were hired before 1995.
3. Challenge 3: Select all employees who have a manager (manager_id IS NOT NULL) and earn greater than 10,000.

Solutions:

-- Solution 1:
SELECT employee_id, first_name, last_name, department_id, salary
FROM employees
WHERE (department_id IN (50, 60)) AND (salary BETWEEN 4000 AND 7000);

-- Solution 2:
SELECT employee_id, first_name, last_name, department_id, hire_date
FROM employees
WHERE department_id NOT IN (30, 50) AND hire_date < '1995-01-01';

-- Solution 3:
SELECT employee_id, first_name, last_name, manager_id, salary
FROM employees
WHERE manager_id IS NOT NULL AND salary > 10000;

---

❓ Frequently Asked Questions (FAQ)

1. What is the difference between WHERE and HAVING?

The WHERE clause filters individual records before grouping or aggregation occurs. The HAVING clause filters aggregated groups (e.g. HAVING COUNT(*) > 5) after GROUP BY has collapsed rows.

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

No. In the SQL standard query execution order, WHERE evaluates before SELECT produces column aliases. Refer to the underlying column name or repeat the calculation in the WHERE clause.

3. Why does WHERE salary != NULL return 0 rows?

Because comparing anything to NULL using equality or inequality operators evaluates to UNKNOWN. To test for missing data, always use IS NULL or IS NOT NULL.