What is the difference between `INNER JOIN`, `LEFT JOIN`, and `RIGHT JOIN`?

Q: What is the difference between `INNER JOIN`, `LEFT JOIN`, and `RIGHT JOIN`?

Learn the answer to "What is the difference between `INNER JOIN`, `LEFT JOIN`, and `RIGHT JOIN`?" with detailed explanations, code examples, and best practices on DeployU.

The Scenario

You are a backend engineer at an e-commerce company. You are writing a new service that needs to query the database to get a list of all the customers and their orders.

You have two tables: customers and orders. The customers table has a customer_id column, and the orders table has a customer_id column that is a foreign key to the customers table.

You need to decide which type of JOIN to use to get the data you need.

The Challenge

Explain the difference between an INNER JOIN, a LEFT JOIN, and a RIGHT JOIN. What are the pros and cons of each approach, and which one would you choose for this use case?

Wrong Approach

A junior engineer might not be aware of the different types of `JOIN`s. They might just use an `INNER JOIN` for everything, which would not always be the correct choice.

Addresses symptoms, not root cause

Right Approach

A senior engineer would be able to provide a detailed explanation of the differences between the different types of `JOIN`s. They would also be able to explain the trade-offs between each approach and would have a clear recommendation for which one to use in this use case.

Step 1: Understand the Key Differences

`JOIN` Type	Description
`INNER JOIN`	Returns only the rows that have a matching value in both tables.
`LEFT JOIN`	Returns all the rows from the left table, and the matching rows from the right table. If there is no match, the result is `NULL` on the right side.
`RIGHT JOIN`	Returns all the rows from the right table, and the matching rows from the left table. If there is no match, the result is `NULL` on the left side.

Step 2: Choose the Right Tool for the Job

For our use case, we want to get a list of all the customers and their orders.

If we want to get a list of only the customers who have placed an order, we should use an INNER JOIN.
If we want to get a list of all the customers, regardless of whether they have placed an order, we should use a LEFT JOIN.
If we want to get a list of all the orders, regardless of whether they are associated with a customer, we should use a RIGHT JOIN.

Step 3: Code Examples

Here are some code examples that show the difference between the three approaches:

INNER JOIN:

SELECT customers.customer_name, orders.order_id
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

LEFT JOIN:

SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

RIGHT JOIN:

SELECT customers.customer_name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

Systematic, production-ready debugging

Practice Question

You want to get a list of all the products and the number of times each product has been ordered, including products that have never been ordered. Which of the following would you use?

Questions