SQL RIGHT JOIN
Starting here? This lesson is part of a full-length tutorial in using SQL for Data Analysis. Check out the beginning.
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
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
It's worth noting that
LEFT JOIN and
RIGHT JOIN can be written as
LEFT OUTER JOIN and
RIGHT OUTER JOIN, respectively.
RIGHT JOINinstead of a
LEFT JOIN. The goal is to produce the exact same results.
SQL Joins Using WHERE or ON