What are the differences between these query JOIN types and are there any caveats?

Question

I have multiple queries (from different section of my site) i am executing

Some are like this:

SELECT field, field1 
FROM table1, table2 
WHERE table1.id = table2.id 
AND ....

and some are like this:

SELECT field, field1 
FROM table1 
JOIN table2 
USING (id)  
WHERE ...
AND ....

and some are like this:

SELECT field, field1 
FROM table1 
LEFT JOIN table2 
 ON (table1.id = table2.id)
WHERE ...
AND ....

Which of these queries is better, or slower/faster or more standard?

Answer 1

The first two queries are equivalent; in the MySql world the using keyword is (well, almost - see the documentation but using is part of the Sql2003 spec and there are some differences in NULL values) the same as saying field1.id = field2.id

You could easily write them as:

SELECT field1, field2
FROM table1
INNER JOIN table2 ON (table1.id = table2.id)

The third query is a LEFT JOIN. This will select all the matching rows in both tables, and will also return all the rows in table1 that have no matches in table2. For these rows, the columns in table2 will be represented by NULL values.

I like Jeff Atwood's visual explanation of these

Now, on to what is better or worse. The answer is, it depends. They are for different things. If there are more rows in table1 than table2, then a left join will return more rows than an inner join. But the performance of the queries will be effected by many factors, like table size, the types of the column, what the database is doing at the same time.

Your first concern should be to use the query you need to get the data out. You might honestly want to know what rows in table1 have no match in table2; in this case you'd use a LEFT JOIN. Or you might only want rows that match - the INNER JOIN.

As Krister points out, you can use the EXPLAIN keyword to tell you how the database will execute each kind of query. This is very useful when trying to figure out just why a query is slow, as you can see where the database spends all of its time.