SQL Self Joins
Starting here? This lesson is part of a full-length tutorial in using SQL for Data Analysis. Check out the beginning.
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,
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
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.