Right idea, wrong apply! I was so intent on finally coming up with a good use for cross apply that I didn't realize I needed an outer apply. So here's what I ended up with for both:
declare @startdt datetime
PRINT 'Left Joins';
SELECT @StartDT = GETDATE();
SET STATISTICS TIME ON;
select distinct t1.id, t1.phonenumber, u.phone as [Universe Match]
, s.phone as [Specialty Practice Match]
, p.phone as [Primary Care Match]
, case when t1.orc_cell1 = 7 then 'Customer'
when t1.orc_cell1 = 9 then 'Non-Customer'
else null end as [Designation]
from j3689141 as t1 left join J3689141_IDNCHK as u
on t1.phonenumber = u.phone
left join J3689141_IDNCHK2 as s on t1.phonenumber = s.phone
left join J3689141_IDNCHK3 as p on t1.phonenumber = p.phone
where (u.phone is not null or s.phone is not null or p.phone is not null)
and t1.orc_cell1 in (7, 9)
and t1.statusflag = 0
order by t1.id
SET STATISTICS TIME OFF;
SELECT ElapsedMS=DATEDIFF(millisecond, @StartDT, GETDATE());
PRINT 'Outer Apply';
SELECT @StartDT = GETDATE();
SET STATISTICS TIME ON;
select distinct t.id, t.phonenumber, u.phone as [Universe Match]
, s.phone as [Specialty Practice Match]
, p.phone as [Primary Care Match]
, case when t.orc_cell1 = 7 then 'Customer'
when t.orc_cell1 = 9 then 'Non-Customer'
else null end as [Designation]
from j3689141 as t
outer apply (
select phone
from J3689141_IDNCHK
where t.phonenumber = phone
) as u
outer apply (
select phone
from J3689141_IDNCHK2
where t.phonenumber = phone
) as s
outer apply (
select phone
from J3689141_IDNCHK3
where t.phonenumber = phone
) as p
where(u.phone is not null or s.phone is not null or p.phone is not null)
and t.orc_cell1 in (7,9)
and t.statusflag = 0
order by t.id
SET STATISTICS TIME OFF;
SELECT ElapsedMS=DATEDIFF(millisecond, @StartDT, GETDATE());
Left Joins
SQL Server Execution Times:
CPU time = 936 ms, elapsed time = 569 ms.
Outer Apply
SQL Server Execution Times:
CPU time = 1672 ms, elapsed time = 681 ms.
The left joins always won.