19th Jan 2021 6 minutes read What Is the SQL WHERE Clause? Andrew Bone sql learn sql WHERE Table of Contents SQL WHERE Clause Syntax SQL WHERE Clause Examples Using WHERE with Numbers Using WHERE with Text Using WHERE to Get a Range of Values Using WHERE with UPDATE Using WHERE with DELETE Find Exact Data with SQL’s 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. idFirstNameLastNameAgeDateJoined 1WillSmith522020-08-23 2BradPitt562018-06-15 3TomHanks642017-02-27 4AngelinaJolie452019-04-02 5MerylStreep712014-03-21 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: idFirstNameLastNameAgeDateJoined 3TomHanks642017-02-27 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: idFirstNameLastNameAgeDateJoined 3TomHanks642017-02-27 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: idFirstNameLastNameAgeDateJoined 1WillSmith522020-08-23 2BradPitt562018-06-15 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 idFirstNameLastName 4AngelinaClooney 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: idFirstNameLastNameAgeDateJoined 1WillSmith522020-08-23 2BradPitt562018-06-15 3TomHanks642017-02-27 5MerylStreep712014-03-21 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. Tags: sql learn sql WHERE