Left join then inner join incorrect result using sql?

Question

I have 3 tables

drop table #temp;
create table #temp(id int,name varchar(50))

insert into #temp values(1,'ABC');
insert into #temp values(2,'XYZ');
select * from #temp

drop table #table_link;
create table #table_link(id int,temp_id int,temp2_id int)

insert into #table_link values(1,1,1);
insert into #table_link values(2,1,2);

select * from #table_link

drop table #temp2;
create table #temp2(id int,active_tag bit);
insert into #temp2 values(1,0)
insert into #temp2 values(2,1)

I desired result is get all rows in #temp table and active_tag=1 with #table_link that is.

id  |  name | #temp2_id |
1   |  ABC  |  2        |
2   |  XYZ  |  NULL     |

I tried query is

select * from #temp t
left join #table_link tl on tl.temp_id=t.id
inner join  #temp2 t2 on t2.id=tl.temp2_id and t2.active_tag=1

In this query i get only ABC row only.I used left join ,why XYZ row doesn't get.Currently Result is coming like this

id  |  name | #temp2_id |
1   |  ABC  |  2        |

Please Explain why it doesn't work ?

Does this answer your question? [Is it true that using INNER JOIN after any OUTER JOIN will essentially invalidate the effects of OUTER JOIN?](https://stackoverflow.com/questions/55094277/is-it-true-that-using-inner-join-after-any-outer-join-will-essentially-invalidat) — philipxy, Aug 28 '21 at 22:41

score 1 · Answer 1 · answered Mar 20 '17 at 07:12

1

select t.id,t.Name,tl.temp2_id from #temp t
left join #table_link tl on tl.temp_id=t.id
AND EXISTS (SELECT 1 from #temp2 t2 WHERE t2.id=tl.temp2_id AND   t2.active_tag=1 )

answered Mar 20 '17 at 07:12

Sree Charan

11
3

yes its worked thank you.please explain why my query is not correct please?Because i used inner in second table so second should will come right? – Prasanna Kumar J Mar 20 '17 at 07:15
Because, you have the XYZ result when you left join. But as soon as you do Inner join that record will be eliminated(Inner join here will be executed on the resultant of first two tables). – Sree Charan Mar 20 '17 at 07:18

score 1 · Accepted Answer · answered Mar 20 '17 at 07:28

Joins are "completed" in the order in which their ON clauses exist. Joins aren't just between tables - they're frequently between results produced from other joins. So:

select * from #temp t
left join #table_link tl on tl.temp_id=t.id
inner join  #temp2 t2 on t2.id=tl.temp2_id and t2.active_tag=1

First performs a left join between t and tl. This produces a new result set (lets call it ttl) which contains rows from t and may contain joined rows from tl or nulls (since its a left join). We then join that result (ttl) to t2, performing an inner join. However, we know that ttl may contain nulls for columns that originated from tl and so the inner join will fail.

What we can instead write is:

select * from #temp t
left join #table_link tl
inner join #temp2 t2
 on t2.id=tl.temp2_id and t2.active_tag=1
 on tl.temp_id=t.id

And note that I've now (by moving on clauses around) changed the order of the joins. We're now first performing an inner join between tl and t2 (lets call it t2tl) and then performing a left join between t and t2tl. This means that the left join acts last and so we can again get null results in the final result.

To see how joins and on clauses act, think of the join word as being a ( and the on clause as being ). Then you find the joins that on clauses relate to as finding matching ()s.

score 0 · Answer 3 · answered Mar 20 '17 at 07:20

0

Try this:

 SELECT * 
 FROM #temp
 LEFT JOIN #table_link ON #table_link.id = #temp.id
 LEFT JOIN #temp2 ON #table_link.temp_id = #temp2.id

You will get the desired output. The problem in your query might be because of joining the #temp2 on the following : t2.id=tl.temp2_id

answered Mar 20 '17 at 07:20

Kinchit Dalwani

398
4
19

it is sample record in my real record #table_link.temp_id = #temp2.id is wrong.that are two different tables – Prasanna Kumar J Mar 20 '17 at 07:23

Left join then inner join incorrect result using sql?

3 Answers3