SQL RIGHT JOIN

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:

• The RIGHT JOIN command

• Practice problem

Right joins are similar to left joins except they return all rows from the table in the RIGHT JOIN clause and only matching rows from the table in the FROM clause.

RIGHT JOIN is rarely used because you can achieve the results of a RIGHT JOIN by simply switching the two joined table names in a LEFT JOIN. For example, in this query of the Crunchbase dataset, the LEFT JOIN section:

SELECT companies.permalink AS companies_permalink,
       companies.name AS companies_name,
       acquisitions.company_permalink AS acquisitions_permalink,
       acquisitions.acquired_at AS acquired_date
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_acquisitions acquisitions
    ON companies.permalink = acquisitions.company_permalink

produces the same results as this query:

SELECT companies.permalink AS companies_permalink,
       companies.name AS companies_name,
       acquisitions.company_permalink AS acquisitions_permalink,
       acquisitions.acquired_at AS acquired_date
  FROM tutorial.crunchbase_acquisitions acquisitions
 RIGHT JOIN tutorial.crunchbase_companies companies
    ON companies.permalink = acquisitions.company_permalink

The convention of always using LEFT JOIN probably exists to make queries easier to read and audit, but beyond that there isn't necessarily a strong reason to avoid using RIGHT JOIN.

It's worth noting that LEFT JOIN and RIGHT JOIN can be written as LEFT OUTER JOIN and RIGHT OUTER JOIN, respectively.

Practice Problem

---

Rewrite the previous practice query in which you counted total and acquired companies by state, but with a RIGHT JOIN instead of a LEFT JOIN. The goal is to produce the exact same results.

Try it outSee the answer

SELECT companies.state_code,        COUNT(DISTINCT companies.permalink) AS unique_companies,        COUNT(DISTINCT acquisitions.company_permalink) AS acquired_companies   FROM tutorial.crunchbase_acquisitions acquisitions  RIGHT JOIN tutorial.crunchbase_companies companies     ON companies.permalink = acquisitions.company_permalink  WHERE companies.state_code IS NOT NULL  GROUP BY 1  ORDER BY 3 DESC