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
JOIN and before
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.yearFROM books bJOIN authors aON books.author_id = authors.idWHERE b.year >= 2020ORDER BY a.name;
Note that we have combined information from two tables (
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
If you want to get an overview first, let’s continue by exploring the operators you can use in the
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.
For numerical values, you can use these comparison operators:
!=(or <>) Not equal to.
<=Less than or equal to.
>=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 salespeopleWHERE 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 salespeopleWHERE 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.
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 salespeopleWHERE 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
To list all salespeople whose commission rate is between 0.10 and 0.14, you can use the following query:
SELECT *FROM salespeopleWHERE 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.
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:
!=(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 salespeopleWHERE 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 salespeopleWHERE 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 salespeopleWHERE 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
SELECT *FROM salespeopleWHERE 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 salespeopleWHERE 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
ANDoperator displays a record if all the conditions are true.
ORoperator displays a record if any of the conditions are true.
NOToperator 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 salespeopleWHERE 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 salespeopleWHERE 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 salespeopleWHERE NOT branch_id = 2;
If you want to get more examples of using
NOT, check out this article.
To build even more complex filtering conditions, you may actually combine multiple conditions using
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 salespeopleWHERE (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!