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!

What is WHERE Clause in SQL?

The SQL WHERE clause filters records based on specific conditions; it allows you to selectively retrieve, modify, or delete data in a database. It is crucial for targeting exact rows in SELECT, UPDATE, or DELETE operations.

In general, you can use the SQL WHERE clause to filter any rows from your tables.

WHERE Clause Syntax

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.

Operators in WHERE Clause

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:

Equality Operator in WHERE

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.)

Comparison Operators in WHERE

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:

Logical Operators in WHERE

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.

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.

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.

Learn More About WHERE

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 our SQL from A to Z track. I prefer it because of how comprehensive it is for someone new to SQL. It consists of 7 courses that that start with the basics and continue through intermediate and advanced topics. 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.