I don't get the opportunity to use a FULL OUTER JOIN in a query very often, but I found a good use case today.

There were 3 consecutive INSERT INTO ... SELECT queries. Each one joined to the same (huge) table: one with an INNER JOIN, one a LEFT JOIN, and another a RIGHT JOIN. I combined them into one FULL OUTER JOIN query, and reduced three expensive table scans to one.