How to Write a WHERE Clause in SQL

We explain how to use the SQL WHERE clause with practical examples. If you have just started learning SQL and want to know how to retrieve or work with only a specific portion of the data stored in your tables, then this article is for you!

The SQL WHERE clause is something you must master if you wish to use SQL for working with data. It is arguably one of the most basic and must-learn constructs of SQL.

In fact, in my experience, I have hardly found any data retrieval or manipulation queries that do not use a WHERE clause. Filtering the details of the employees belonging to a particular department, selecting the orders with values above a certain amount, or getting a list of customers belonging to a specific geographical region, all require you to use the WHERE clause one way or another in your queries.

Of course, these are just some examples. In general, you can use the SQL WHERE clause to filter any rows from your tables.

So, without further ado, let’s jump straight in!

Basic Syntax of an SQL Query With a WHERE Clause

The basic syntax of an SQL query that uses a WHERE clause is:

SELECT <column names>
FROM <table name>
WHERE <conditions>;

The WHERE clause follows the SELECT and the FROM clauses. While the SELECT clause specifies the columns to be returned from the table(s), the WHERE clause contains the conditions that must evaluate to true for a row to be returned as a result. Each condition is evaluated for each row returned from the table(s). SQL has a variety of conditional operators for specifying how to filter the rows you need.

Let me take you through some SQL WHERE examples to explain this better.

The SQL WHERE Clause With a Single Condition

Imagine you work at a multinational company with offices around the globe. The details of these offices are stored in a database, in a table called offices.

The offices table:

Now, say you want a list of the offices in the U.S. You can use a WHERE clause here. Your query looks like this:

SELECT *
FROM offices
WHERE country = 'USA';

The output:

For this query, SQL first evaluates each row and compares the value of the column country to 'USA'. It then returns only those rows for which the condition evaluates to true. In our case, we have three rows where the country is the USA.

(At this stage, if you are not clear on how to write queries, I encourage you to take the SQL Basic course from LearnSQL.com. Not only is it well structured, but it also has some awesome exercises to fuel your learning and growth.)

You can also use comparison operators like >, <, >=, <= and <> for comparing values. For instance, say you want to retrieve only those rows where the office_code is greater than 5.

The query:

SELECT *
FROM offices
WHERE office_code > 5;

The output:

Or, if you want to retrieve all the rows where the territory is not 'NA', the query looks like this:

SELECT *
FROM offices
WHERE territory <> 'NA';

The output:

The SQL WHERE Clause With Multiple Conditions

So far, I have covered very simple examples of queries that illustrate the use of a SQL WHERE clause with a single condition. However, when you write practical, real-life queries, you often use more than one condition to retrieve the results you need.

Using Conditional Operators

SQL has conditional operators AND, OR, and NOT for expanding the number of conditions used in a query. You can connect conditions using the AND operator when you want only the rows that meet all of the conditions. In contrast, you use the OR operator when you want to retrieve every row for which at least one of the conditions is true.

As an example, say you want to return the cities and the countries of all offices in either the UK or France. As you may have guessed, you use the OR operator to do this.

The query:

SELECT city, country
FROM offices
WHERE country = 'UK'
  OR country = 'France';

The output:

Here, SQL returns the rows for which one or both of the conditions are true. In practice, there is a better way to write this query using the IN operator.

Using an IN Operator

The IN operator allows you to specify a list of values to check against. See the following query:

SELECT city, country
FROM offices
WHERE country IN ('UK', 'France');

The output:

Here, all rows whose countries are in the list of the values specified (in our case, the UK and France) are returned.

With an IN operator, you can specify a list of many values, not just two.

You use the NOT IN operator to return the rows whose values are not in the list. For instance, you can get the postal codes of all offices that are not in the JAPAC or EMEA territory.

Using the IN Operator in a Subquery

Another way to use the IN operator is with a subquery to generate a list from another table. To understand this better, imagine you have another table that has the revenue for each office.

The office_revenue table:

You want to know which offices are not generating enough revenue, and you define that to be those with revenue less than 200,000. This means you need to find the cities of offices with less than 200,000 in revenue.

The query:

