So Whats faster? Where in or inner join… the answer is it depends!

 

So Whats faster? Where in or inner join… the answer is it depends!
Lets see what the execution plans look like in different scenarios.

First lets create our test environment:

 

p3_build_data

Queries: Here are the queries we’ll try:

p3_selects_data

 

 

Results with no optimizations (Indexes):

(50000 row(s) affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘NumbersTest’. Scan count 1, logical reads 2225, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘NumbersTest2’. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(50000 row(s) affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘NumbersTest’. Scan count 1, logical reads 2225, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘NumbersTest2’. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(50000 row(s) affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘NumbersTest’. Scan count 1, logical reads 2225, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘NumbersTest2’. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(50000 row(s) affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘NumbersTest’. Scan count 1, logical reads 2225, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘NumbersTest2’. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(50000 row(s) affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘NumbersTest’. Scan count 1, logical reads 2225, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘NumbersTest2’. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

p3_PLAn1a

p3_plan1b

 

 

Similar results for all queries. Each took roughly the same time and resources.
Optimizations:

Create a simple non-clustered index.
p3_optimize_data

(50000 row(s) affected)
Table ‘NumbersTest’. Scan count 10000, logical reads 32044, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘NumbersTest2’. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(50000 row(s) affected)
Table ‘NumbersTest’. Scan count 10000, logical reads 32044, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘NumbersTest2’. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(50000 row(s) affected)
Table ‘NumbersTest’. Scan count 10000, logical reads 32044, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘NumbersTest2’. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(50000 row(s) affected)
Table ‘NumbersTest’. Scan count 10000, logical reads 32044, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘NumbersTest2’. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(50000 row(s) affected)
Table ‘NumbersTest’. Scan count 10000, logical reads 32044, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘NumbersTest2’. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

p3_PLAn2a

p3_PLAn2b

 

Summary: in this case, where there’s a large indexed table (approx 1m rows) and we are looking for a subset of that table (50K rows) inner join will outperform where…in and where… exsits. Cross apply works the same as inner join.

Getting Started – the Select Statement

If you understand the SELECT statement thoroughly, you have understood 80% of SQL, because this is the most commonly used statement, as well as the most complex (when you understand its ramifications in terms of all the things it lets you do). The original definition of SQL included only the SELECT statement.

Select * from tblClientData

table

means all rows and all columns.

this will be alot of data. in fact, this query would be almost useless, because it would return too much data, as well as kill the server. Generally this kind of a query is a bad idea.