For the most part I’ve used many JOINs in my queries since I leartn how to use them and what they were good for, I much prefer to use an Inner Join over the the Implied join of a WHERE statement.

For those who haven’t yet learnt about JOINs you might be using an implied join in your queries even now… lets take this example:

SELECT table1.field1,table2.field2
FROM table1,table2
WHERE table1.table2_id = table2.IDField

This is an Implied JOIN, you are telling MySQL to get the 2 fields from the 2 tables but only return the ones where the field in table1 is in the field in table2. This works, but using Joins we can improve it.

Ok, Lets take the above example and apply the join mentality to it:

SELECT table1.field1, table2.field2
FROM table1
INNER JOIN table2 on table1.table2_id = table2.IDField

This Links in only the rows that match the ON criteria which in theory should be less than the whole table, which again in theory should make the query faster.

Ok, now we have the JOIN theory out the way, we can get down to what this blog post is actually really all about, Conditional joins. Just Like the WHERE Clause in the SELECT statement you can use AND & OR in the ON part of a JOIN. Usually it’s used to add more constraints to the join, but you can use it in interesting ways,

SELECT table1.field1, table2.field2
FROM table1
INNER JOIN table2 on (table1.table2_id = table2.IDField) AND (table1.itemName = 'foo')

Only JOIN rows that have the matching ID and where the itemName from table1 is equal to foo;

SELECT table1.field1, table2.field2
FROM table1
INNER JOIN table2 on ((table1.table2_id = table2.IDField) AND (table1.itemName = 'foo')) OR (table2.item = 'xx')

Only JOIN rows when the IDs are matching and the itemname is foo, OR when the item in table2 is ‘xx’

Baring in mind that the more complicated you make it, the harder it will be to maintain IT still can be a great method of Reducing the amount of rows that need to be processed.

Hopefully this will help someone, if so or if you have anything to add/point out/criticise etc.. please do comment.. I Don’t Bite.