When should I prefer JOIN over WHERE in MySQL queries?

Question

Examine the following scenario (source: http://phpweby.com/tutorials/mysql/32):

mysql> SELECT * FROM products;
+----+--------------+--------------+
| id | product_name | manufacturer |
+----+--------------+--------------+
|  1 | Shoes        | Company1     |
|  2 | Laptop       | Company2     |
|  3 | Monitor      | Company3     |
|  4 | DVD          | Company4     |
+----+--------------+--------------+

mysql> SELECT * FROM buyers;
+----+------+------------+----------+
| id | pid  | buyer_name | quantity |
+----+------+------------+----------+
|  1 |    1 | Steve      |        2 |
|  2 |    2 | John       |        1 |
|  3 |    3 | Larry      |        1 |
|  4 |    3 | Michael    |        5 |
|  5 | NULL | Steven     |     NULL |
+----+------+------------+----------+

Suppose I'd like to create a single table where each buyer is listed along the products he bought. I can get very similar results using two different ways - one using JOIN and the other using WHERE.

mysql> SELECT buyer_name, quantity, product_name
 FROM buyers LEFT JOIN products 
 ON buyers.pid=products.id;
+------------+----------+--------------+
| buyer_name | quantity | product_name |
+------------+----------+--------------+
| Steve      |        2 | Shoes        |
| John       |        1 | Laptop       |
| Larry      |        1 | Monitor      |
| Michael    |        5 | Monitor      |
| Steven     |     NULL | NULL         |
+------------+----------+--------------+

mysql> SELECT buyers.buyer_name, buyers.quantity, products.product_name
 FROM buyers,products 
 WHERE buyers.pid=products.id;
+------------+----------+--------------+
| buyer_name | quantity | product_name |
+------------+----------+--------------+
| Steve      |        2 | Shoes        |
| John       |        1 | Laptop       |
| Larry      |        1 | Monitor      |
| Michael    |        5 | Monitor      |
+------------+----------+--------------+

This is a toy example so I guess it doesn't really matter which way you choose (except the difference re. buyers who didn't actually buy anything, e.g. Steven).

But when it comes to large tables, is there a difference in efficiency between the the two queries? From some trials I did, I think there is.

I would be happy to better understand if that's correct and what is the fundamental difference between the implementation of the two schemes, and when should I prefer each of them.

Answer 1

Explicitly mentioning the join is generally supposed to be better (and easier to read) besides being the ANSI standard, but with modern optimizers, I dont think there is any marked difference in performance in both the versions.

Note: the two queries you mentioned are not equivalent - if you replace the left join with an inner join, they become equivalent, in which case there is no noticeable difference in performance.

An inner join is generally faster than a left join.

Answer 2

I would stick to the ANSI style(using the join keyword) join syntax. It's makes the query much easier to read.

Edit: The reason your result sets are different is because you used a left join which isn't the equivalent to the "where" syntax join.

Answer 3

Your two code examples are performing different sorts of JOINs. The first is doing a LEFT OUTER join -- the "outer" implies that it includes results which don't show up in both tables. The second is doing an INNER join -- it doesn't include rows which are NULL in one or both tables.

I believe the second example should perform the same as a "FROM buyers join products on buyers.pid=products.id".

Answer 4

Your second query is actually equivalent to:

SELECT buyers.buyer_name, buyers.quantity, products.product_name
FROM buyers
INNER JOIN products ON buyers.pid=products.id
;

The difference in the results is the difference between an INNER and an OUTER join.

As far as which style you use, that's a matter of preference. Most people prefer explicit joins (JOIN/ON syntax) to implicit joins (in the WHERE clause) to separate the join condition from selection criteria.

Answer 5

Generally speaking, joins are slow, but cross-joins are even slower.

Any database management system could optimize either sort of query if it was designed to do so. However, many, many person-hours have been spent optimizing joins on any RDBMS that has been in wide use for any length of time. So, generally speaking, where JOIN is the logical way to relate the input tables in your result set, use JOIN. WHERE has plenty of other uses.

Edit (for clarity on one point):

MySQL has been around long enough that it probably optimizes the cross-join WHERE syntax to be executed the same way as the JOIN syntax, though I don't have an installation handy to check at the moment. So, if the difference is only semantic, say what you mean and what will be the clearest, which as others have pointed out, is usually going to be the JOIN syntax.

Answer 6

Your question is a little malformed. You're comparing a LEFT JOIN with a query that replicates an INNER JOIN. However if it was written correctly, the answer would be no the performance should be the same, INNER JOIN basically lets you type less.

Answer 7

I can't for certain say whether JOIN'ing is faster or slower or equivalent to WHERE'ing.

However, here's how I think of it -- when you JOIN, you are normally dealing with primary and foreign keys. "Product INNER JOIN Buyers ON Product.id = Buyers.pid" from your example.

Your WHERE clause a lot of times is where you define other criteria that doesn't have anything to do with relating 2 tables together. Where clauses normally deal with 1 table. "WHERE DateCreated > '2010/01/01' AND Status = 'A' " for example.

These abstractions aren't always true.

I often want to get all child records where a foreign key is a specific value. "WHERE buyers.pid = 3" So, obviously, that WHERE clause deals with a key value.

In my experience, using a WHERE clause to join tables together was in vogue until about 10-12 years ago, and then most everyone switched to using INNER JOIN. That's how most code I write and see written these days is structured, but that's hardly a good answer to your question, merely an observation on conventions.

Answer 8

When should I prefer JOIN over WHERE in MySQL queries?

In general, choose whichever construct makes sense logically: easy to understand by a human coder, uses Standard constructs and syntax (making it easier to port to future releases of the same product or another SQL product), easy to maintain, etc. Then test for performance and optimize as necessary, however leave the original code in comments in case the 'ideal' code from a logical point of view produces acceptable performance after a port.