NULL's in MySQL logical AND operator precedence

0 replies
I normally use an INNER JOIN between these two tables, but I have some specific needs for using the LEFT JOIN below. (I've stripped out most of the code, leaving just the essence below, so if you look at it, one's knee-jerk reaction would be "Why not just use INNER JOIN?"... except that you're not seeing the whole picture here.)

I reviewed the MySQL manual for logical operator precedence, but couldn't figure out for certain the right answer to this: In most languages, once it's determined that CompanyID is NULL, (because there's no corresponding Company row for that Member), the processor simply stops and immediately returns FALSE for the AND expression. i.e., there's no need to evaluate Company.Active. That's desirable, because if CompanyID is NULL, then Company.Active will be NULL, as it won't exist. I think I'm reading this to mean that MySQL will work in the exact same fashion, i.e., evaluate the AND as FALSE, not as NULL. So, that's what I would like to happen. Will it?

SELECT * FROM Member LEFT JOIN Company USING (CompanyID)
WHERE CompanyID IS NOT NULL AND Company.Active;
Thanks in advance!
#logical #mysql #null #operator #precedence

Trending Topics