use northwind
go

set statistics io on

--Correlated Subquery
declare @starttime	datetime,
	@endtime	datetime
set @starttime = getdate()
SELECT companyname
FROM customers as c
WHERE EXISTS
	(SELECT *
		 FROM orders as o
	 WHERE o.customerid = c.customerid 
		and orderdate >'1/1/95')
set @endtime = getdate()
select 'TotalTime(Q1)' = datediff(ms, @starttime, @endtime)
go

-- Nested Subquery
declare @starttime	datetime,
	@endtime	datetime
set @starttime = getdate()
SELECT companyname
FROM customers
WHERE customerid IN
	(SELECT customerid
		 FROM orders
	 WHERE orderdate >'1/1/95')
set @endtime = getdate()
select 'TotalTime(Q2)' = datediff(ms, @starttime, @endtime)
go

-- Join
declare @starttime	datetime,
	@endtime	datetime
set @starttime = getdate()
SELECT  distinct companyname
FROM customers as c
	JOIN orders as o ON c.customerid  = o.customerid
 WHERE orderdate >'1/1/95'
set @endtime = getdate()
select 'TotalTime(Q3)' = datediff(ms, @starttime, @endtime)

