SQL Joins on Multiple Keys

Starting here? This lesson is part of a full-length tutorial in using SQL for Data Analysis. Check out the beginning.

In this lesson we'll cover:

• Joining on multiple keys

This lesson uses the same data from previous lessons, which was pulled from Crunchbase on Feb. 5, 2014. Learn more about this dataset.

Joining on multiple keys

There are couple reasons you might want to join tables on multiple foreign keys. The first has to do with accuracy.

The second reason has to do with performance. SQL uses "indexes" (essentially pre-defined joins) to speed up queries. This will be covered in greater detail in the lesson on making queries run faster, but for now all you need to know is that it can occasionally make your query run faster to join on multiple fields, even when it does not add to the accuracy of the query. For example, the results of the following query will be the same with or without the last line. However, it is possible to optimize the database such that the query runs more quickly with the last line included:

SELECT companies.permalink,
       companies.name,
       investments.company_name,
       investments.company_permalink
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_investments_part1 investments
    ON companies.permalink = investments.company_permalink
   AND companies.name = investments.company_name

It's worth noting that this will have relatively little effect on small datasets.