SQL query return data from multiple tables

I would like to know the following:

• how to get data from multiple tables in my database?
• what types of methods are there to do this?
• what are joins and unions and how are they different from one another?
• When should I use each one compared to the others?

I am planning to use this in my (for example – PHP) application, but don’t want to run multiple queries against the database, what options do I have to get data from multiple tables in a single query?

Note: I am writing this as I would like to be able to link to a well written guide on the numerous questions that I constantly come across in the PHP queue, so I can link to this for further detail when I post an answer.

The answers cover off the following:

1. Part 1 – Joins and Unions
2. Part 2 – Subqueries
3. Part 3 – Tricks and Efficient Code
4. Part 4 – Subqueries in the From Clause
5. Part 5 – Mixed Bag of John’s Tricks