Those of you who have been using MySQL for sometime now would know that the MySQL 5.0 Online Reference Manual is not just a manual but also a repository of user comments exploring and solving common and/or deeper problems. If you are stuck with a particular problem that you find unable to frame a SQL for, the comments on the manual pages would usually have a solution.

For example, consider a common scenario: You have some data in table1 and table2, and you want to find the data in table1 which does not exist in table2. This is a typical outer join problem, and an anonymous user comment on the MySQL Manual page for JOIN syntax gives the solution:

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL

Now suppose you want to search not from the entire table2, but from a subset of table2 (say CityID = 1). How do you do that? You can try and see for yourself that the following SQL is not the solution:

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL
and table2.CityID = 1

Note that this is because we are looking for records that do not exist in table2 and hence cannot have a where clause on table2 data.

Read the manual page a little further, and another user comment points out:

Conditions for the “right table” go in the ON clause.
Conditions for the “left table” go in the WHERE clause,
except for the joining conditions themselves.

That makes the solution as:

SELECT table1.* FROM table1 LEFT JOIN table2 ON (table1.id=table2.id and table2.CityID = 1)
WHERE table2.id IS NULL

Happy digging! :-)