-- Generate Test Data
-- Make the Column of Inerest different data for each
-- And TableOne have more data than TableTwo
SET STATISTICS IO Off
SET STATISTICS TIME Off

Drop Table #TableOne
Drop Table #TableTwo

Create Table #TableOne (id int, ColumnOfInterest varchar(50))
Create Table #TableTwo (id int, ColumnOfInterest varchar(50))

Declare @cnt int
Set @cnt = 0

While @cnt < 1000000 Begin
	Insert Into #TableOne Select @cnt, NewID()
	If @cnt % 4 = 0 Insert Into #TableTwo Select @cnt, NewID()
	Set @cnt = @cnt + 1
End

--Turn statistics on so you can see how each performs
SET STATISTICS IO ON
SET STATISTICS TIME ON

--Run this before every query execution so you get accurate results
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE


Select
T1.ColumnOfInterest, T2.ColumnOfInterest
From
#TableOne T1
Left Join #TableTwo T2 On T1.id = T2.id

/*
SQL Server parse and compile time: 
   CPU time = 46 ms, elapsed time = 66 ms.

(1000000 row(s) affected)
Table '#TableTwo'. Scan count 3, logical reads 1713, physical reads 0, read-ahead reads 0, 
	lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TableOne'. Scan count 3, logical reads 6850, 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.

SQL Server Execution Times:
   CPU time = 2922 ms,  elapsed time = 14848 ms.
*/

Select
T1.ColumnOfInterest, T2.ColumnOfInterest
From
(Select id, ColumnOfInterest From #TableOne) T1
Left Join (Select id, ColumnOfInterest From #TableTwo) T2 On T1.id = T2.id

/*
SQL Server parse and compile time: 
   CPU time = 63 ms, elapsed time = 85 ms.

(1000000 row(s) affected)
Table '#TableTwo'. Scan count 3, logical reads 1713, physical reads 0, read-ahead reads 0, 
	lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TableOne'. Scan count 3, logical reads 6850, 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.

SQL Server Execution Times:
   CPU time = 2765 ms,  elapsed time = 15404 ms.
*/