If you are like me, you like to find new ways of doing the same thing in different ways. Perhaps a new way is easier to read. Perhaps the query runs faster. Perhaps it will run slower? Unless you implement the ideas, try them out and perform an Execution Plan along with Timing the results, how would you ever know.

Personally another Indicator I use for coding is ‘Ease of Reading’. I don’t mean for kindergarten children.  If T-SQL were that simple we would not have jobs that pay as well as they are. T-SQL falls in the line of Adult Literacy.  For those of you who just chuckled at those two concatenated nouns, no, I am not referring to the other type of Adult Entertainment’.

 

Below is a highly simplistic form of a query I was creating.  But the point is, I wanted everyone who existed in the first table, but only if they also existed in the second table. So easy, even my Mum could write this.  7 lines of code (9 including Brackets).


SELECT DISTINCT [E].[clientno]  --140,000 Records
FROM [dbo].[episode] AS [E]

WHERE [E].[ClientNo] IN
(
   SELECT DISTINCT [A].[clientno]  --3,100 Records
   FROM [dbo].[CHSP_RelevantEpisodes] AS [A]
)

However, as we all know, the WHERE IN is not a very efficient search. Sure it does the job, but is there a better way? Or even just another way?

Introducing: INTERSECT


SELECT DISTINCT [E].[clientno]--140,000 Records
FROM [dbo].[Episode] AS [E]

INTERSECT

SELECT DISTINCT [E].[ClientNo] --3,100 Records
FROM [dbo].[CHSP_RelevantEpisodes] AS [E]

5 lines of code, No Brackets Required.  In THIS format it is sure a lot easier to read, easier to Error Check and easier to perform Data Integrity Checks.

As far as the Execution Plan goes, the ratio WHERE IN : INTERSECT is 51%:49%, so in this case it is not a deal breaker.

HOWEVER, I am still to figure out what the go is if you need more columns from the first Table. I think I might start testing the Intersect method  over the next few weeks or so.