Filtering with WHERE

35 min

The `WHERE` clause is used to filter records, extracting only those that fulfill specified conditions. WHERE is one of the most important SQL clauses, enabling you to retrieve specific data from large tables. Without WHERE, queries return all rows, which is inefficient and often unnecessary. Understanding WHERE is essential for writing effective queries. WHERE comes after FROM and before GROUP BY in query execution order.

Comparison operators (`=`, `>`, `<`, `>=`, `<=`, `<>`, `!=`) compare column values to specified values. These operators work with numbers, dates, and strings (with string comparison rules). `=` tests equality, `<>` or `!=` tests inequality, and comparison operators (`>`, `<`, etc.) work with ordered data types. Understanding comparison operators enables you to filter data precisely.

The `BETWEEN` operator selects values within a range (inclusive). `WHERE column BETWEEN value1 AND value2` is equivalent to `WHERE column >= value1 AND column <= value2`. BETWEEN works with numbers, dates, and strings. Understanding BETWEEN helps you write concise range queries.

The `LIKE` operator performs pattern matching using wildcards. `%` matches any sequence of characters (including zero characters), and `_` matches exactly one character. `LIKE 'A%'` matches strings starting with 'A', `LIKE '%ing'` matches strings ending with 'ing', and `LIKE '_at'` matches three-character strings ending with 'at'. Understanding LIKE enables you to search for partial matches.

The `IN` operator allows you to specify multiple values in a WHERE clause. `WHERE column IN (value1, value2, value3)` is equivalent to `WHERE column = value1 OR column = value2 OR column = value3`. IN is more readable and often more efficient than multiple OR conditions. Understanding IN helps you write cleaner queries for multiple value matching.

Multiple conditions can be combined using `AND`, `OR`, and `NOT` operators. AND requires all conditions to be true, OR requires at least one condition to be true, and NOT negates a condition. Parentheses control evaluation order. Understanding logical operators enables you to build complex filtering conditions. Best practices include using parentheses for clarity, understanding operator precedence, and testing complex conditions carefully.

Key Concepts

WHERE clause filters records based on conditions.
Comparison operators (=, >, <, etc.) compare values.
BETWEEN selects values within a range.
LIKE performs pattern matching with wildcards (% and _).
IN allows matching multiple values.

Learning Objectives

Master

Using WHERE clause to filter records
Applying comparison operators for precise filtering
Using BETWEEN for range queries
Using LIKE for pattern matching
Combining conditions with AND, OR, and NOT

Develop

Query design thinking
Understanding how to filter data effectively
Writing efficient, readable WHERE clauses

Tips

Always use WHERE to filter data—avoid retrieving unnecessary rows.
Use BETWEEN for range queries—it's more readable than >= AND <=.
Use LIKE carefully—it can be slow on large tables without indexes.
Use IN instead of multiple OR conditions for better readability.

Common Pitfalls

Forgetting WHERE clause, retrieving all rows unnecessarily.
Not using parentheses with AND/OR, causing unexpected results.
Using LIKE with leading wildcards (LIKE '%pattern'), causing slow queries.
Not understanding string comparison rules, causing unexpected filtering.

Summary

WHERE clause filters records based on specified conditions.
Comparison operators enable precise value filtering.
BETWEEN, LIKE, and IN provide powerful filtering options.
AND, OR, and NOT combine conditions for complex filtering.
Understanding WHERE is essential for effective database queries.

Exercise

Select all customers from the `Customers` table where the `Country` is 'Germany'.

SELECT * FROM Customers
WHERE Country = 'Germany';

Filtering with WHERE