The Complete Guide to the SQL WHERE Clause

Learn how to use the SQL WHERE clause to filter rows. In this comprehensive article, we cover comparison operators and the BETWEEN, IN, LIKE, AND, OR, and NOT operators.

Filtering output rows is one of the first things you need to learn when starting your SQL journey. In this guide, we’ll see how to use the SQL WHERE clause to filter rows in different scenarios. We’ll cover basic and some more advanced use cases.

The WHERE Clause in SQL

Let’s imagine you are working on one of your first SQL projects and running your first SQL queries. You already know the basic syntax of the SELECT statement and how to retrieve columns from one or two tables in your database. But then you noticed that very often you don’t need all records from a table. You want to learn how to filter your output so you can return only a few records that meet certain criteria.

Do you want only those records corresponding to a specific customer? Or do you need to retrieve the list of products that are low in stock (e.g. less than 10 items)? Or maybe you want to get a list of sales representatives who work in branch X and have had above average sales in the last month?

In all these cases, you’ll need the SQL WHERE clause to filter your results. This clause introduces certain conditions, like:

• quantity < 100
• price BETWEEN 100 AND 500
• customer_name = ‘John Smith’.

For the filtering conditions to be executed properly, the WHERE clause should be placed after FROM and JOIN  and before GROUP BY,  HAVING, and ORDER BY.

For example, here’s an SQL query to get information about books issued since 2020, ordered by the author’s name:

SELECT b.title, a.name, b.year
FROM books b
JOIN authors a
ON books.author_id = authors.id
WHERE b.year >= 2020
ORDER BY a.name;

Note that we have combined information from two tables (books and authors) and placed the WHERE clause after the ON joining condition and before the ORDER BY clause. You can learn more about the correct syntax of the WHERE clause in this beginner-friendly guide.

It might also be a good idea to start practicing WHERE right away. In this case, I would recommend our interactive SQL Basics course. Its 129 interactive exercises cover key SQL concepts, including simple and complex WHERE conditions.

If you want to get an overview first, let’s continue by exploring the operators you can use in the WHERE clause.

Operators to Use with SQL WHERE

You can build very basic as well as complex filtering conditions in WHERE thanks to a wide range of operators that can be used for comparing values in SQL. We'll review the key ones that allow filtering by numerical values, strings, NULL values, and a list of values.

Numerical values

For numerical values, you can use these comparison operators:

• = Equal to.
• != (or <>) Not equal to.
• < Less than.
• <= Less than or equal to.
• > Greater than.
• >= Greater than or equal to.

To see how these operators work in practice, we’ll have a few examples. For our examples, we’ll use the following table that includes information on salespeople: ID, first name, last name, annual salary, commission rate, the commission they received in 2021, and their branch ID.

First, we want to get the records of all salespeople whose annual salary is equal or greater than $50K. We can use the following query:

SELECT *
FROM salespeople
WHERE salary >= 50000;

As expected, we got the list of salespeople whose salary is equal to or above $50K.

Next, let’s see how we can use float numbers rather than integers with the comparison operators. We’ll list all salespeople who, thanks to their long experience with the company, have a commission rate above 0.12:

SELECT *
FROM salespeople
WHERE commission_rate > 0.12;

Since we have been using the non-inclusive > operator in the WHERE clause, we’ve got only those salespeople whose commission rate is strictly above 0.12. This excludes the ones whose rate is equal to 0.12.

Also, note that we’ve been using comparison operators with literals (i.e. 50000 and 0.12). When filtering records, we can also use comparison operators with expressions. For example, let’s list the salespeople whose commission earnings in 2021 were greater than their annual salary:

SELECT *
FROM salespeople
WHERE commission_2021 > salary;

The query worked as intended; we see four salespeople that apparently had very high sales in 2021, so that their commission earnings exceeded their salary.

At this point, you should feel more or less comfortable with the comparison operators demonstrated above. It’s time to introduce one more operator you can use with numerical values in WHERE: the BETWEEN operator.

To list all salespeople whose commission rate is between 0.10 and 0.14, you can use the following query:

SELECT *
FROM salespeople
WHERE commission_rate BETWEEN 0.10 AND 0.14;

Note that the BETWEEN operator is inclusive on both the lower and upper bounds, so the result set includes records corresponding to the commission rates of 0.10 and  0.14.

Now, let’s move to the operators you can use with text values.

Text values

First of all, with text values, you can use the following set of comparison operators that work similarly with strings as they do with numerical values but in case of the text values, the records are ordered and compared alphabetically:

• = Equal to.
• != (or <>) Not equal to.
• < Less than (occurs first in alphabetical order, i.e. a < b).
• <= Less than or equal to.
• > Greater than (occurs after in alphabetical order, i.e. b > a).
• >= –Greater than or equal to.

