MySQL Embedded SELECTs vs JOINs

Question

Is there a noticeable difference between:

SELECT userid, username, userdept, (SELECT deptname FROM depts WHERE deptid=userdept) AS deptname FROM users 

and

 SELECT userid, username FROM users INNER JOIN depts ON depts.deptid=users.userdept 

Which one is better?

Answer 1

The second query has better performance.

You can see this example: http://www.codersrevolution.com/index.cfm/2008/7/31/MySQL-performance-INNER-JOIN-vs-subselect

Answer 2

the connection is better

see this link: http://www.selikoff.net/2008/12/10/memo-avoid-nested-queries-in-mysql-at-all-costs/

Answer 3

you can see a lot of discussions in SO on this topic.

Answer 4

These two queries are not synonymous.

They would be synonymous if you replaced INNER JOIN with a LEFT JOIN , except that the subquery is subject to rejection if the deptid not unique and the LEFT JOIN will always succeed.

If depts.deptid has a UNIQUE index (which probably is, since this field is most likely PRIMARY KEY ), then the performance difference will be negligible.