SQL WHERE Clause For Filtering Data

In this tutorial, we’ll discuss what the SQL WHERE clause is and how to use it. We’ll also talk about multiple conditions by using AND and OR operators. 

The SQL WHERE clause is for filtering data or records. It’s used to create a condition to extract only the necessary records needed. 

Aside from using the WHERE clause in the SELECT statement, we can also use it with the UPDATE, DELETE, and other SQL statements. However, for this tutorial, we’ll only use it in the SELECT statement.

Simple Selection Using SQL WHERE Clause

Without using the WHERE clause in a SELECT statement, it’ll return all the rows from a particular table.

For this example, we only want to get the data with a Gadgets category.

So, we need to use the WHERE clause to limit the results. In this example, we created a condition that it should check the Category column and only return the records if the Category is Gadgets.

Note that when using a string, we need to enclose it with single quotation marks (‘ ‘). 

When using numbers or integers, we don’t need to use single quotation marks. For example, we want to fetch the records where the price is less than or equal to 30.

So, we just need to create a query where it should check the Price column and return those rows with a value of 30 or less than 30. As you can see, we didn’t enclose the number to single quotation marks as well because it’s an integer.

SQL WHERE Clause With The AND Operator

We can also use the WHERE clause with multiple columns together with the AND and OR operators.

The AND operator will work if both conditions are TRUE. So it’s used to get records where multiple conditions are TRUE.

For example, in this scenario, we want the records where the manufacturer is GizmoWorks and the price is less than 20.  

We’ve created multiple conditions with the WHERE clause together with the AND operator as shown in the image.

On our table, we have 2 rows with a GizmoWorks manufacturer. However, the price of the 2nd row is more than 20. Hence, the result will only return the row that returns true to the 2 conditions that we’ve set. 

SQL WHERE Clause With The OR Operator

OR operator means that it should return records if either of the conditions is true. So, if AND only returns a record where both or all of the conditions are true, using OR operator will return a record even if only one condition returns true. 

Let’s say for example that we want to fetch the records where the product name is either SingleTouch or MultiTouch.

We just need to create a query to check the PName column of the Product table. In this query, we used the WHERE clause and the OR operator to return rows that complied to either of the conditions that were set.

Using WHERE Clause in SSMS

Let’s now use the WHERE Clause in SSMS. Here, we’re using a sample database from Microsoft named AdventureWorks2012. You may learn how to set this up by following this tutorial. 

For this example, we’ll fetch the records under the SalesOrderHeader table.

To execute this query in SSMS, just highlight it and click the execute button.

Without using the WHERE Clause, it’ll return all of the records under this table. As we can see, it returned 31,465 rows.

Let’s say we want to filter those records and return only the rows with a TerritoryID of 5. So, let’s use the WHERE clause here to create that condition.

After executing that query, it’ll now return 486 rows instead of 31,465. This is because it filtered the records to those who only have TerritoryID of 5.

Right now, we’re still bringing all of the columns from that table. We can also limit the columns by specifying the columns that we want to see.

Just change the * to the specific column names and execute the query. 

It’ll now give us the specific columns that we’ve set with the same number of rows. 

Let’s now use the WHERE clause with a String value. For example, we want to limit the records based on the PurchaseOrderNumber column value of P014123169936. Since this value has a letter, it’s considered a String even if there are a lot of numbers with it.

Remember that when using a String value, we need to enclose it with single quotation marks. Without the single quotation marks, a red line will appear indicating that there’s an error.

The result will then give us only one row which means that there’s only one data with the purchase order number that we’ve set.

WHERE Clause With AND Operator And Order By Keyword

Let’s have another filter where we want to select records with certain order dates. For this example, we want to select only the records where the order date is greater than or equal to ‘2014-01-01’ and less than or equal to ‘2014-03-31’.

We can also use the Order By keyword here. For this example, we want this to be sorted by OrderDate in descending order.

The results should only display the records that met the two conditions that we’ve set.

We can also recreate that query by using the BETWEEN operator. BETWEEN operator selects a value within a given range. So, it’ll give us the same results if we do it this way. 

WHERE Clause With OR Operator For Multiple Conditions

In this example, we have multiple conditions using the OR operator. We want to select the rows with either 5, 6, or 7 as their territory ID.

The results should display all of the records that met either of the conditions that we’ve set. For this example, it returned 7,225 rows.

Using Functions With WHERE Clause

We can also use functions with the WHERE clause. For example, instead of manually inputting the date value of the OrderDate column, we can use the YEAR function to automatically fetch all of the records with an order date of the year 2014. 

The result gave us 11,761 rows which are the records for 2014.

***** Related Links *****
SQL SELECT Statement: An Introduction
SQL For Power BI Users – New Enterprise DNA Course
Query Folding And Connecting To SQL Server

Conclusion

To summarize, we’ve learned how to use the WHERE clause in the SELECT statement. Again, we can also use it in the UPDATE, DELETE, and other SQL statements. We’ve also learned how to use it to create multiple conditions by using the AND and OR operators.

Moreover, we learned the usage of BETWEEN operator for selecting between a range of values. We can also use the WHERE clause with a function like the YEAR function. Using the WHERE clause is very important to filter some records that we want to have. It’s necessary to reduce the records being returned by a query to filter out the unnecessary data that we don’t need. 

All the best,

Hafiz