What is the difference between these two select statements?
select foo from table1
inner join table2 on table1.a = table2.a and table1.b=table2.b;
select foo from table1
inner join table2 on table1.a = table2.a
where table1.b=table2.b;
What is the difference between these two select statements?
select foo from table1
left outer join table2 on table1.a = table2.a and table1.b=table2.b;
select foo from table1
left outer join table2 on table1.a = table2.a
where table1.b=table2.b;
Hey Victor, if you try this on real tables, you'll probably be able to tell the difference, but maybe not the why.
First set of questions, with inner joins, it's about evaluation precedence. No difference in results, but the WHERE is a limit on results, not on JOINs. What's happening is the two sets are being intersected just using column 'a' in the second query, and then the results are limited by the WHERE. if it's joining on both a and b, then it limits in the intersection. Not a big deal in a simple query, can be a big deal on a very busy transaction or larger queries.
Second set using LEFT JOIN, it's more important. A LEFT JOIN is saying "this might not be there". Accordingly, if you leave the condition in the join, then you're saying "give me everything from this table, whether a and b are matching (or not)". If you put the condition in the WHERE clause, then you're saying "give me everything from this table if it matches on ONLY column a (or not)", and THEN saying "now return all the stuff from that intersection where b matches", which effectively converts your LEFT to an INNER join. You'll never see the ones that had nothing in column b.
hope that helps
17 People are following this question.
