Subqueries are one way of using multiple tables together, but a more flexible and powerful way is to use JOIN.
JOIN is used in the FROM clause of an SQL statement as follows:
SELECT musicians.name, instruments.name as main_instrument FROM musicians JOIN instruments ON musicians.main_instrument = instrument.id;
A JOIN statement requires an ON clause that specifies the conditions used to match rows in each table. The ON clause acts like a filter, much like the WHERE clause does; you can imagine that the JOIN creates a new table containing every possible combination of rows from both tables, then filters out the ones that don't match the ON conditions. Tables are typically joined by matching the values in common fields, such as ...
