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

join is better ,

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

Answer 2

Your 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 3

you can see the many discussion in SO on this topic as well.

Answer 4

These two queries are not synonyms.

They would be the synonyms if you replaced the INNER JOIN with the LEFT JOIN, with the exception that the subquery is a subject to failure if deptid is not unique, while a LEFT JOIN will always succeed.

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