Month: April 2014

In this scenario, I was attempting to Left Join a table to another table and Inner Joining them to a third table.  I ended up getting no results from the query.

A bit of research found this link:

http://www.sitepoint.com/forums/showthread.php?769412-Combining-Left-and-Inner-Joins

In short, you have to perform the Inner Joins first before you Left Join.

My solution was to Left Join the entire way.  My reason for the Inner Join in the first place, and the reason why I’m unsure of this method was that it seemed like a good way to kill performance.  However, I’m not at the stage where I need to kill time performance tuning the system so I’ll have to ignore it for now.

I’m probably wrong anyway, but it wouldn’t hurt to check when I finally get the time.

When joining, I like to use ON + AND a lot.  I heard that this tends to have better performance than running a WHERE query.  And it worked well as long as I stayed with INNER JOINS.  Imagine my surprise when I needed to do a LEFT JOIN while performing a conditional match.  I expected a single row back, but SQL returned every single possible row instead.

After searching around, I found this link:

http://www.codeproject.com/Articles/231132/Difference-between-And-clause-along-with-on-and-Wh

Currently, I’m building up a website, so I can’t begin using Explain to tune.  In the meantime, I’m going to use WHERE to filter the list whenever I have an Outer join.  If this ends up killing performance, I’ll have to find a way to alter the queries later.