Pros / Cons of MySql JOINS

Question

When I'm selecting data from multiple tables I used to use JOINS a lot and recently I started to use another way but I'm unsure of the impact in the long run.

Examples:

SELECT * FROM table_1 LEFT JOIN table_2 ON (table_1.column = table_2.column)

So this is your basic LEFT JOIN across tables but take a look at the query below.

SELECT * FROM table_1,table_2 WHERE table_1.column = table_2.column

Personally if I was joining across lets say 7 tables of data I would prefer to do this over JOINS.

But are there any pros and cons in regards to the 2 methods ?

Answer 1

Second method is a shortcut for INNER JOIN.

 SELECT * FROM table_1 INNER JOIN table_2 ON table_1.column = table_2.column

Will only select records that match the condition in both tables (LEFT JOIN will select all records from table on the left, and matching records from table on the right)

Quote from http://dev.mysql.com/doc/refman/5.0/en/join.html

[...] we consider each comma in a list of table_reference items as equivalent to an inner join

And

INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.

In general there are quite a few things mentioned there, that should make you consider not using commas.