How to Write SQL Queries | Filter Rows with WHERE
In previous posts, we introduced some database fundamentals, introduced the basics of a SELECT Statement and discussed column expressions. In this post, we are going to begin to look at chow we can filter the rows returned in a query using a WHERE Clause
You can read the previous posts here
The Basics of a SELECT query
In the previous post, we introduced the syntax for a SELECT Statement you can find this below
SELECT <Column Expression>,...,<Column Expression> | *
FROM <table> [JOIN <table> ON <join condition>]
WHERE <filter condition>
GROUP BY <column>,...,<column>
HAVING <filter condition>
ORDER BY <column>,...,<column>
In this post, we will discuss the WHERE clause
The WHERE Clause – Filter Rows with WHERE
When you use where a clause you are filtering the rows returned by your query. You specify a condition in the where clause, if a row meets the condition it is included in the result set. If it doesn’t satisfy the condition it is not returned. There WHERE can also be referred to as a restriction.
All sorts of conditions are possible, it is very common to test on the primary key. So, if you were looking for a specific customer using our customer example of previous posts, you can filter on the primary key to get that specific customer.
So, let’s look at an example
SELECT CustomerID
,FirstName
,LastName
FROM [SalesLT].[Customer]
WHERE CustomerID = 19
The query returns the CustomerID, Firstname and LastName columns only for the Customer(s) whose CustomerID = 19. CustomerID is the primary key, if there is a customerID 19 in the dataset there will only be one row with that value and it is returned.
WHERE Clause Operators
There are numerous operators that can be used to build your WHERE clause and reduce the rows returned by your query, including:
- You can test for equality =
- You can test for inequality <>
- Greater than <
- Less than >
- Greater than or equal >=
- Less than or equal <=
- We can test for several values using IN and NOT IN
- Search intervals using BETWEEN
- IS NULL and IS NOT NULL can be used to filter on NULLs
- Wildcard searches using LIKE
We can combine and negate conditions using AND OR and NOT
Other Filter Rows with WHERE Clause Examples
The customer table in our examples has 847 rows of customer data.
The following query will return all customers whose customerID is greater than or equal to 19 and less than 23
SELECT CustomerID
,FirstName
,LastName
FROM [SalesLT].[Customer]
WHERE CustomerID >= 19
AND CustomerID < 23
You can see that four rows meet this condition and are included in the results.
The second example in the section returns all rows whose modified date falls in September 2007
SELECT CustomerID
,FirstName
,LastName
,ModifiedDate
FROM [SalesLT].[Customer]
WHERE ModifiedDate >= '2007-09-01 00:00:00'
AND ModifiedDate <= '2007-09-30 00:00:00'
You can see that 94 rows satisfy this condition and return results.
Other Useful Information
If you are interested in attending some formal training the check out our Writing SQL Queries training page. If you would like to speak to us directly please use this contact form
Summary
In this post, we have started to look at how you can restrict the number of rows returned in your query. We have done this using the WHERE clause. Restricting the rows returns allows you to focus on the relevant data. It can also help improve performance. The less work the database engine needs to do the fewer resources are needed. Performance is outside the scope of this series. However, having correct indexes in place can help queries with the WHERE clause. More about that in later posts though.
What you need to follow along
If you would like to follow along with demos and examples here then you can download all you need here:
- SQL Server 2019 Developer edition
- SQL Server Management Studio
- AdventureWorksLT2019 – The small version of the AdventureWorks sample database
All of the above are free to download and install. You might need a reasonable specification laptop to run SQL Server but I suspect most commodity hardware could cope these days
Useful Links
Check out our data analysis videos on YouTube
- Database fundamentals – How to Write SQL Queries series.
- Simple SELECTS –Â How to Write SQL Queries series.
- Column Expressions – How to Write SQL Queries series.
A LinkedIn POD – Do you want to join our LinkedIn Community ?
0 Comments