SQL Self Joins

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:

• Self joining tables

• What's next?

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

Sometimes it can be useful to join a table to itself. Let’s say you wanted to identify companies that received an investment from Great Britain following an investment from Japan.

SELECT DISTINCT japan_investments.company_name,
     japan_investments.company_permalink
  FROM tutorial.crunchbase_investments_part1 japan_investments
  JOIN tutorial.crunchbase_investments_part1 gb_investments
    ON japan_investments.company_name = gb_investments.company_name
   AND gb_investments.investor_country_code = 'GBR'
   AND gb_investments.funded_at > japan_investments.funded_at
 WHERE japan_investments.investor_country_code = 'JPN'
 ORDER BY 1

Note how the same table can easily be referenced multiple times using different aliases—in this case, japan_investments and gb_investments.

Also, keep in mind as you review the results from the above query that a large part of the data has been omitted for the sake of the lesson (much of it is in the tutorial.crunchbase_investments_part2 table).

Congratulations, you've learned most of the technical stuff you need to know to analyze data using SQL. The Advanced SQL Tutorial covers a few more necessities (an in-depth lesson on data types, for example), as well as some more technical features that will greatly extend the tools you've already learned.

Start the Advanced SQL Tutorial