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](https://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables/12475851#12475851) 2. [Part 2 - Subqueries](https://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables/12492267#12492267) 3. [Part 3 - Tricks and Efficient Code](https://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables/12508381#12508381) 4. [Part 4 - Subqueries in the From Clause](https://stackoverflow.com/a/12542115/1450077) 5. [Part 5 - Mixed Bag of John's Tricks](https://stackoverflow.com/a/12586263/1450077)