Thanks Alan.B for testing it extensively! 🙂
When I ran your queries, like:
select n.*
from Names n
FULL JOIN TableB b ON n._id=b._id
WHERE LEFT(name,1)='A'
AND ChildCount>50
it showed Nested Loop as you suggested.
But, I ran my 2 queries, and the Actual EPs also showed Nested Loop for Inner Join.
Any idea why you suspected otherwise?
This is because the OUTER joins will produce plans with nested loops whereas the queries above will still generate a hash match
Alan.B (3/19/2013)
cnayan (3/19/2013)
My question is the latter one - which is more efficient.I tested this a little and here's what I determined: Both queries you posted will produce the exact same query plan and therefore are equally efficient. Taking the sample data provided (and not knowing if either id represented a clustered index), the following queries will most likely produce the exact same query plan (regardless of there are any clustered indexes present):
IF OBJECT_ID('tempdb..#names') IS NOT NULL DROP TABLE #names;
IF OBJECT_ID('tempdb..#TableB') IS NOT NULL DROP TABLE #TableB;
CREATE TABLE #names (id int, name char(4));
CREATE TABLE #TableB (id int, ChildCount int);
INSERT INTO #names SELECT 1,'A100' UNION ALL SELECT 2,'A100' UNION ALL SELECT 3,'A100' UNION ALL SELECT 4,'A200';
INSERT INTO #TableB SELECT 1,10 UNION ALL SELECT 2,25 UNION ALL SELECT 3,10 UNION ALL SELECT 4,70;
select n.[ID]
from #Names n
inner join #TableB b
on b.[ID] = n.[ID]
and b.[ChildCount] > 50
where n.[Name] Like 'A%'
select n.[ID]
from #Names n
inner join #TableB b
on b.[ID] = n.[ID]
and b.[ChildCount] > 50
and n.[Name] Like 'A%'
select n.[ID]
from #Names n
inner join #TableB b
on b.[ID] = n.[ID]
and LEFT(n.[Name],1)='A'
and b.[ChildCount] > 50
select n.[ID]
from #Names n
inner join #TableB b
on b.[ID] = n.[ID]
and n.[Name] Like 'A%'
and b.[ChildCount] > 50
select n.[ID]
from #Names n
inner JOIN #TableB b ON n.id=b.id
WHERE LEFT(name,1)='A'
AND ChildCount>50
;WITH filtered_names AS
(select [ID]
from #Names WHERE [Name] Like 'A%')
SELECT n.id
FROM #TableB b
inner join filtered_names n ON n.id=b.id
WHERE b.ChildCount>50
select n.[ID]
from #Names n
CROSS JOIN #TableB b
WHERE b.[ID] = n.[ID]
and b.[ChildCount] > 50
and n.[Name] Like 'A%'
select n.[ID]
from #Names n
CROSS APPLY #TableB b
WHERE b.[ID] = n.[ID]
and b.[ChildCount] > 50
and n.[Name] Like 'A%'
If you don't have a clustered index on either table then each of the above queries will be equally slow and the queries below will produce the best plan. This is because the OUTER joins will produce plans with nested loops whereas the queries above will still generate a hash match:
select n.[ID]
from #Names n
FULL JOIN #TableB b ON n.id=b.id
WHERE LEFT(name,1)='A'
AND ChildCount>50
select n.[ID]
from #Names n
RIGHT JOIN #TableB b ON n.id=b.id
WHERE LEFT(name,1)='A'
AND ChildCount>50
select n.[ID]
from #TableB b
LEFT JOIN #Names n ON n.id=b.id
WHERE LEFT(name,1)='A'
AND ChildCount>50
Edit: typo & bonus code