Wednesday 29 August 2012

A Neat Trick with the IN predicate

I just recently learned this new trick with the IN() predicate in MS SQL. The following example is kept simple so that the logic should be obvious, but the really valuable lesson herein is that the arguments to the IN() predicate could be extended to include any number of columns, and therefore this is a general method for finding a match within any column(s) in a given table.
Here is the T-SQL:

USE AdventureWorks2008R2 -- change the database name to suit your version of the AdventureWorks db

SELECT  LastName
       ,FirstName
       ,MiddleName
       ,Title
       ,Suffix
FROM    Person.Person
WHERE   'Lee' IN ( FirstName, LastName )
ORDER BY LastName
       ,FirstName

In AdventureWorks2008R2, this query returns 9 rows where the LastName is 'Lee' and 100 rows where the FirstName is 'Lee'.

On more small thing worth a note: the formatting is purposeful. It was generated by RedGate SQL Prompt, with an option set to preface the lines with a comma. The native SQL default is to suffix the column names with a comma, but that format can lead to hassles when you re-order the columns. If you have SQL Prompt, you're off to the races, but even if you don't, I would suggest that you format your SQL this way. The compiler doesn't care about white space, so make your life easier by using this formatting.

No comments:

Post a Comment