SQL WHERE Clause for Filtering Database Results

SQL remains the industry standard for managing structured relational databases, thanks to its system of simple declarative statements that can retrieve data from multiple database tables. SQL’s query structure helps keep data secure in databases and allows users to refine search results for accurate, efficient returns of result sets that match the parameters of the query. Learning the structure of SQL statements is the first step toward mastering this versatile programming language.

Like any language, SQL consists of many elements that you can modify in multiple ways to refine the results of a query to the database. One of these is the clause, a key part of SQL statements and queries. Clauses might be optional in some cases, but when used, they establish the conditions for searching the database and returning the correct result.

The SQL WHERE clause is an SQL fundamental — one that students learn very early in any SQL course or training. The WHERE clause is a flexible, powerful tool that refines and filters the results of all SQL statements, including the frequently used SELECT, INSERT, UPDATE, or DELETE. Learning how to use the WHERE clause is one of the first steps in writing effective SQL queries.

The main SQL statement for retrieving data is SELECT. This statement contains a FROM clause, which tells SQL where to get the requested data. The FROM clause is the first one parsed when SQL processes the query, and it produces the result set for the SELECT statement. 

Yet without more refinement, the SELECT statement might return more loosely qualified data than what the user desires. In that case, the statement needs to be more specific about what it requests. The way to refine the result set produced by the FROM clause is to apply a filter, and that’s what the WHERE clause does.

The SQL WHERE clause is a filter that tightens the selected criteria so it returns only the data that meets the stricter requirements of this more specific request. It points the query to the exact place in the database where the result resides.

Here’s an everyday example. When you’re using an online search engine, the search engine returns results based on the keywords you enter. The less specific the keywords, the larger the result set the search engine will return. To get the desired results, you’ll need to narrow the search with more specific keywords or apply filters for things like language or media type. That helps the search engine return a smaller set of results that’s more focused and relevant. 

This is the same principle that applies when the FROM clause follows the WHERE clause. By targeting a more specific subset of data, it filters and refines the results.

Here’s an example scenario using a table of vendors in several cities from around the world:

Now, let’s suppose the objective is to retrieve data from a specific vendor named “Apple.”  We might begin this way:

SELECT * FROM Vendors (the main data table)

Here, the wildcard symbol * designates all columns so that the SELECT statement requests all the columns from the Vendors table. But we’re only interested in the data from the vendor “Apple.”

In that case, the SELECT statement is very inefficient. It has returned everything in the table, not just the target data relating to “Apple.” To get that data, the SELECT criteria will need to be more focused. That’s why we need to add the WHERE clause—to filter the result set so that it contains only the rows with the requested vendor name = “Apple.” Now the query looks like this:

SELECT *
FROM Vendors
WHERE vendor_name = ‘Apple’;

Now the WHERE clause sets a condition that will refine and filter the result set, returning only data related to “Apple.” In this sequence, SQL still parses and processes the FROM clause first as it did before, which returns the full result set. But with the addition of the WHERE clause, SQL also applies its conditions to the result set for further filtering.

Because SQL has also processed the WHERE clause, each row of data is compared to the condition vendor name = “Apple” and tested if true or matching the conditions specified by WHERE. Only rows returning this true condition will pass through the WHERE filter. Rows that don’t contain the vendor name = “Apple” condition will return as false, so they are filtered out. With the added filtering of the WHERE clause, this new, refined SQL query contains only the row that matches the vendor name “Apple,” which was the original query objective. That results in this data set:

In this example, SQL tested each row to determine a match with the WHERE clause. But what would happen if the objective were to return all the vendors EXCEPT “Apple”? In that case, the test would need to be on the WHERE condition returning false. There are two ways to do this using the WHERE clause combined with a negative condition that excludes “Apple” from the query:

1. Use the not condition: WHERE NOT vendor_name = ‘Apple’.
2. Use the not equal to construction:  WHERE vendor_name < > ‘Apple’.

This is a simple illustration of the use of the WHERE clause. Here, a single condition was sufficient to focus the result set and return the desired data, but in actual practice, the WHERE clause can be either simple or complex. Depending on the circumstances, the WHERE statement can be refined with a variety of different conditions to narrow the results even further.

Using WHERE with AND

WHERE can be refined with AND, an operator that combines two conditions that must both be true to return the desired result.

SELECT *
FROM Vendors
WHERE vendor_city = 'seoul'
AND vendor_id > 100;

In this example, the query returns a result for the vendors that match true both for being in Seoul and for having a vendor ID of greater than 100. From the data table in our previous example, that would yield the result:

Using WHERE with OR

One can also use WHERE with OR, a condition that filters the result to match true for either of two alternate options.

SELECT vendor_id
FROM Vendors
WHERE vendor_name = 'Samsung'
OR vendor_name = 'Apple';

In this example, the criteria is for vendors that have either the name Samsung or the name Apple, so both will appear in the results.

WHERE Combined With AND and OR

The WHERE clause can also operate with multiple conditions in more than one category.

SELECT *
FROM Vendors
WHERE (vendor_city = 'Seoul' AND vendor_name = 'Samsung')
OR (ranking <= 10);

In this case, WHERE combines both the AND and OR conditions, and this returns two results—vendors that match true to the location Seoul and the vendor name Samsung, and also vendors that have a ranking above 100, like this: 

Using WHERE With Multiple Conditions

When building a statement in SQL, you can also use a variety of other conditions or comparisons in the WHERE clause. These include the following relational operator conditions:

• IN or BETWEEN … AND, two operators that specify locations in the database
• IS NULL or IS NOT NULL, two operators that test for empty values
• LIKE or MATCHES, operators that filter the search for a specific pattern stated in the query

Creating Sub-Queries with WHERE

You can narrow the query results even further by adding another SELECT statement within the WHERE clause. This is a sub-query, and it looks like this:

SELECT *
     FROM Vendors
     WHERE vendor_id IN (SELECT vendor_id
                  FROM Vendors
                  WHERE ranking > 10) ;

In this sub-query example, a SELECT statement appears inside the WHERE clause and is compared with the condition IN. That means that all the vendor columns (*) in the initial FROM clause are tested to check if their Vendor id fields show up in the sub-query results, which include only the vendor ids with a ranking higher than 10.

A sub-query can include other WHERE clause operators, including:

• IN or EXISTS
• ALL, ANY or SOME

All these operators offer different ways to refine the result set of any query to the database.

Refine Queries Further with ORDER BY

The WHERE clause is just one of the essential clauses that allow SQL users to filter results in structured databases. The SQL ORDER BY clause is another tool for refining SQL queries. ORDER BY follows WHERE in an SQL statement. It’s useful for sorting the resulting data by either ascending or descending order, based on one or several columns in the data table. Read more about using ORDER BY here.

SQL statements account for most of the actions performed on structured, relational databases, so it’s helpful to become familiar with the most common SQL commands. Here’s an overview of the most useful SQL commands and how they operate with combinations of clauses and conditions to achieve the desired results. 

SQL remains the standard query language for structured databases, and the WHERE clause is a powerful tool for refining basic SQL commands for greater efficiency and more precise results. Combined with other operators and clauses, the WHERE clause modifies the DELETE statement and SELECT statement to return SQL data sets that accurately match query parameters and improve the quality of searches in relational databases of all kinds.