Join Tables

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