Archives

T-SQL Basics - Filtering Records with the Where Clause

All of the queries we’ve looked at so far will return all of the records in a table. This isn’t very efficient. The Where clause filters records out of your result set that you do not want.

For example, suppose we’re only interested in Sales Order Master records for “Acme Rockets Inc.” The following select statement will work:

SELECT SOM.fcustno CustNo
 ,SOM.fcompany CustName
 ,SOM.fsono SalesNo
 ,SOM.fsorev SORev
 ,SOM.fcustpono CustPONo
 ,SOM.forderdate OrderDate
FROM dbo.somast SOM
  where SOM.fcompany = 'Acme Rockets Inc.'

Notice that I have used single quotes around the company name, they are required for character values, but not numerics. Please note that many fields that M2M refers to as numbers are actually not numeric fields. Most record number fields such as Sales Order Number, Purchase Order Number, and Invoice Number are 6 digit character fields even though they may contain only numeric values. Therefore, this is how to write the statement if you only want to return information for Sales Order “Number” 000001.

SELECT SOM.fcustno CustNo
 ,SOM.fcompany CustName
 ,SOM.fsono SalesNo
 ,SOM.fsorev SORev
 ,SOM.fcustpono CustPONo
 ,SOM.forderdate OrderDate
FROM dbo.somast SOM
  where SOM.fsono = '000001'

And/Or Operators

What if we took the above example and wanted to retrieve 2 sales order records? An Or statement will return as true if either expression is true. Therefore, an Or statement like this will retrieve both records.

SELECT SOM.fcustno CustNo
 ,SOM.fcompany CustName
 ,SOM.fsono SalesNo
 ,SOM.fsorev SORev
 ,SOM.fcustpono CustPONo
 ,SOM.forderdate OrderDate
FROM dbo.somast SOM
  where SOM.fsono = '000001'
  or SOM.fsono = '000002'

Let’s say you wanted to check for records for Acme Rockets Inc. with a particular Sales Order Number. The And statement will only evaluate to true if both conditions are true.

SELECT SOM.fcustno CustNo
 ,SOM.fcompany CustName
 ,SOM.fsono SalesNo
 ,SOM.fsorev SORev
 ,SOM.fcustpono CustPONo
 ,SOM.forderdate OrderDate
FROM dbo.somast SOM
  where SOM.fsono = '000001'
  and SOM.fcompany =  'Acme Rockets Inc.'

I recorded a quick video to explain the gotchas of using And with Or in a Where clause.

Parenthesis Use

As I mentioned in the video, parenthesis can be used to modify expression evaluation in a Where clause. Suppose you wanted to run the previous query for two separate sales order numbers. In English we are looking for any Acme orders with an order number of ‘000001’ or ‘000002’ that are for Acme Rockets Inc.

SELECT SOM.fcustno CustNo
 ,SOM.fcompany CustName
 ,SOM.fsono SalesNo
 ,SOM.fsorev SORev
 ,SOM.fcustpono CustPONo
 ,SOM.forderdate OrderDate
FROM dbo.somast SOM
  where (SOM.fsono = '000001' or SOM.fsono = '000002') -- evaluated as a group
  and SOM.fcompany =  'Acme Rockets Inc.'

Greater Than, Less Than, Not Equal To

Where clauses are not limited to equal conditions. Not Equal To (<> , !=), Greater Than or Equal To (>=) Less Than or Equal To (<=) and simple Greater Than or Less Than are all supported. So, the previous query could also be written:

SELECT SOM.fcustno CustNo
 ,SOM.fcompany CustName
 ,SOM.fsono SalesNo
 ,SOM.fsorev SORev
 ,SOM.fcustpono CustPONo
 ,SOM.forderdate OrderDate
FROM
  dbo.somast SOM
where SOM.fsono <= '000002'
and SOM.fcompany =  'Acme Rockets Inc.'

It’s important to note that since somast.fsono is a character field the less than or equal to is treated a like an alphabetical lookup rather than a numeric one. Typically this isn’t a problem because the numbers consistently include preceding zeros, but it is something to keep in mind.

The Between Statement

Suppose we wanted to return all of the Sales Order Numbers between ‘000001’ and ‘000100’. Well, we’d use the Between statement of course.

Where fsono between '000001' and '000100'