To retrieve information on all salespeople whose last name (when sorted alphabetically) is before ‘Keen’, we’d use the following query:

SELECT *
FROM salespeople
WHERE last_name < ‘Keen’;

These comparison operators work well with text values. However, note that we always include quotation marks with string literals used in the WHERE conditions (e.g. ‘Keen’). Also, while some databases are not case-sensitive by default (e.g. SQL Server, MySQL), others are case sensitive (e.g. Oracle) and wouldn’t return any records if you search for ‘keen’ instead of ‘Keen’.

We often need much more flexibility with strings than with numbers, and that’s when the LIKE operator comes in handy. It allows us to do some advanced filtering with text values, especially when combined with an SQL wildcard (e.g., ‘_’ for one missing character or ‘%’ for any number of characters).

For example, if we want to list all salespeople whose last name starts with K, we can write the following SQL query:

SELECT *
FROM salespeople
WHERE last_name LIKE ‘K%’;

Read this article if you want to learn more about how SQL wildcards can be used for filtering the records with text values in SQL.

Comparisons with NULL values

The combination of comparison operators and NULL values can trip up SQL beginners because of some counterintuitive behaviors. For example, if we test that a certain value is equal to NULL, the result will be unknown even if the column value is NULL. As the WHERE clause requires true conditions, you’ll get zero rows with a condition like the following:

SELECT *
FROM salespeople
WHERE commission_2021 = NULL;

Result:
Query has no result

The solution is to use the IS NULL or IS NOT NULL operators. Here’s how you can retrieve all records that have NULL in the commision_2021 column:

SELECT *
FROM salespeople
WHERE commission_2021 IS NULL;

Check out this guide for more examples of NULL’s behavior with various comparison operators.

IN operator – Checking against a list of values

Finally, you can use the IN operator to check against a predefined list of values. For example, let’s say you have a list of employees whose earnings need to be verified. You can retrieve the necessary records using an SQL query like this one:

SELECT *
FROM salespeople
WHERE last_name IN (‘Kaplan’, ‘Gerard’, ‘Zuma’);

Looks perfect! However, there are often some syntax nuances to be noted when using various operators in the WHERE clause. For your convenience, we have prepared a 2-page SQL Basics Cheat Sheet that includes numerous examples of operators being used for filtering the output of an SQL query.

Combining Filtering Conditions in WHERE

In real-world assignments, having one condition in the WHERE clause is often insufficient. Luckily, you can combine different filtering conditions with the AND, OR, and NOT operators:

• The AND operator displays a record if all the conditions are true.
• The OR operator displays a record if any of the conditions are true.
• The NOT operator displays a record if the corresponding condition is not true.

How these work will become clearer with examples.

To list all salespeople that work in branch #5 and have salaries equal to or greater than $50K, use the following query:

SELECT *
FROM salespeople
WHERE branch_id = 5 AND salary >= 50000;

To retrieve all records where the last name is either ‘Kaplan’ or ‘Reddington’, use the following query:

SELECT *
FROM salespeople
WHERE last_name = ‘Kaplan’ OR last_name = ‘Reddington’;

Finally, to get information on all salespeople except the ones who work in branch #2, use this query:

SELECT *
FROM salespeople
WHERE NOT branch_id = 2;

If you want to get more examples of using AND, OR, and NOT, check out this article.

To build even more complex filtering conditions, you may actually combine multiple conditions using AND, OR, and NOT in the same WHERE statement. However, it’s important to remember the precedence of these operators in SQL: NOT → AND → OR. For better clarity, it’s highly recommended to use parentheses, even if they are not required in a specific case.

Let’s say we want to retrieve all records where an employee’s last name is either ‘Keen’ or ‘Park’, their commission earnings were higher than their salary in 2021, and they are not working in branch #2. Here’s a possible query:

SELECT *
FROM salespeople
WHERE (last_name = ‘Keen’ OR last_name = ‘Park’) 
AND (commission_2021 > salary)
AND (NOT branch_id = 2);

And now we are done with examples! If you need more guidance on the WHERE clause with some additional examples, read more of our beginner-friendly guides. And then, practice, practice, and practice!

Time to Use the SQL WHERE Clause!

The best way to master any new concept in SQL is to try it in multiple queries. For beginners, I think that the most comfortable environment for practicing something new is in interactive online courses – you have all the examples ready for you and can write queries from the comfort of your browser.

To practice SQL WHERE, I would recommend starting with our interactive SQL Basics course. It covers everything  you’ll need to start retrieving data from a database, including writing sophisticated filtering conditions.

For even more practical exercises, check out the SQL Practice learning track. It includes 5 interactive courses with hundreds of coding challenges.

Thanks for reading, and happy learning!