As discussed in the previous lesson, INNER joins return all results where there is a matching record in each table. But sometimes, you need all the records from one table whether or not there is a matching record in the join table. In this situation, you use SQL LEFT JOIN and/or SQL RIGHT JOIN.
What is SQL Left Join?
SQL LEFT JOIN will return all rows from the left table, which is the first table mentioned in the JOIN clause, even if there's no matching record in the right table.
What is SQL Right Join?
A SQL RIGHT JOIN will return all rows from the right table (the second one mentioned in the JOIN clause), even if there's no matching record in the left table.
Left Join vs. Right Join
SQL LEFT JOIN vs RIGHT JOIN can also be thought of as which one you read first and second. If you want all the records from the table you read first in the query (whether or not there's a match in the other table), it's a LEFT join. If you want all the records from the table you read second in the query (whether or not there are matches in the other table), it's a RIGHT join.
Note: The following example uses the MySQL Sakila demo database. If you don't already have it installed, follow the steps to install the Sakila DB, and head back here.
Example of Left Join vs. Right Join
Suppose you want to get a list of all customers from the customer table, and you also want to return a list of actors from the actor table that share the same first or last name as the customer.
That said, you need the entire list of customers, whether or not there is an actor with the same first or last name. In this case, you can use a LEFT JOIN to get all records from the customer table, as well as any matching records from the actor table. When there is no match in the actor table, the result will just show NULL for those fields.
Left Join Example
SELECT
c.first_name,
c.last_name,
a.first_name,
a.last_name
FROM customer c
LEFT JOIN actor a
ON c.last_name = a.last_name
ORDER BY c.last_name;
Notice how you read the customer table before you read the actor table? Because you're using a LEFT join, it will select all records from the customer table as well as any matching records from the actor table. If there's no match in the actor table, it will just show NULL.
Right Join Example
SELECT
c.first_name,
c.last_name,
a.first_name,
a.last_name
FROM customer c
RIGHT JOIN actor a
ON c.last_name = a.last_name
Notice how you read the customer table before you read the actor table? Because you're using a RIGHT JOIN, it will select all records from the actor table as well as any matching records from the customer table. If there's no match in the customer table, it will show NULL.
Right / Left Join vs Inner Join
Now that you've seen the examples of RIGHT JOIN and LEFT JOIN above, suppose you only want records from the actor table and the customer table where there is a match. You don't care about records from either table where there is not a match. In this case, you'd use the default INNER join.
Remember here that you don't have to type the word INNER as the INNER JOIN is the default join MySQL uses when you use the keyword JOIN all by itself.
SELECT
c.first_name,
c.last_name,
a.first_name,
a.last_name
FROM customer c
INNER JOIN actor a
ON c.last_name = a.last_name
Summary: SQL Left Join and Right Join
- The SQL
LEFT JOINandRIGHT JOINcommands are used to select all results from one table, and ONLY the matching results from the other. LEFT JOINtakes all results from the first table, and only matching results from the second tableRIGHT JOINtakes all results from the second table, and only matching results from the first tableLEFTandRIGHTare synonymous to the first and second tables written in the query
Left Join / Right Join Syntax
Here's the syntax for the SQL LEFT JOIN and RIGHT JOIN commands, where you can substitute the variables in < > with your values.
SELECT <YOUR_COLUMNS>
FROM <YOUR_TABLE_1>
<LEFT_or_RIGHT> JOIN <YOUR_TABLE_2>
ON <YOUR_TABLE_1.COLUMN> = <YOUR_TABLE_2.COLUMN>
You're doing great! Now it's time to watch a video summing up what you just learned.
Shout out to quackit.com for inspiring some good LEFT and RIGHT join examples for this page.