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.
Just to let you know, this apparent improvement is unlikely to make any difference at all to execution speed.
When playing with SQL, it’s very important to keep your eye on the results of EXPLAIN.
The below demonstrates that the examples you give actually result in the query optimizer doing identical query plans.
First, the setup:
mysql> CREATE TABLE table1 (id int auto_increment primary
mysql> CREATE TABLE table2 (id int auto_increment primary
mysql> ALTER TABLE table2 ADD KEY (table1_id);
mysql> insert into table1 values (1, ‘moo’), (2, ‘cow’), (3, ‘ribbit’), (4, ‘foo’), (5, ‘bar’);
mysql> insert into table2 values (1, 1, ‘moo’), (2, 1, ‘ribbit’), (3, 1, ‘fox’), (4, 2, ‘raaaaa’);
Now the results of the queries:
mysql> EXPLAIN SELECT t1.value, t2.value FROM table1 AS t1, table2 AS t2 WHERE t1.id = t2.table1_id;
mysql> EXPLAIN SELECT t1.value, t2.value FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.id = t2.table1_id;
+—-+————-+——-+——–+—————+———+———+——————-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——–+—————+———+———+——————-+——+——-+
| 1 | SIMPLE | t2 | ALL | table1_id | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.table1_id | 1 | |
+—-+————-+——-+——–+—————+———+———+——————-+——+——-+
2 rows in set (0.00 sec)
Shifting more logic into the join condition also appears to make no difference:
mysql> EXPLAIN SELECT t1.value from table1 AS t1, table2 AS t2 WHERE t1.id = t2.table1_id AND t1.value = ‘cow’;
+—-+————-+——-+——–+—————+———–+———+——————-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——–+—————+———–+———+——————-+——+————-+
| 1 | SIMPLE | t2 | index | table1_id | table1_id | 5 | NULL | 4 | Using index |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.table1_id | 1 | Using where |
+—-+————-+——-+——–+—————+———–+———+——————-+——+————-+
2 rows in set (0.00 sec)
mysql> EXPLAIN SELECT t1.value from table1 AS t1 INNER JOIN table2 AS t2 ON (t1.id = t2.table1_id AND t1.value = ‘cow’);
+—-+————-+——-+——–+—————+———–+———+——————-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——–+—————+———–+———+——————-+——+————-+
| 1 | SIMPLE | t2 | index | table1_id | table1_id | 5 | NULL | 4 | Using index |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.table1_id | 1 | Using where |
+—-+————-+——-+——–+—————+———–+———+——————-+——+————-+
Thanks, this is really useful! A good explanation too.
Thanks a lot for this post, it really helped me out too.
The ON tatement is very useful at LEFT JOINS.
If you need to join a table with only one of two other tables depending on a specific value, you can use conditional LEFT JOINS.
See good example with explanation at
http://www.mysqldiary.com/conditional-joins-in-mysql/
thanks IIan, yes there are a myriad of different joins for different things, this was only meant to promote their usage.. a Left Join is for usage (I thought) for when there is the option of corresponding matches not found on the 2nd table..