Learning SQL, Part 4 - More Where Statements

This is the fourth article in the learning SQL series:

• Part 1: Setup your environment
• Part 2: Basic Select Statements
• Part 3: Where Statements
• Part 4: More Where Statements (this article)
• Part 5: Aggregations and Group By Statements
• Part 6: Joins

Filtering in SQL: More WHERE Statements

Previous articles reviewed how to setup your database environment, selecting records, and where statements used to filter records. In this article, where statements are expanded on. Similar to prior examples, this article will use the DimCustomer table in our AdventureWorks database.

In Statements

Part 3 reviewed how to write multiple conditions, separating with the AND keyword. However, if there’s a situation where you want to filter a particular column to a list of values, the IN keyword comes in handy. In the below query, the top 10 records are pulled where the FirstName column is Jon or Ian. Notice how in this example, separate AND conditions were not required as the comma separated list was provided within the parenthesis after our IN keyword.

SELECT TOP 10 *
FROM [AdventureWorksDW2017].[dbo].[DimCustomer]
WHERE
	FirstName IN ('Jon', 'Ian')

Like Statements

Another way to filter is using the Like keyword. This provides the ability to perform wildcard searches - useful in situations where you want to match a portion of a text string. Let’s say we wanted to pull some customers where the FirstName column starts with Rob. When using the like keyword, the percent sign is used to denote one or more wildcard characters (underscore is typically used for exactly one wildcard character).

SELECT TOP 10 *
FROM [AdventureWorksDW2017].[dbo].[DimCustomer]
WHERE
	FirstName LIKE 'Rob%'

Not Statements

Reversing some of the prior examples, you can filter records to everything but certain records. This is where the NOT statment comes in. The example below shows pulling 10 records where the FirstName column does not start with Rob. This is not limited to like filters - the NOT keyword can be used in conjunction with any of the prior examples.

SELECT TOP 10 *
FROM [AdventureWorksDW2017].[dbo].[DimCustomer]
WHERE
	FirstName NOT LIKE 'Rob%'

Subqueries

Subqueries will be covered in more depth in a later article; however, these can be used in filter conditions. Let’s say we want to pull all customers who have a last name with a bachelors degree. Maybe we are looking for family members who have a relative that went to college (assuming there are not duplicate unrelated last names). The nuance here is that we don’t just want records with a bachelors degree - but to first find all the last names that have someone with a bachelors degree - then in a “second pass” search the whole customer table for these last names.

SELECT TOP 10 *
FROM [AdventureWorksDW2017].[dbo].[DimCustomer]
WHERE
	LastName IN (
		SELECT 
			[LastName]
		FROM [AdventureWorksDW2017].[dbo].[DimCustomer]
		WHERE
			[EnglishEducation] = 'Bachelors'
	)
ORDER BY
	[LastName]

That’s all for this article. Try some of the above methods on your own to get the feel of using these. Can you think of situations where some of these methods would be useful?

Next Steps

Check out additional articles in this series here:

• Part 1: Setup your environment
• Part 2: Basic Select Statements
• Part 3: Where Statements
• Part 4: More Where Statements (this article)
• Part 5: Aggregations and Group By Statements
• Part 6: Joins