Filtering Data using the WHERE Clause

Understanding Data Filtering

Database tables usually contain large amounts of data. You seldom need to retrieve every row in a table. More often, you want to extract a specific subset of the data for specific operations or reports. Retrieving just the data you need involves specifying search criteria, also known as a filter condition.

Databases are optimized to perform filtering quickly and efficiently. Filtering data at the database level rather than the client application level dramatically improves performance and reduces network bandwidth usage.

The WHERE Clause

Within a standard SQL query, data is filtered by specifying search criteria in the WHERE clause. The WHERE clause is placed immediately after the table name in the FROM clause.

SELECT product_name, price
FROM products
WHERE price = 3.49;

This statement retrieves two columns from the products table, but instead of returning all rows, it evaluates the condition for each row. Only rows where the price is exactly 3.49 are returned in the final result set.

Comparison Operators

The previous example uses a simple equality test. SQL supports a wide range of conditional operators to evaluate data against known values.

• = checks for exact equality.
• <> or != checks for inequality, returning rows that do not match the specified value.
• < checks if a value is strictly less than another.
• > checks if a value is strictly greater than another.
• <= and >= check for less than or equal to, and greater than or equal to, respectively.

By utilizing these operators, you can establish precise numerical boundaries for your data retrieval.

SELECT product_name, price
FROM products
WHERE price >= 10.00;

Checking for Non-Matches

Sometimes it is more efficient to define what you do not want to see. By using the inequality operator, you can exclude specific records from your results.

SELECT product_name, category
FROM products
WHERE category <> 'Electronics';

This instructs the database to evaluate every row and return those where the category column contains any value other than 'Electronics'.

Filtering Text and Character Data

The WHERE clause is not limited to numerical evaluations. You can also filter records based on text or character data. When filtering by a string, standard SQL requires that you enclose the text value within single quotes.

SELECT first_name, last_name, school
FROM teachers
WHERE school = 'Myers Middle School';

If you omit the single quotes around a text value, the database engine will misinterpret the string as a column name or a SQL keyword, resulting in a syntax error. Numerical values, conversely, should never be enclosed in quotes when performing mathematical comparisons.

Filtering Missing Data

In a relational database, a column might contain no value at all. This absence of data is represented by a special marker called NULL. A NULL value is not equivalent to a zero or an empty space; it signifies that the data is completely unknown or missing.

Because NULL is not a known mathematical value, you cannot use standard comparison operators like the equals sign to find it. Instead, SQL provides a dedicated operator to filter for missing data.

SELECT customer_name, email
FROM customers
WHERE email IS NULL;

Understanding how to isolate NULL values is a fundamental skill for maintaining data integrity and identifying incomplete records within your tables.

Clause Placement

When combining multiple clauses in a single SELECT statement, the sequence is strict. The WHERE clause must always follow the FROM clause. If you also need to sort your filtered data, the ORDER BY clause must be placed after the WHERE clause.

SELECT product_name, price
FROM products
WHERE price < 20.00
ORDER BY price DESC;

Placing an ORDER BY clause before a WHERE clause violates SQL syntax rules and will prevent the query from executing. The database logically filters the rows first, and only sorts the surviving rows before presenting them.

Apply these conditional filtering techniques to extract precise subsets of data from your tables.