SQL SELF JOIN Tutorial Example
Hi everyone. So I need help understanding the following example in the SELF JOIN lesson.
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
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.