Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server / SQL Server 2012

Get Ready To Learn SQL Server: 3. How to Filter Your Query Results

Rate me:
Please Sign up or sign in to vote.
4.75/5 (3 votes)
24 Oct 2014MIT5 min read 6.3K   8  
In today’s lesson, you’re going to learn how to filter the results returned from your queries using the WHERE clause.

Introduction

In today’s lesson, you’re going to learn how to filter the results returned from your queries using the WHERE clause. This clause is important as only those records matching the where clause’s conditions are returned in the query results. The objectives of today’s lesson are to:

  • Learn about various condition types, such as Equality, Range, and Membership
  • Comprehensive example with Select, Where, and Order By
  • Successfully complete the exercises

The WHERE Clause

A where clause is a condition that must be in order for row to be returned in a query. A simple example of a Select statement with a where clause is Select CustomerName from Customers Where State=’MI’. In this example, only customers in the state or Michigan are selected.

In order for a row to be returned, the expression State=’MI’ must be true, that is State must equal ‘MI’. In general, a row is included in a result when the where clause condition is true. The condition of a where clause can be made up of one or more tests.

Through the use of Boolean logic, you can combine these tests for complex comparison between one or more columns or expressions. In this lesson, we’ll cover three type of conditions: Equality, Range and Membership.

Equality Conditions

An equality condition tests that a column or expression matches a value. As you may expect, the equals sign is used to form an equality. It the following SQL statement, were returning all Persons whose title is Mr.

SQL
SELECT FirstName,
LastName
FROM   Person.Person
WHERE  Title = 'Mr.'

Other examples of equality conditions include:

  • Quantity = 30
  • UnitPrice = .25
  • City = ‘El Paso’

You may have noticed that numbers are written as is, however text is enclosed in single quotes. You can also incorporate expressions into an equality condition. For example, if your data was inconsistent and states in the database in various capitalization combination like ‘mi’, ‘MI’, and ‘mI’, which are all different values as far as an equality is concerned, you could compare the uppercase version to be safe. To do so, your condition would be

SQL
Upper(State) = 'MI'

That way regardless, of however your state was capitalized, it would always evaluate to all caps and then safely be compared to the proper abbreviation of the state.

Inequalities

Sometimes, you need to exclude a value. To do this, you can use an inequality. The operator for this is <>. Example of inequalities are:

  • State <> ‘MI’
  • Quantity <> 0

If you were to return all persons whose title wasn’t ‘Mr.‘, then your query would be:

SQL
SELECT FirstName,
       LastName
FROM   Person.Person
WHERE  Title <> 'Mr.'

Tip! I remember that <> stands for not equals by seeing that the < and > oppose each other.

Range Conditions

Sometimes, you want to know when a value falls within a range, such as quantities greater than 10. The range conditions we are most interested in are greater than, less than, and between. The operators to perform the various tests are:

  • > (greater than)
  • >= (greater than or equal to)
  • < (less than)
  • <= (less than or equal to)
  • BETWEEN

Consider our Purchasing.PurchaseOrderDetail table. If you want to know orders where the order quantity is more than 10, then test using:

SQL
OrderQty > 10

To include order quantities of 10, test using:

SQL
OrderQty >= 10

This work just as well with text as with numbers. The condition...

SQL
LastName < 'Nixon'

...returns all persons whose last name is before Nixon. To include Nixon, you would write:

SQL
LastName <= 'Nixon'

Thus to find all persons with a last name less than or equal to Nixon, you would write:

SQL
SELECT FirstName,
       LastName
FROM   Person.Person
WHERE  LastName <= 'Nixon'

You can also use expressions. To find all OrderDetails with a TotalPrice more than 100 dollars, your query would look like:

SQL
SELECT PurchaseOrderID,
       PurchaseOrderDetailID,
       ProductID,
       UnitPrice,
       OrderQty,
       UnitPrice * OrderQty AS TotalPrice
FROM   Purchasing.PurchaseOrderDetail
WHERE  UnitPrice * OrderQty >= 100

But what if you want to TotalPrices that fall within 100 and 200 dollars? How can you pull this one off? Where there are actually a couple of ways, but the one we’ll talk about now is the Between operator. The between operator is used in a condition as:

SQL
UnitPrice * OrderQty BETWEEN 100 and 200

As you probably suspect, you can also use it for test as well. If you’re looking for all the states between a and c, you would:

SQL
States BETWEEN 'A' and 'CZ'

Back to our TotalPrice example, the full statement for this query is:

SQL
SELECT PurchaseOrderID,
       PurchaseOrderDetailID,
       ProductID,
       UnitPrice,
       OrderQty,
       UnitPrice * OrderQty AS TotalPrice
FROM   Purchasing.PurchaseOrderDetail
WHERE  UnitPrice * OrderQty BETWEEN 100 AND 200

Note that the BETWEEN operator is inclusive, that is, it includes both the beginning and ending value.

!Trivia: Are some versions of SQL you can use a column alias, such as TotalPrice, in the WHERE clause. Unfortunately, that isn’t the case with SQL Server.

Membership Conditions

The last condition type to learn about in this lesson is the membership type. The membership type allows you to conduct multiple match tests compactly in one statement. For instance, consider if you have a couple of JobTitles you want to include in a query result. In this case, you could use the in operator to find all matches

SQL
JobTitle IN ('Accountant', 'Buyer', 'Stocker')

The above will match or return turn if the JobTitle title is either ‘Accountant’, ‘Buyer’, or ‘Stocker’. To use the IN comparison operator, separate the items you wish to test for with commas and be sure to enclose them in parenthesis. The full SQL statement for our example is:

SQL
SELECT NationalIDNumber,
       OrganizationNode,
       JobTitle
FROM   HumanResources.Employee
WHERE  JobTitle IN ('Accountant', 'Buyer', 'Stocker') 

Comprehensive Example

Now, let's hook it all together with an example to both filter and sort a query. The overall structure for the select statement is:

SQL
SELECT columns
FROM Table
WHERE WhereClause
ORDER BY Columns

In the prior section, the query to select contacts who were owners is:

SQL
SELECT NationalIDNumber,
       OrganizationNode,
       JobTitle
FROM   HumanResources.Employee
WHERE  JobTitle IN ('Accountant', 'Buyer', 'Stocker')

To sort by JobTitle, just add an ORDER BY clause as:

SQL
SELECT   NationalIDNumber,
         OrganizationNode,
         JobTitle
FROM     HumanResources.Employee
WHERE    JobTitle IN ('Accountant', 'Buyer', 'Stocker')
ORDER BY JobTitle

Exercises

It’s important to practice! Use the sample database to answer these questions.

  1. Select PurchaseOrderDetailID, due date, and order quantity for all purchase order detail items whose order quantity is less than 100.
  2. Select PurchaseOrderDetailID, due date, and order quantity for all purchase order detail items whose order quantity is between 1 and 10.
  3. Find all Persons whose first name is “Ken
  4. Find all productIDs from PurchaseOrders 421, 424, 142

Answers to Exercises

Congratulations! You just learned how to filter query results using the where clause. More tutorials are to follow! Remember! I want to remind you all that if you have other questions you want answered, then post a comment or tweet me. I’m here to help you. What other topics would you like to know more about?

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
-- There are no messages in this forum --