SQL Joins with Comparison Operators

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:

• Using comparison operators with joins

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

Using comparison operators with joins

In the lessons so far, you've only joined tables by exactly matching values from both tables. However, you can enter any type of conditional statement into the ON clause. Here's an example using > to join only investments that occurred more than 5 years after each company's founding year:

SELECT companies.permalink,
       companies.name,
       companies.status,
       COUNT(investments.investor_permalink) AS investors
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_investments_part1 investments
    ON companies.permalink = investments.company_permalink
   AND investments.funded_year > companies.founded_year + 5
 GROUP BY 1,2, 3

This technique is especially useful for creating date ranges as shown above. It's important to note that this produces a different result than the following query because it only joins rows that fit the investments.funded_year > companies.founded_year + 5 condition rather than joining all rows and then filtering:

SELECT companies.permalink,
       companies.name,
       companies.status,
       COUNT(investments.investor_permalink) AS investors
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_investments_part1 investments
    ON companies.permalink = investments.company_permalink
 WHERE investments.funded_year > companies.founded_year + 5
 GROUP BY 1,2, 3

For more on these differences, revisit the lesson SQL Joins Using WHERE or ON.