Basic Filtering

Retrieving every row from a table is rarely useful in a production environment. To find specific information, we use the WHERE clause to filter data based on specific criteria.

Definition: The WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified condition. It acts as a gatekeeper that processes rows one by one (logically) and only allows those that evaluate to TRUE to pass through to the next stage of the query.

Context: Why Filter?

Filtering is crucial for two main reasons:

1. Relevance: Users only want to see data that matters to them (e.g., “My Orders” vs. “All Orders”).
2. Performance: Reducing the volume of data early in the query process (as close to the FROM clause as possible) significantly reduces CPU and memory usage.

Detailed Explanation: The Predicate

A condition in a WHERE clause is known as a predicate. A predicate compares an expression (like a column name) with a value or another expression using an operator.

Basic Comparison Operators

Operator	Description	Example
=	Equal to	Department = 'Sales'
<> or !=	Not equal to	Status != 'Cancelled'
>	Greater than	Price > 100
<	Less than	Stock < 10
>=	Greater than or equal to	Age >= 18
<=	Less than or equal to	Weight <= 50.5

Filtering on Different Data Types

• Strings: Must be enclosed in single quotes (e.g., 'New York'). Note that some databases are case-sensitive ('apple' != 'Apple').
• Numbers: Should not be quoted (e.g., 500 or 19.99).
• Dates: Usually written as strings in ‘YYYY-MM-DD’ format (e.g., '2023-01-01').

Example: Filtering a Dataset

Consider an Inventory table:

ItemID	ItemName	Category	Quantity	LastRestock
1	Widget A	Tools	50	2023-10-01
2	Gadget B	Electronics	5	2023-11-15
3	Widget C	Tools	0	2023-09-20
4	Sprocket	Parts	120	2023-12-01

Case 1: Finding Out-of-Stock Items

Equality Filter

SELECT ItemName
FROM Inventory
WHERE Quantity = 0;

Case 2: Finding High-Stock Parts

Comparison Filter

SELECT ItemName, Quantity
FROM Inventory
WHERE Quantity > 100;

Output

Case 1:

ItemName
Widget C

Case 2:

ItemName	Quantity
Sprocket	120

Under the Hood: The Full Table Scan

When you run a query with a WHERE clause on a column that is not indexed, the database must perform a Full Table Scan.

Imagine a phone book with 1 million names, but it’s not sorted alphabetically. To find “John Doe,” you would have to look at every single page. This is what the database does.

1. It reads a block of data from the disk into memory.
2. It iterates through every row in that block.
3. It evaluates the WHERE condition.
4. If TRUE, it adds the row to the result set.

As we will learn in Module 8, Indexes allow the database to skip this scan and jump straight to the relevant data, much like an index at the back of a textbook.

Caution

Filtering is powerful, but inefficient filtering on massive tables without indexes can lead to slow applications and high server costs.