Skip to main content

LEFT and RIGHT Joins

Premium

In SQL, the LEFT JOIN and RIGHT JOIN are two types of outer joins that are used to combine data from two tables. They work similarly to an inner join, but they include all the rows from one of the tables and only the matching rows from the other table.

Here's the basic syntax for a LEFT JOIN:

SQL
SELECT table1.column1, table2.column2
FROM table1
LEFT JOIN table2
ON table1.column1 = table2.column2;

And here's the basic syntax for a RIGHT JOIN:

SQL
SELECT table1.column1, table2.column2
FROM table1
RIGHT JOIN table2
ON table1.column1 = table2.column2;

A LEFT JOIN returns all the rows from the left table (table1) and the matching rows from the right table (table2). If there is no matching row in the right table, the result set will contain NULL values for the columns from the right table.

A RIGHT JOIN works similarly, but does exactly the opposite: It returns all the rows from the right table (table2) and the matching rows from the left table (table1), with NULL values for the columns from the left table.

Example: Left vs. right join

Here's an example to help illustrate the difference between a left join and a right join. Similar to before, let's say you have a customers table and an orders table. The customers table contains information about each customer, and the orders table contains information about each order.

SQL
SELECT customers.name, orders.order_id, orders.order_date
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

This LEFT JOIN returns a result set that includes all the customers and the orders associated with them. If a customer doesn't have any orders, the result set will still include the customer with NULL values for the order_id and order_date columns.

SQL
SELECT customers.name, orders.order_id, orders.order_date
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

This RIGHT JOIN returns a result set that includes all the orders and the customers associated with them. If an order doesn't have a customer, the result set will still include the order with NULL values for the name column.

Are left and right joins interchangeable?

Yes! Any RIGHT JOIN can be expressed as a LEFT JOIN by simply swapping the positions of the two tables in the query. The difference between a LEFT JOIN and a RIGHT JOIN lies in which table's rows are included in the result set and which table's rows are used to fill in the NULL values for non-matching rows.

So, essentially, the difference between a LEFT JOIN and a RIGHT JOIN is just the order of the tables in the query, and the direction in which the NULL values are filled in.

Try it yourself

Try writing a query to join the data from the customers table and the orders table, returning all orders with their customer data.

Next Lesson