whats the advantage or disadvantage of doing one of these way

Question

I see some people do joins like this

SELECT * FROM table1
JOIN table 2
ON table1_id = table2_id

where as some people do this

SELECT * 
FROM table1 
WHERE table1.table1_id = tabl2.table2_id;

whats the difference and what is more efficient and benificial

Answer 1

€: found this: http://ibmsystemsmag.blogs.com/db2utor/2008/04/performance-com.html

The numbers from this exercise show that using INNER JOIN over the IMPLICIT JOIN syntax won't give you a performance improvement. This may have been the case in DB2 V4 or V5, but as you can see the optimizer is getting very smart and will rewrite statements to be in the best format to achieve good performance.

Answer 2

Implicit joins (the second example) are considered bad style by many and can mislead you or another programmer. Writing joins out (first example) is more explicit.

In efficiency, it probably won't matter as your SQL optimizer will execute both of the queries in the same way.

Answer 3

No difference at all. It is same queries. (First query is like "old-school", for people who remember that join statement did not present at all, and second query is like 'new-school', who can't image how to join will works without non-necessary words :). Its a joke)

Answer 4

The former (explicit join) overtly distinguishes between conditions that are used for joining tables from those that apply to the whole recordset, while the latter (implicit join) does not. Also, I don't know whether mysql allows outer joins with the implicit join syntax - I believe some SQLs do, some don't.

The upshot: explicit joins are clearer and therefore should be easier to maintain; implicit joins are less verbose.

Answer 5

Here's why

SELECT * FROM table1
INNER JOIN table 2
ON table1_id = table2_id

SELECT * FROM table1
LEFT JOIN table 2
ON table1_id = table2_id

SELECT * FROM table1
RIGHT JOIN table 2
ON table1_id = table2_id

SELECT * FROM table1
FULL OUTER JOIN table 2
ON table1_id = table2_id

Answer 6

One reason to avoid implicit joins is that it is very easy to make a mistake with them and get an accidental cross join. You also can have a lot of problems if you need to use an outer instead of inner join. Further, many people who use implict joins don;t seem to really understand what joins do and thus often create queries that appear to work but which are not correct. Since thoroughly understanding joins is a requirement for any person who writes SQL code, it is foolish to use a syntax which will help you avoid learning the basics you need to know to be successful.

And when you do intend to use a cross join, the person maintaining the code has no idea if that was what you meant to do or if it was a mistake on your part since accidental cross joins are so common with the poor syntax.

The implicit joins syntax has been outdated for almost two decades, why would you even consider using it?