Tables in Joins
When combining data from multiple tables, you must decide what tables to use. There are several noteworthy considerations:
Combining Three or More Tables Each JOIN operation combines two tables. However, you can use multiple JOIN operations within one query to assemble data from any number of tables. Because the result of each JOIN operation is effectively a table, you can use that result as an operand in a subsequent join operation. For example, to create a result set in which each row contains a book title, an author, and the percentage of that book's royalties the author receives, you must combine data from three tables: authors, titles, and titleauthor. The resulting SQL might look like this:
SELECT title, au_fname, au_lname, royaltyper FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id INNER JOIN titles ON titleauthor.title_id = titles.title_idUsing a Table merely to join others You can include a table in a join even if you do not want to include any of that table's columns in a result set. For example, to establish a result set in which each row describes a title-store pair in which that store sells that title, you include columns from two tables: titles, and stores. But you must use a third table, sales, to determine which stores have sold which titles. The resulting SQL might look like this:
SELECT title, stor_name FROM titles INNER JOIN sales ON titles.title_id = sales.title_id INNER JOIN stores ON sales.stor_id = stores.stor_idNotice that the sales table contributes no columns to the result set.
Using a table twice in one query You can use the same table two (or more) times within a single query. For more information, see Using a Table Twice in One Query.
Using something else in place of a table In place of a table, you can use a query, a view, or a user-defined function that returns a table. For more information, see How to: Create Queries using Something Besides a Table.
For more information on adding tables to a query, see How to: Add Tables to Queries.