SELECT city
FROM offices
WHERE office_code IN (SELECT office_code
  FROM  office_revenue
  WHERE revenue < 200000);

The output:

In this query, SQL first processes the subquery to return a list of the office codes for the offices with revenue less than 200,000. The office codes in the offices table are then checked against this list to return the rows matching those in the list.

More Complex Conditions

Let’s try a slightly more complex example.

What if you want to get the office codes and the cities of all offices that are in the JAPAC or EMEA territory, have postal codes that are not NULL, do not have a state specified, and have office codes greater than or equal to 5?

A good approach to writing this query is to isolate the conditions one by one and relate each to the columns available:

• The territory is either JAPAC or EMEA.
• The postal_code is not NULL.
• The state is NULL or blank.
• The office_code is greater than or equal to 5.

Now that you understand the conditions, writing the query becomes straightforward. Note that all of these conditions must be met for a row to be returned.

The query:

SELECT office_code, city
FROM offices
WHERE territory IN ('JAPAC', 'EMEA')
AND postal_code IS NOT NULL
AND (state IS NULL OR state = '')
AND office_code >= 5;

The output:

The conditions are evaluated in the following manner. With the first condition, SQL returns only the rows whose territories are JAPAC or EMEA. Of those rows, it then checks whether the postal code is NULL. Then, it considers the third condition where we use the parentheses. The two conditions in the parentheses are evaluated to give a collective result: it checks the value of the column state and returns true when it is either blank or NULL. Finally, the last condition filters the rows with office_code greater than or equal to 5. Hence, only the Sydney and London offices are returned in the result.

Notice we do not use postal_code <> NULL or state = NULL. Instead, we have used the IS NOT and the IS operators, respectively, for filtering out values with NULL. This is because NULL is something that is unknown or does not exist and therefore can never be equated to another value. SQL does not throw an error if you use, = or <>, but instead, you receive an empty set as result and no rows are returned. This is obviously wrong.

Now, say you have the same requirement, only that you now want to get all the office codes greater than or equal to 4 but less than or equal to 6. You can use the BETWEEN operator for this.

The query:

SELECT office_code, city
FROM offices
WHERE territory IN ('JAPAC', 'EMEA')
AND postal_code IS NOT NULL
AND (state is NULL OR state ='')
AND office_code BETWEEN 4 AND 6;

The output:

The BETWEEN operator checks for a range of values between 4 and 6, including the bounds. Now you see two rows returned: Paris and Sydney.

What You Cannot Use in a WHERE Clause

You cannot use aggregate functions directly in a WHERE clause. This is because the conditions in a WHERE clause are evaluated row by row, whereas aggregate functions work on multiple rows to return a single result.

Consider this example. You want to return all office codes whose revenue is above average. Maybe your first instinct is to write something like this:

SELECT office_code
FROM office_revenue
WHERE revenue > AVG(revenue);

However, this query returns an error:

ERROR 1111 (HY000): Invalid use of group function

Filtering records with aggregate functions can be accomplished by using a subquery. Take a look:

SELECT office_code, revenue
FROM office_revenue
WHERE revenue > (SELECT AVG(revenue)
		     FROM office_revenue);

The output:

In this query, SQL first retrieves the average revenue from the subquery. It then compares that value against the revenue for each row, returning only the office codes with corresponding revenue above the average value.

Ready to Use the SQL WHERE Clause in Your Queries?

Having read through the article, you now have a fair idea of how to use the SQL WHERE clause. The queries provided in this article are basic and are good for a beginner.

If you have recently started your SQL learning journey and wish to expedite your growth, I recommend this track from LearnSQL.com. I prefer this one because of how comprehensive it is for someone new to SQL.

It sets a great foundation for an analyst – or anyone using SQL for that matter – and gives a good base for writing queries you use day to day for gathering insights from data.

But one thing I can’t emphasize enough is the need for hands-on practice and consistency. That cements all the learning. You can start by writing 10-20 queries every day as practice; within 3 months you will have written almost 1,800 queries!

The great thing about SQL is how simple and powerful it is at the same time. So, what are you waiting for? Get your hands dirty straight away!!