MySQL Embedded SELECTs vs. JOINs

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 b开发者_开发问答etter?

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

join is better ,

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

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

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.