Left Join Error

This is my first time doing a left join with a query. Previously it was all simple joins. That being the case, I think I have an issue with syntax but I can’t see to figure out exactly what it is. Here’s my query in PHP code:

$userQuery = mysql_query("
	SELECT
		Users.ID,
		Users.BusinessUnit,
		Users.DateAccessed,
		Users.EmailAddress,
		Users.FirstName,
		Users.LastName,
		Users.SuperAdmin,
		BusinessUnits.Name,
		Roles.Name
	FROM
		Users, Roles
	LEFT JOIN
		BusinessUnits
	ON
		Users.BusinessUnit=BusinessUnits.ID
	WHERE
		Users.Company='$_SESSION[SESS_COMPANY]'
	AND
		Users.Status='Active'
	AND
		Users.Role=Roles.ID
	ORDER BY
		FirstName ASC
	");

The error I’m getting is Unknown column ‘Users.BusinessUnit’ in ‘on clause’. However, the column Users.BusinessUnit truly does exist. I’ve triple checked my spelling and I’m even including that table in the FROM portion of the query. What am I doing wrong? Is it a simple syntax error?

Correct me if I’m wrong, but isn’t INNER JOIN faster than the comma notation?

When using an INNER JOIN MySQL directly maps all rows from one table to the matching rows in another table, while the comma first calculates the Cartesian product of the tables involved and only later removes unwanted results using the WHERE clause? Meaning with the comma you get larger intermediate results and have MySQL do more work than with an INNER JOIN?

EXPLAINs for the two queries are exactly the same.
I guess MySQL uses the same approach to parsing both queries, coming up with the same plan for both of them :slight_smile:

implicit joins can only ever be inner joins, yes?

so use JOIN syntax exclusively, and you’ll never have to worry about mixing them again

plus, in implicit joins the join conditions are dumped into the WHERE clause where they get all mixed together with other join conditions as well as filter conditions, resulting in a mishmash of stuff which is difficult to understand if you are new to the query (e.g. somebody else wrote it, or even if you wrote it a few months ago)

with JOIN syntax, the join conditions are isolated to their specific joins, resulting in a query that is self-documenting and a lot easier to maintain

this is a common problem people are discovering when migrating from earlier versions of mysql to version 5, which is more strict about enforcing adherence to sql standards

the problem arises because you have mixed two types of join – the implicit join which uses a comma in the FROM clause and puts the join conditions in the WHERE clause, versus the explicit join, which uses JOIN syntax

explicit joins take precedence over implicit during query parsing, similar to the way ANDs take precedence over ORs when evaluating compound conditions

thus, the portion of the FROM clause highlighted below in blue is executed first:

  FROM Users, [COLOR="blue"]Roles
LEFT 
  JOIN BusinessUnits
    ON [COLOR="Red"]Users.BusinessUnit[/COLOR]=BusinessUnits.ID[/COLOR] 
 WHERE ...

as you can see, the ON clause is trying to join Roles and BusinessUnits on a condition which involves a column that isn’t in either of those tables, hence the error

make sense?

the solution is of course to eliminate the implicit join and rewrite it as an explicit INNER JOIN

i’m pretty sure there is no difference, although as soon as i say that, someone is bound to come up with an example which does show a difference

best way to convince yourself: do an EXPLAIN on both versions of your query

You are brilliant. Thanks for taking the time to explain why the error which seemed so cryptic was actually justified. All makes sense and I’ll try not to make that mistake again.

On a side note, as long as I’m using all either implicit or explicit joins, is there a reason that I could try to use one or the other? I’m assuming implicit joins is just shorthand. Maybe one is faster than the other?