What Is the SQL WHERE Clause?

The WHERE clause is a fundamental part of SQL. This clause filters results and applies conditions to SQL queries. It is most commonly used alongside the SELECT statement. If you’re a complete newcomer to this topic, you can learn the basics of SQL, including WHERE, in our SQL Basics course

This article will clearly explain how to use WHERE in a SQL query. After reading it, you’ll have the confidence and knowledge to integrate WHERE into your daily work.

SQL WHERE Clause Syntax

The syntax of SQL’s WHERE clause is simple to understand. It must come after the FROM keyword in your SQL query:

SELECT *
FROM table
WHERE id = 100

In this example, there is an integer column called id. We are selecting all rows where the value of the id column is equal to 100.

SQL WHERE Clause Examples

One of the best ways of learning SQL is by using it, so let’s work through some different examples of using the WHERE clause. You can observe how useful it is for real-world situations!

Imagine we operate and own a gym. When people sign up as gym members, they give us some basic information. This is stored in our members table, which contains a unique ID for each member, their first and last names, their age, and the date they joined the gym.

If you are completely new to SQL or if you need to refresh your knowledge of the SELECT statement, take a moment to read this article. SELECT will appear throughout this piece, so it is important that you understand it.

Using WHERE with Numbers

If we want to retrieve the details of one of our members and we know their unique id, we could write a query like this:

SELECT * 
FROM members 
WHERE id = 3

This would retrieve all the data for this member, Tom Hanks:

Using WHERE with Text

The previous example used WHERE in conjunction with a numerical value from our id column. However, you can use WHERE with many different data types. Let’s look at an example using the WHERE clause with a text value. In this case, we’ll look in our VARCHAR columns, FirstName and LastName.

SELECT * 
FROM members 
WHERE FirstName = 'Tom' AND LastName = 'Hanks'

Notice how the text values we want to match are surrounded by single quotes. You must do this every time you include a text value in your query. Executing this query returns the following data:

Fantastic! This is exactly the result we wanted.

Look at the query again. Did you notice the AND operator? The WHERE clause is often combined with the use of three different SQL operators – AND, OR, and NOT:

• A WHERE clause with AND requires that both conditions are true.
• A WHERE clause with OR requires that either condition is true.
• A WHERE clause with NOT requires that the specified condition is not true.

Try these operators for yourself by modifying the query used above. Notice the effect it has on the data that is returned. The best way to fully understand these operators is to read tutorials dedicated to the m and to practice writing queries that include the AND, OR, and NOT operators. Here are more useful ways you can practice SQL online.

Using WHERE to Get a Range of Values

Our next example will look at using comparison operators (<,>,>=,<=,!=) in conjunction with the SQL AND operator we have just covered. Imagine we want to target everyone in their fifties with a promotional offer. How will we isolate those members with an Age value between 50 and 59? Look at the SQL query below:

SELECT * 
FROM members 
WHERE Age >= 50 AND Age < 60

Pay special attention to the syntax that follows the WHERE keyword.

This SQL query, translated into plain English, simply reads, “Select all from members where age is greater than or equal to 50 and age is less than 60”. Executing it yields this result:

There we have it! All of our members that are in their fifties have been returned. We can now target these members with our upcoming special offer.

These are just some of the ways you can use the SQL WHERE clause to help you narrow down your queries. One of the most important things in SQL is having the ability to filter through vast amounts of data; the WHERE clause helps you accomplish this.

Using WHERE with UPDATE

The WHERE clause can also be combined with the UPDATE and DELETE statements. UPDATE is used to modify existing records in your database. The DELETE statement is used to completely remove a record from your database.

So, how does using UPDATE in conjunction with the WHERE clause actually work? Imagine that gym member Angelina Jolie has recently married and has taken her husband’s surname. We need to update her last name in our database. To do this, you’d find the id for Angelina Jolie and use it in your query:

UPDATE members SET LastName = 'Clooney' WHERE id = 4

The column you would like to update follows the SET keyword. It is very important that you include the WHERE clause when using the UPDATE statement; if you do not, it will set the LastName to ‘Clooney’ for all the records in the table.

Now let’s see the effect this query has had on our dataset by executing the following query:

SELECT id, FirstName, LastName 
FROM members 
WHERE id = 4

Success! As you can see, the data was updated correctly. UPDATE is a powerful tool and allows you to update any incorrect values in your dataset.

Using WHERE with DELETE

What if we wanted to remove a record from our database table entirely? Using WHERE in combination with the DELETE statement makes this possible. The syntax for DELETE is simple, and when applied to our example it looks as follows:

DELETE FROM members 
WHERE id = 4

We use our WHERE clause to specify exactly what record we want to delete. It is important to note that if several records have the same id value (in this case, 4), all of those records would be deleted from the table.

Be very careful when using the DELETE statement; it is a good idea to always SELECT the records first, so you know exactly what you are deleting.

We can see the effect of our DELETE statement by selecting all of our records:

SELECT * 
FROM members 

Executing this SQL query yields the following result set:

Find Exact Data with SQL’s WHERE Clause

You now have the ability to find and interact with the exact data you want. WHERE is an extremely powerful tool in your arsenal. You can use this clause in conjunction with UPDATE, DELETE, and SELECT. This allows you to manipulate and present data in countless ways – the possibilities are endless!

If you want to continue learning SQL using an effective and proven resource, consider our SQL Basics course, which will equip you with a fundamental knowledge of SQL.