I had a situation recently where I was outer joining one table onto another. I needed all rows from the first table, plus any matching rows from the second table. So:

SELECT * 
FROM table1 t1
LEFT JOIN table2 t2 ON (t1.col_a = t2.col_a)

So far, so good, right? In this particular instance though I wanted to filter my results on a particular criteria so I added something like:

SELECT * 
FROM table1 t1
LEFT JOIN table2 t2 ON (t1.col_a = t2.col_a)
WHERE t2.col_b <> 42

This is where it all started to go a bit weird. I wanted all the rows from the first table, plus anything from the second table that matched but didn’t have col_b equal to 42 (this is a rather contrived example I know but my real-world example was so complicated I’d still be explaining it if I started). The syntax above was causing a problem in the case that there was a single matching row in the second table which matched the criteria in the where clause. In this instance, the entry from the first table would appear once, and then immediately get filtered out by the constraint in the where clause.

What I actually needed to do was filter the second table before the join happens. So here’s the syntax:

SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON (t1.col_a = t2.col_a AND t2.col_b <> 42)

It turns out that you can use the ON clause when you join to include anything that you would usually put in a where clause to restrict the rows on the table that is being joined. Hope this makes sense and is useful to someone, add your comments if you have any!