Class 17 of 58 • Data Manipulation & Querying (DML & DQL)

SQL WHERE Clause: Row Filtering & Comparison Operators

Q: Why can't you use column aliases in the WHERE clause?

Because in the database engine's logical query processing pipeline, the WHERE clause executes in Step 2 (filtering raw table rows), long before the SELECT clause executes in Step 5 (where aliases are created). The engine does not know the alias exists yet when evaluating WHERE.

In production databases holding millions of records, you rarely want to retrieve all rows at once. The WHERE clause specifies the search condition that rows must satisfy to be selected for reading, updating, or deleting. The database evaluates the condition for each row: if the condition evaluates to TRUE, the row is returned; if FALSE or UNKNOWN (NULL), it is discarded.

SQL Comparison Operators Reference

Operator	Meaning	Example Query Expression
=	Equal to	`WHERE course = 'BCA'`
<> or !=	Not equal to	`WHERE status <> 'Inactive'`
>	Greater than	`WHERE gpa > 3.50`
<	Less than	`WHERE salary < 40000`
>=	Greater than or equal to	`WHERE age >= 18`
<=	Less than or equal to	`WHERE balance <= 0.00`

Hands-on Filtering Examples

-- 1. Filter by numeric threshold
SELECT student_id, first_name, gpa
FROM students
WHERE gpa >= 3.80;

-- 2. Filter by exact string match
SELECT student_id, first_name, course
FROM students
WHERE course = 'BCA';

-- 3. Filter by date comparison
SELECT student_id, first_name, enrollment_date
FROM students
WHERE enrollment_date >= '2026-01-01';

The Critical Role of Indexes in WHERE Filtering

When an indexed column is evaluated in a WHERE clause (e.g., WHERE student_id = 105), the database traverses a B+ Tree index in (O(\log N)) time, reading only 2 or 3 disk blocks. Without an index on that column, the engine must perform a Full Table Scan (FTS), inspecting every single row on disk!

BCA Exam Trap: Never write WHERE salary = NULL! Always write WHERE salary IS NULL. We will study SQL three-valued logic in detail in the NULL handling class.

Next, let us combine multiple filter criteria using SQL AND, OR, NOT Logical Operators!

Frequently Asked Questions (FAQ)

1. Why can't you use column aliases in the WHERE clause?

Because in the database engine's logical query processing pipeline, the WHERE clause executes in Step 2 (filtering raw table rows), long before the SELECT clause executes in Step 5 (where aliases are created). The engine does not know the alias exists yet when evaluating WHERE.

2. What is the difference between <> and != in SQL?

Both <> and != mean 'NOT EQUAL TO'. <> is the official ANSI SQL standard operator, whereas != is widely supported across all modern engines. They behave identically.

3. Why does 'WHERE column = NULL' never return any rows?

In SQL three-valued logic, NULL represents an unknown value. Comparing anything to NULL using standard equality (= NULL) always evaluates to UNKNOWN, not TRUE. You must use IS NULL or IS NOT NULL instead.