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:
- Part 1 - Joins and Unions
- Part 2 - Subqueries
- Part 3 - Tricks and Efficient Code
- Part 4 - Subqueries in the From Clause
- Part 5 - Mixed Bag of John's Tricks