Join Tables

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_id
```
Using 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_id
```
Notice 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 Using Something Else in Place of a Table.

For more information on adding tables to a query, see Adding Tables.

See Also

Structure of Retrieval Queries