10

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?

the Tin Man
  • 158,662
  • 42
  • 215
  • 303
rcs20
  • 595
  • 9
  • 27

3 Answers3

19

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.

dash
  • 89,546
  • 4
  • 51
  • 71
0

personally, i prefer using left joins in my queries, though you can run into issues in the case of null records or duplicates, but that can be resolved with a simple modification with an outer clause. it's my understanding that a join is a bit more resource intensive, but this is up for debate and might be based on personal preference.

just my $.02.

Robert Van Sant
  • 1,497
  • 10
  • 12
  • here's a follow up to my answer with a similar response and benchmarks around the joins [Join explanation](http://stackoverflow.com/questions/186071/left-join-outperforming-inner-join) – Robert Van Sant Dec 08 '11 at 00:12
0

The third example, using ON (field1=field2) is the more common, and seems to be the more commonly accepted standard.

I don't know about the performance difference, you would have to run some EXPLAIN queries to see what MySQL actually ends up doing with them all really.

I do know though that the first, with WHERE being used to join them all, is much less readable on anything other than trivial queries. Once you have some complex conditions in a query, it's confusing to have "join conditions" all muddled in with "selection conditions".

Cylindric
  • 5,858
  • 5
  • 46
  • 68