ThoughtSpot acquires Mode to define the next generation of collaborative BI >>Learn More

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:

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

Self joining tables

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,
  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'

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).

What's next?

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

Get more from your data

Your team can be up and running in 30 minutes or less.