Shantanu's Blog

This is related to the discussion on comma joins.

http://www.mysqlperformanceblog.com/2010/04/14/is-there-a-performance-difference-between-join-and-where/

Here are a few points to note:

1) "Inner join" syntax is ANSI compliant. Using the standard way of writing queries is better.

2) When you need to rewrite the comma syntax to left join, it will have to re-code the whole structure and adopt the join syntax.

SELECT * FROM A,B WHERE A.id = B.id and A.x=123;

If you have already written the above query, it will be difficult to write the left join as shown below.
SELECT * FROM A LEFT OUTER JOIN B ON A.id=B.id WHERE A.x=123;

If you had written the inner join as shown below, it is easy to change to Left join.
SELECT * FROM A INNER JOIN B ON A.id = B.id WHERE A.x=123


3) In the following example, isn't it difficult to find the relations between tables?

SELECT * FROM A, B, C, D WHERE A.x=123,
B.DATE > CURDATE()
...
some more conditions
...

D.id = C.id and
C.id = B.id and
A.id = B.id

Isn't the following much easy to read and understand?

SELECT * FROM A
INNER JOIN B ON B.id = A.id
INNER JOIN C C.id = B.id
INNER JOIN D D.id = C.id
WHERE A.x=123, B.DATE > CURDATE()

I prefer that the column of table that is being joined come first in the sequence and therefore B.id = A.id and not A.id = B.id though both are the same.

4) The forth point is that if I forget to add the comma, it becomes a table alias and things become difficult to find what went wrong.

SELECT * FROM A B WHERE B.id=123;

The above is a perfectly valid query but with wrong results since the table A is aliased as B!!

5) Updates
We need to join tables not just in "select", but in updates too. The update statement can be written using comma joins. But see the difference between these two queries and decide which one would you prefer.

UPDATE phpbb2_posts_text A, phpbb3_posts3 B -- implicit comma join
SET B.bbcode_uid=A.bbcode_uid
, B.post_subject=A.post_subject
, B.post_text=A.post_text
WHERE A.somecolumn = B.somecolumn
AND B.post_id<=218727


UPDATE phpbb2_posts_text A
INNER
JOIN phpbb3_posts3 B -- explicit join
ON A.somecolumn = B.somecolumn
SET B.bbcode_uid=A.bbcode_uid
, B.post_subject=A.post_subject
, B.post_text=A.post_text
WHERE B.post_id<=218727

Labels: mysql tips