SQL WHERE Clause to Filter Rows for SELECT, UPDATE and DELETE

By: Eric Blinn | Updated: 2022-02-09 | Comments (1) | Related: > TSQL

Problem

I am writing some Microsoft SQL Server T-SQL code and need to limit my query to include only rows that meet certain criteria. I know this means that I need to use the WHERE keyword. How does WHERE work in a SQL SELECT statement? Where in the SQL query do I put the WHERE? What criteria can I use? How do I check many criteria at once?

Solution

This SQL tutorial will describe the WHERE clause of a SQL statement showing many different examples and use cases. All of the demos in this tutorial will be run against Microsoft SQL Server 2019 and reference the WideWorldImporters sample database which can be downloaded for free from Github.

SQL WHERE Condition Basics

WHERE can be applied to DML (Data Manipulation Language) statements such as SELECT, UPDATE and DELETE to tell SQL Server which rows to return, modify, or remove respectively. Almost every SQL Server query will include a WHERE clause as it is rare to return an entire table/view or update every single row of a table.

TThe WHERE clause works by including an argument after the keyword. An argument is a logical operator that evaluates to either true or false. Some examples might be MyNumericColumn < 1 or MyTextColumn = 'Hello'. The WHERE clause will cause the query only affect rows where the argument evaluates to true for that row. The argument will be evaluated independently for every row in the table(s).

Multiple arguments can be included in a single WHERE clause by utilizing the SQL operators AND and OR.

Parentheses can be used to force the order in which multiple arguments are calculated or to treat several arguments as a single argument. This is most common when there are many parameters combined using both AND and OR keywords.

There will be examples of each of these scenarios below.

The T-SQL coding language treats arguments the same way in most situations. This means that while this tip is about the WHERE clause, most of the information regarding arguments, AND and OR, and parentheses would be applicable to HAVING clauses, join predicates, IF statements, or WHILE loops.

Simple SQL WHERE Clause Syntax

IImagine that the users have requested a report based on invoices. A developer may start with the following query:

SELECT InvoiceID, CustomerID, InvoiceDate FROM Sales.Invoices;

This query returns 70,510 rows. That is far too many rows for the users to review. They ask that the report only show a single customer at a time. This calls for a WHERE clause! On a SELECT statement, the WHERE clause is placed immediately after the FROM clause. If there were multiple tables or views in the FROM clause then WHERE would be placed after all tables and join predicate information were completed.

SELECT InvoiceID, CustomerID, InvoiceDate
FROM Sales.Invoices
WHERE CustomerID = 905;

This query result set returns only 113 rows. Notice that rows are all different invoices, but all of them have the same CustomerID of 905.

This screenshot of query output shows 9 results from 9 different orders belonging to customer 905.

Combine 2 arguments using AND for WHERE Clause

Continuing the previous example, the users now want to look at the invoices by quarter. Now a second argument is needed. The CustomerID argument stays and a second argument, based on InvoiceDate, is added. Since the users want both of these arguments to be true, the AND keyword is used.

SELECT InvoiceID, CustomerID, InvoiceDate
FROM Sales.Invoices
WHERE CustomerID = 905
  AND InvoiceDate BETWEEN '2013-01-01' AND '2013-03-31';

This query result set returns only 6 invoices. Every invoice has both CustomerID 905 and an InvoiceDate in the 1^st quarter of 2013.

This screenshot of query output shows the 6 invoices returns by the query with 2 arguments.

Combine 2 arguments using OR for WHERE Clause

At WideWorldImporters, 2 sales people have quit. All of their historical invoices need to be reassigned to a remaining salesperson. This will require an UPDATE statement and 2 arguments – one for each departing sales person. Since the query needs to affect any invoice record where either argument is true, an OR keyword is needed.

On an update statement the WHERE clause goes after the SET clause. If using the optional FROM clause then the WHERE would go after the FROM. This tip will show both variations.

For the purposes of this demo, any queries that modify data will be immediately rolled back to return WideWorldImporters to its original condition.

Either one of these 2 functionally equivalent queries will update all of the invoices for either of the 2 salespeople, numbered 7 and 20, to belong to the salesperson numbered 13. If the OR was changed to an AND then zero rows would be affected as there can be no row where the SalesPersonPersonID is both 7 and 20 simultaneously.

