All Articles

Learning SQL, Part 4 - More Where Statements

This is the fourth article in the learning SQL series:

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')
Example 1 Query: Filtering Using the IN Keyword
Example 1 Query: Filtering Using the IN Keyword
Example 1 Results

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%'
Example 2 Query: Wildcard Filters Using the LIKE Keyword
Example 2 Query: Wildcard Filters Using the IN Keyword
Example 2 Results

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%'
Example 3 Query: Not Filters to Exclude Records
Example 3 Query: Not Filters to Exclude Records
Example 3 Results

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]
Example 4 Query: Subquery Filters
Example 4 Query: Subquery Filters
Example 4 Results

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?

Check out these books I recommend for learning Data Analytics and Power BI: