I consider my understanding of MySQL around the intermediate level, but when writing table joins in the past I have always used the ‘equi-join’ method:
SELECT t1.*, t2.`name` FROM t1, t2 WHERE t1.n = t2.n;
Now, when a record for t2.n doesn’t exist, a row will not be returned. To solve this problem in the past, I would have executed two separate queries and process the output with PHP – Not the most efficient solution.
Today I had to write a query on a relatively larger scale with a number of table joins. I knew for a fact that a few of these joins would not have matching data on all occasions and splitting one query into x queries and then processing the results would not be a good idea in the slightest.
Searching Google yielded bugger all on the subject, though that could have been down to my search criteria. The MySQL manual was helpful as usual (that’s sarcasm, by the way – They should take a page out of PHPs book, their manual is great), but with a mixture of the two I found the solution. Left joins.
SELECT t1.*, t2.`name` FROM t1 LEFT JOIN t2 ON t1.n = t2.n;
The above will return a row, even if there isn’t a match for t1.n = t2.n. Learning every day…
John Kleijn
May 24th, 2008 at 1:26 pm
Actually the solution is OUTER joins. The full syntax for a ‘regular’ join is ‘INNER JOIN’, the full syntax for a left join is ‘LEFT OUTER JOIN’. You can do a ‘RIGHT OUTER JOIN’ (RIGHT JOIN for short) which has the same effect as switching the table references in the query.
The method you previously used is pre-SQL92 syntax.
Tirofijo
July 8th, 2008 at 6:13 am
Ah this was what I needed and it works – thanks.