JOINs vs SELECTing from many tables

Question

I've been looking far and wide, but I can't find an answer, probably because I can't figure out the right way to ask the question. So here it is: is there any reason to prefer any of these two queries?

SELECT * FROM table1, table2 WHERE table1.id = table2.id;

and

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

The question stems from a much more complicated query that I am trying to optimize, but I think those two queries contain the essence of the question, and hopefully by asking in this way it will be more useful to others. Thanks in advance.

Answer 1

SELECT * FROM table1, table2 WHERE table1.id = table2.id;

and

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

will return the same results. However you should prefer the second one in this case. The first form is still widely encountered because Oracle did not support the second form for a long time.

Yet, stating INNER JOIN carries intent and forces you to write a condition because an INNER JOIN requires an ON clause. For larger queries it makes the query far more readable and makes it harder to skip a joining predicate.

Also, note that I would tend to read the first form as: take the cartesian product of the two tables, and only retain rows that have equal ids in both tables which in SQL is expressed as SELECT * FROM table1 CROSS JOIN table2 WHERE table1.id = table2.id;.

Answer 2

Actually the two queries are the same, only two different ways to write it. Personally I think that the latter one gives a little more readability for the user.

Answer 3

From what I gather they are both the same. One is just syntactical sugar for the other.

It's known as Explicit versus Implicit Join notation.

Answer 4

They are the same.

Much of the differences seen in basic format is often between different databases such as Oracle and mySQL. Traditionally in oracle joins were done just using table.foreign_id = table2.id whereas mySQL would be using table1 join table2 on table1.foreign_key = table2.id

Similarly join, inner join, left inner join and left join have traditionally varied a little bit from database implementation to implementation so it is best to check the manual/document for your current database implementation to make sure it's doing what you want.

In more recent times the syntax has become more standardized with ANSI standards but what people use often shows their roots!
This well-written article -
http://www.google.com/url?sa=t&source=web&cd=4&ved=0CD8QFjAD&url=http%3A%2F%2Fwww.kingtraining.com%2Fconfdownloads%2Fdownloads%2FOracle9iJoin_paper.pdf&ei=LOGdTpfiFOrV0QH4zpmECQ&usg=AFQjCNHjicW-tWmJfZcXwNi220LxjGvNhg&sig2=4tdM2lfgQ6AqiYudWWTirg

gives a lot more info and history and explains the various kinds of inner and outer joins really well.