SQL SELF JOIN Tutorial Example

  • NE
  • purple_bee
  • 3 wk ago
  • 68

Hi everyone. So I need help understanding the following example in the SELF JOIN lesson.

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'

I'm not following how SQL is going through and comparing the rows. For example, from my understanding:

AND gb_investments.funded_at > japan_investments.funded_at 

Can never be true since both of those entries are in the same column/row position. In other words, gb_investments.funded_at  should equal japan_investments.funded_at

Also, I don't see how the WHERE statement can work since all of the rows have already been filtered country_code = 'GBR'. Indeed, if you print all the rows to this query, there are no rows with 'JPN' as the country_code. 

Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
reply to topic
Like Follow
  • 3 wk agoLast active
  • 68Views
  • 2 Following

Create A New Discussion

Share your thoughts