I have a table1 (a,b,c) and table2(a,b,c)
What's the difference between
select * from table1 T1 inner join table2 T2 on T1.a=T2.a and T1.b = t2.b and T1.c = T2.c and
select * from table1 T1 inner Join table2 T2 on T1.a = T2.a where T1.b= T2.b and T1.c = T2.C Is is the same ? and which one is better?
Thanks
With inner joins there are no difference. It is only when you start using left/right joins, that you will see differences.
For LEFT JOINS, if you had
select * from table1 T1 LEFT join table2 T2 on T1.a=T2.a and T1.b = t2.b and T1.c = T2.c It would include all rows fromo table1 and only rows from table2 where fields a,b and c matched.
If you had
select * from table1 T1 inner Join table2 T2 on T1.a = T2.a where T1.b= T2.b and T1.c = T2.C This would include rows from table1 and those from table2 where a is equal, and then filter on b and c.
I always find this visual representation usefull.
For your queries, this doesn't change anything.
And in term of performance, your RDBMS is able to understand that it's the same.
But considering you have more joins, this would change the readability of the query.
Example :
SELECT * FROM table1 T1 INNER JOIN table2 T2 ON T1.a = T2.a AND T1.b = T2.b AND T1.c = T2.c LEFT JOIN table3 T3 ON T3.x = T1.a AND T3.status = 1 WHERE T1.a > 100 You can understand faster which condition works with which table in INNER/LEFT JOIN
