Recently in MSDN forums, OP wanted to get all results from the first table (called it r) LEFT JOINed with the other table (m) and INNER JOINed with another table (called s). In my mind it was a tricky question and I solved it by a separate CTE first doing the INNER JOIN between s and m and then LEFT JOINING with the result of the r table.

However, user cyberkiwi showed a different approach that I was never aware of (as I always write JOIN conditions right after JOIN keyword):

<pre class="brush: plain">
declare @A table (id int identity, a int)
insert @A select 1 union all select 2 union all select 3
declare @B table (id int identity, b int)
insert @B select 2 union all select 3
declare @C table (id int identity, c int)
insert @C select 3 union all select 4

select *
from @A A
left join
    	@B B
    	inner join @C C
    		on B.b = C.c
    on A.A = B.B

-- output
--id,a,id,b,id,c
--1,1,NULL,NULL,NULL,NULL
--2,2,NULL,NULL,NULL,NULL
--3,3,2,3,1,3

-- The version I write --
select *
from @A A
left join
    	@B B on A.A = B.B
    	inner join @C C
    		on B.b = C.c
</pre>

Do you see the difference between the first way and the second (most common way, for me, at least)? Well, the first way is the solution for the OP's problem. You can examine the execution plan to see, that it does the same as CTE version does: first INNER joins b & c and then r LEFT JOINed with that result.

Hope this was helpful for you as well!

Published under: SQL Server Tips ·  ·  ·  ·