Keep in mind that the statement is inclusive meaning that the first and last value are also returned. If you didn’t want to include ‘000100’ you would need to edit the last value or use a less than instead. The most common use of between is with date comparison and there are gotchas with that. I’ll explain date manipulation and comparison in a later blog article.

The IN Operator

Let’s say that you want retrieve Sales Orders from several of Acme’s subsidiaries. In this example, you want to extract records for “Acme Rockets Inc.” and “Acme Rocket Skates” as well. You could use the following Where clause:

Where SOM.fcompany = 'Acme Rockets Inc.' or SOM.fcompany = 'Acme Rocket Skates'

However, suppose Acme has 20 subsidiary companies all with some version of Acme in the name. Using Or would be unwieldy. Therefore, you can use the In statement like this:

WHERE
  SOM.fcompany IN
    ('Acme Rockets Inc.'
    ,'Acme Rocket Skates'
    ,'The Acme Company'
    ,'Acme Inc'
    ,'A Company Making Everything'
    ,'Acme'
    ,'Acme Rackets Inc.'
    ,'Acme Rickets Inc.')
    -- etc.

While this is better, listing 20 companies this way would still be a pain. So, for this case, we’re going to use the Like statement.

Using Like and Pattern Matching

The most commonly used wild card character is %. % matches any string of characters or no characters at all. For the sake of argument, let’s say that all of the variations of Acme actually started with the word Acme. They could be returned like this:

WHERE
  SOM.fcompany LIKE ('Acme%')

Returns:
CustName
Acme Rockets Inc.
Acme Rocket Skates
Acme Inc
Acme
Acme Rackets Inc.

Notice that we lost “The Acme Company”. If you wanted to include them the following statement will work.

WHERE
  SOM.fcompany LIKE ('%Acme%')

Returns:
CustName
Acme Rockets Inc.
Acme Rocket Skates
The Acme Company
Acme Inc
Acme
Acme Rackets Inc.

Use a preceding % sparingly because the statement becomes non-Sargable. Basically that query will be unable to use an index placed on the field and may cause performance problems.

The astute reader will notice that we missed the Acme company named “A Company Making Everything”. How can we make sure they are returned as well? More than one wildcard character can be used.

WHERE
  SOM.fcompany LIKE 'a%c%m%e%'

Returns:
CustName
Acme Rockets Inc.
Acme Rocket Skates
Acme Inc
A Company Making Everything
Acme
Acme Rackets Inc.
Acme Rickets Inc.

Important to note. You will notice that my last wildcard statement is in a different case than the records in the table. Depending on the collation setting of your database, your results may vary as some are case sensitive.

An Underscore (_) replaces a single character.

WHERE
  SOM.fcompany LIKE 'Acme R_ckets Inc.'

Returns:
CustName
Acme Rockets Inc.
Acme Rackets Inc.
Acme Rickets Inc.

Let’s raise the complexity a notch. Brackets [] can also be used to match a single character in a range.

WHERE
  SOM.fcompany LIKE 'Acme R[a-i]ckets Inc.'

Returns:
CustName
Acme Rackets Inc.
Acme Rickets Inc.

The range can also represent distinct letters.

WHERE
  SOM.fcompany LIKE 'Acme R[a,o]ckets Inc.'

CustName
Acme Rockets Inc.
Acme Rackets Inc.

Brackets can also exclude characters in a range. The symbol ^ (typically over the 6 key) represents Not.

WHERE
  SOM.fcompany LIKE 'Acme R[^a-i]ckets Inc.'

Only Acme Rockets Inc is returned. Brackets and underscores are pretty powerful, but aren’t used nearly as frequently as the general wildcard %.

Using Not

I’ve already eluded to the use of Not with wildcards. However, it’s easy to use in non-wildcard clauses as well. Not negates any expression that it precedes. Using the sample database the following query:

SELECT
 SOM.fsono SalesNo
FROM
  dbo.somast SOM
WHERE
  SOM.fcustno = '000104' AND
  SOM.fcompany NOT LIKE ('Acme Rockets Inc.')

Returns:
SalesNo
000003
000020
000052

That about covers the basics of filtering records using a Where clause. In future articles I’ll discuss Joins, Grouping, and the use of Sub Queries.

Any questions? Is there anything in particular you’d like me to cover?

5 comments to T-SQL Basics – Filtering Records with the Where Clause

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>