Columns in Joins

The JOIN operator matches rows by comparing values in one table with values in another. You decide which columns from each table should be matched. You have several choices:

• Related Columns   Typically, you join tables by matching values in columns for which a foreign-key relationship exists. For example, you can join discounts to stores by matching the values of stor_id in the respective tables. The resulting SQL might look like this:

  SELECT *
  FROM discounts INNER JOIN stores 
       ON stores.stor_id = discounts.stor_id
  

  For more information on joining tables on related columns, see Joining Tables Automatically.

• Unrelated Columns   You can also join tables by matching values in columns for which no foreign-key relationship exists. For example, you can join publishers to authors by matching the values of state in the respective tables. Such a join yields a result set in which each row describes an author-publisher pair located in the same state.

  SELECT au_lname, 
         au_fname, 
         pub_name, 
         authors.state
  FROM   authors INNER JOIN publishers 
                 ON authors.state 
                  = publishers.state
  

  For more information on joining tables on unrelated columns, see Joining Tables Manually.

Note also that you use multiple columns to match rows from the joined tables. For example, to find the author-publisher pairs in which the author and publisher are located in the same city, you use a join operation matching the respective state columns and the respective city columns of the two tables. You need to match both city and state because it is possible that different states could have like-named cities (e.g., Springfield, Illinois and Springfield, Massachusetts).

For more information on joining tables on multiple columns, see Joining Tables on Multiple Columns.

See Also

Reference

Join Properties

Other Resources

Designing Queries and Views