<\/p>\n No difference there, results are the same. I’m not going to run the test for EXISTS because, if anyone remembers how EXISTS works (or remembers a blog post<\/a> I wrote a while back), EXISTS doesn’t depend on what’s in the SELECT clause at all, it just looks for existence of rows, and DISTINCT cannot remove unique rows, just duplicates.<\/p>\n A look at the execution plan shows why there are no duplicate values returned in the first query (the one without DISTINCT).<\/p>\n That’s a semi-join there, not a complete join. A semi-join is a join that just checks for matches but doesn’t return rows from the second table. Since it’s just a check for existence, duplicate rows in the inner table are not going to make any difference to the results.<\/p>\n So that answers the correctness aspect, distinct is not necessary to get correct results. But does it improve performance by having it there? Or does it perhaps reduce the performance? Time for larger tables.<\/p>\n Stolen from my last look<\/a> at EXISTS and IN:<\/p>\n Some row counts first.<\/p>\n First test is without indexes on the lookup columns:<\/p>\n The reads are identical, which shouldn’t be a surprise as there’s no way with the current tables to run those queries without doing a full table scan.<\/p>\n Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0. For durations and CPU, I’m going to run each 10 times and aggregate the results from the profiler T-SQL:BatchCompleted event. The results are just about identical.<\/p>\n Furthermore, the execution plans are identical. Something interesting to notice in this case is that the join is not a semi-join, it’s a complete join and to ensure that the complete join doesn’t return duplicate rows (which would be incorrect), there’s a hash match (aggregate) right before the join that’s removing duplicate rows from the inner resultset, and that’s present in both execution plans, when the distinct is specified and when it’s not.<\/p>\n One last question to answer – does the presence of indexes change anything?<\/p>\n The execution plan has changed, in operators if not in general form. The hash join is replaced by a merge join (still a complete join, not a semi-join), the hash match (aggregate) has been replaced by a stream aggregate and the clustered index scans are now (nonclustered) index scans<\/p>\n![\"DistinctIN\"](\"https:\/\/www.sqlinthewild.co.za\/wp-content\/uploads\/2010\/12\/DistinctIN.png\" "\\"DistinctIN\\"")
<\/a><\/p>\n<\/a><\/p>\nCREATE TABLE PrimaryTable_Large (\r\nid INT IDENTITY PRIMARY KEY,\r\nSomeColumn char(4) NOT NULL,\r\nFiller CHAR(100)\r\n);\r\n\r\nCREATE TABLE SecondaryTable_Large (\r\nid INT IDENTITY PRIMARY KEY,\r\nLookupColumn char(4) NOT NULL,\r\nSomeArbDate Datetime default getdate()\r\n);\r\nGO\r\n\r\nINSERT INTO PrimaryTable_Large (SomeColumn)\r\nSELECT top 1000000\r\nchar(65+FLOOR(RAND(a.column_id *5645 + b.object_id)*10)) + char(65+FLOOR(RAND(b.column_id *3784 + b.object_id)*12)) +\r\nchar(65+FLOOR(RAND(b.column_id *6841 + a.object_id)*12)) + char(65+FLOOR(RAND(a.column_id *7544 + b.object_id)*8))\r\nfrom msdb.sys.columns a cross join msdb.sys.columns b;\r\n\r\nINSERT INTO SecondaryTable_Large (LookupColumn)\r\nSELECT SomeColumn\r\nFROM PrimaryTable_Large TABLESAMPLE (25 PERCENT);<\/pre>\n
\n
SELECT ID, SomeColumn FROM PrimaryTable_Large\r\nWHERE SomeColumn IN (SELECT LookupColumn FROM SecondaryTable_Large)\r\n\r\nSELECT ID, SomeColumn FROM PrimaryTable_Large\r\nWHERE SomeColumn IN (SELECT DISTINCT LookupColumn FROM SecondaryTable_Large)<\/pre>\n
\nTable ‘PrimaryTable_Large’. Scan count 1, logical reads 14548, physical reads 0.
\nTable ‘SecondaryTable_Large’. Scan count 1, logical reads 798, physical reads 0.<\/p><\/blockquote>\n\n
<\/a><\/p>\nCREATE INDEX idx_Primary\r\nON dbo.PrimaryTable_Large (SomeColumn)\r\n\r\nCREATE INDEX idx_Secondary\r\nON dbo.SecondaryTable_Large (LookupColumn)<\/pre>\n