Database Fundamentals #24: More Filtering Data

In this Database Fundamentals post we continue discussing the functionality of the WHERE clause. We started with the basics of the logic using things like AND, OR and LIKE or ‘=’. Now, we’ll expand into some other areas.

Functions in the WHERE clause

SQL Server provides you with all sorts of functions that can be used to manipulate strings, modify dates or times or perform arcane mathematical equations. The problem with these is that if you do them on columns in tables it can lead to performance issues. The trick then, is to not perform functions on the columns in the tables. We’ll cover this in more detail when we get to indexing, variables, and parameters. Just don’t get into the habit of putting functions on the columns in your tables in the WHERE clause.

SELECT Statements Inside the WHERE Clause

Earlier you were shown the IN operator and a list of comma separated values. You can also use a SELECT statement in the WHERE clause with the IN operator or the equals operator and an appropriate query to put them to work:

SELECT ft.TransactionID,
	ft.CompanyID
FROM Finance.FinancialTransaction AS ft
WHERE ft.CompanyID IN (SELECT c.CompanyID
					FROM Management.Company AS c
					WHERE CompanyDesc = 'Aerospace & Defense');

Effectively this is a JOIN, and probably it’s a better construct for this query. But this query does demonstrate the possibility you have with a SELECT statement in the WHERE clause. Like all the other filter mechanisms throughout these posts, you can mix and match them, but understand what they’ll do before you use them, or you will get unexpected results.

The WHERE Clause and OUTER JOIN

Because of how the OUTER JOIN works, if you refer to columns from a table that is the LEFT or RIGHT table in an OUTER JOIN, you may see the data returned looks more like an INNER JOIN. To deal with this issue, when you’re attempting to filter the data returned from a SELECT statement using an OUTER JOIN, you should move the WHERE criteria for that table only into the JOIN criteria so that your query looks more like this:

	SELECT p.LastName,
		ft.TransactionAmount,
		ft.TransactionDate,
		ft.TransactionTime
	FROM Personnel.Person AS p
	LEFT JOIN Finance.FinancialTransaction AS ft
		ON p.PersonID = ft.PersonID
		and ft.TransactionAmount > 14
WHERE p.DateOfBirth < '1/1/1980';

This ensures that the filtering occurs before the LEFT JOIN, not after which would completely change the data set returned.

Conclusion

That wraps up our introduction to the SELECT statement and the WHERE clause. The next post in the series will move back to data structures again where we’ll begin to talk about how to build referential constraints on your tables in order to relate them to one another. These are called foreign keys.