BEGIN TRAN

   --Variation 1, no FROM clause 
   UPDATE Sales.Invoices
   SET SalespersonPersonID = 13
   WHERE SalespersonPersonID = 7
      OR SalespersonPersonID = 20;
 
ROLLBACK

 
BEGIN TRAN

   --Variation 2, an update with a FROM 
   UPDATE TableAlias1
   SET SalespersonPersonID = 13
   FROM Sales.Invoices AS TableAlias1
   WHERE SalespersonPersonID = 7
      OR SalespersonPersonID = 20;
 
ROLLBACK

Using AND and OR together in WHERE Clause

The business users at WideWorldImporters have once again asked for a new report. They want a list of all invoices for the first quarter of 2013 that belong to one of the sales persons 7 or 20. The report writer came up with this query that has 3 arguments.

SELECT InvoiceID, CustomerID, InvoiceDate, SalespersonPersonID
FROM Sales.Invoices
WHERE InvoiceDate BETWEEN '2013-01-1' AND '2013-03-31'
  AND SalespersonPersonID = 7
   OR SalespersonPersonID = 20;

The results, however, were unexpected. They appear to include all the rows required, but also include several others that don't belong as they are not in the first quarter of 2013.

This screenshot of query output shows several orders from August that should not be in the query output.

The reason for this is that any arguments on either side of the OR are calculated separately with the OR handled last. Sometimes, this author stylizes queries this way to emphasize that point.

SELECT InvoiceID, CustomerID, InvoiceDate, SalespersonPersonID
FROM Sales.Invoices
WHERE InvoiceDate BETWEEN '2013-01-1' AND '2013-03-31'
AND SalespersonPersonID = 7
 OR SalespersonPersonID = 20;

What that query is actually asking for is any invoice from the first quarter of 2013 for salesperson 7. Separately, it would like to see any invoice for salesperson 20 – regardless of date. Notice that when executing the query, all of the unexpected invoice rows belong to salesperson 20, not salesperson 7.

This query can be fixed with some parentheses. The next section will do just that.

Using parentheses in WHERE clauses

SQL Server will process any arguments within parentheses and calculate their outcome to a single true or false before moving on to other arguments.

In order to build a list of all invoices for the first quarter of 2013 that belong to one of the sales persons 7 or 20, both an AND and an OR are needed. SQL Server wants to calculate the OR last, but that won't work in this situation as shown in the previous section of the tip. The answer to this problem is to use parentheses to force the OR to be calculated earlier in the process.

This sample query uses parentheses force SQL Server to consider both salesperson arguments first, treating them as a single argument by returning true if the invoice belongs to salesperson 7 or salesperson 20. Then it will further limit the results to only those invoices that are from the first quarter of 2013.

SELECT InvoiceID, CustomerID, InvoiceDate, SalespersonPersonID
FROM Sales.Invoices
WHERE InvoiceDate BETWEEN '2013-01-1' AND '2013-03-31'
  AND (SalespersonPersonID = 7 OR SalespersonPersonID = 20);

Using WHERE on DELETE statements

WideWorldImporters has declared that all vehicle temperature readings over 6 months old are no longer needed and can be deleted. The DBA needs to make a DELETE statement to remove these rows. It can then be executed once per week as part of a SQL Server Agent job.

When writing a DELETE statement, the WHERE clause goes after the table is listed. Just like an update statement, the WHERE will go after an optional FROM clause. Here are both examples.

BEGIN TRAN
 
   DELETE [Warehouse].[VehicleTemperatures]
   WHERE RecordedWhen < DATEADD(m, -6, GETDATE());
 
ROLLBACK

 
BEGIN TRAN
 
   DELETE TableAlias2
   FROM [Warehouse].[VehicleTemperatures] AS TableAlias2
   WHERE RecordedWhen < DATEADD(m, -6, GETDATE());
 
ROLLBACK

Final Thoughts

The WHERE clause is a T-SQL tool that every T-SQL developer will use many times per day -- every day. Mastering it is a must!

Next Steps

Learn SQL with these resources

About the author

Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-02-09

SQL WHERE Clause Explained