Sheeri K. Cabral of the Pythian Group goes through the meanings of INNER, LEFT/RIGHT OUTER, CROSS, NATURAL joins, how to do a FULL OUTER JOIN in MySQL, and what STRAIGHT_JOIN means. She also goes through how to recognize when you want those types of joins, and best practices for the semantics of writing joins and design patterns. Subqueries are explained in this session, and some examples of how to think differently so that you end up writing JOINs instead of subqueries. The slides (slightly different from the slides used, because I corrected the errors I made on them) can be found at http://technocation.org/files/doc/2010_01MySQLJoins.pdf
[youtube YU6QsDvMk1U]