CODEWITHSUNDEEP
sqlsql-serverjoinwhere-clausecrud
user3484362
user3484362

Reputation:

Inner join differences

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

Upvotes: 0

Views: 59

Answers (2)

Ryx5
Ryx5

Reputation: 1366

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

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166586

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.

SQL Fiddle DEMO

I always find this visual representation usefull.

SQL SERVER – Introduction to JOINs – Basic of JOINs

Upvotes: 3

Related Questions