[SQL Basic] Let’s check movie rentals by the inventory list! — SQL LEFT / RIGHT JOIN

SQLGate

17 Sep 2018 • 7 min read

#14. Marketing/Data Analysis for Beginners

#Glossary:

📌Lesson 3: SELECT / FROM / WHERE
📌Lesson 4: ORDER BY
📌Lesson 5: AND / OR / IN
📌Lesson 6: LIKE
📌Lesson 7: AS /DISTINCT / IS NULL
📌Lesson 9: COUNT / SUM
📌Lesson 10: AVG / MAX / MIN
📌Lesson 11: GROUP BY / HAVING
📌Lesson 12: CASE
📌Lesson 13: INNER JOIN

🆕 🌟
📌OUTER LEFT JOIN: combine tables — returns all rows in the left table (table1) and only matching rows in the right table(table2)
📌OUTER RIGHT JOIN: combine tables — returns all rows in the right table (table2) and only matching rows in the left table(table1)

In the previous tutorial we learned how to combine tables with the INNER JOIN query. Today we’re going to cover the RIGHT JOIN and LEFT JOIN queries.

#Table of Contents

Query 1. Use the inventory_id column to combine the rental and inventory tables! (LEFT JOIN/rental_id, customer_id, film_id, inventory_id)

select a.rental_id, a.customer_id, b.film_id, b.inventory_id from rental a left join inventory b on a.inventory_id=b.inventory_id;

Query 2. Use the inventory_id column to combine the rental and inventory tables! (RIGHT JOIN/rental_id, customer_id, film_id, inventory_id)

select a.rental_id, a.customer_id, b.film_id, b.inventory_id from rental a right join inventory b on a.inventory_id=b.inventory_id;

🔑 INNER JOIN? OUTER JOIN? What‘s the difference?

INNER JOIN combines tables based on data from a column they have in common. But when we use INNER JOIN, only the information that is exact in both tables shows up. For example, let’s say table1 has information on the movie Die Hard but table2 doesn’t have any information on it. Then Die Hard won’t show up at all in the combined table we get at the result of INNER JOIN, it will just be omitted.

The LEFT/RIGHT JOIN that we’re going to cover today will help us see the difference in the column the two tables have in common. Using the Die Hard example, if we use LEFT JOIN to see information based on table1, then the movie name would be visible. And if we use RIGHT JOIN to see information based on table2, Die Hard wouldn’t be visible.

❓❓Feeling confused? Keep reading to learn more about RIGHT and LEFT join!

⭐️We mentioned before that it’s not necessary to write “inner” for INNER JOIN to work, you can just write JOIN (but it is good practice!). The same goes for OUTER RIGHT JOIN/OUTER LEFT JOIN- you don’t need to write “outer”. You can just write RIGHT JOIN / LEFT JOIN.

#Left Join

Q 1. Use the inventory_id column to combine the rental and inventory tables! (LEFT JOIN/rental_id, customer_id, film_id, inventory_id)

SELECT (column name/if using columns from multiple tables then‘table name.column name’)
FROM (table 1) <- the table that combination is based on
LEFT JOIN (table2)
ON (table1.column) = (table2.column)

📌LEFT JOIN refers to the left table (table 1) when we combine two tables. Imagine two tables side-by-side. The first one (table 1) would be on the left. This query returns all the rows from the left table and only the matching rows from the right table.

First, write the column name of the data you want to see after SELECT (table name or alias.column name). As mentioned above, LEFT JOIN will focus on table 1 — which is the first table name you write after FROM. So if you write your query as FROM rental, then the data from the inventory table will be combined based on the rental table as shown in the table below:

Let’s try it! First write the columns you want to see (rental_id, customer_id from the rental table and film_id, inventory_id from the inventory table). Remember that we can use aliases. Let’s use 🅰️ and 🅱️ for our two tables.

🅰️ rental table
🅱️ inventory table

Using LEFT JOIN, we can combine the tables with 🅰️ as the base table. And using ON we can specify the matching columns we want combined.

SELECT a.rental_id, a.customer_id, b.film_id, b.inventory_id FROM rental a LEFT JOIN inventory b ON a.inventory_id=b.inventory_id;

The left table is the main table here, meaning even the organization is based on the left table (table 1: rental)! All the rows in table1 are shown! Now let’s explore what it looks like when we use RIGHT TABLE.

#Right Join

Q2. Use the inventory_id column to combine the rental and inventory tables! (RIGHT JOIN/rental_id, customer_id, film_id, inventory_id)

SELECT (column name/if using columns from multiple tables then‘table name.column name’)
FROM (table1)
RIGHT JOIN (table 2) <- the table that combination is based on
ON (table1.column) = (table2.column)

Let’s use the same example but use RIGHT JOIN this time.

📌RIGHT JOIN refers to the right table (table 2) when we combine two tables. Imagine two tables side-by-side. The first one (table 1) would be on the left. The second table (table2) would be on the right. This query returns all the rows from the right table and only the matching rows from the other table.

The overall query is the same, but this time the columns are combined based on the right table (table2) which we identify second. If you write FROM rental RIGHT JOIN inventory then you’ll get a result like the one below:

If we run the same query but use RIGHT JOIN, you’ll see something different. This time where the film ID is 1, and the inventory ID is 5, you’ll get two null values in rental ID and customer ID. That’s because the left table(table 1) doesn’t have any information on this row. But we’re joining based on the right table(table2) which does have this information, so we’re given nulls.

This is where the data from the two tables doesn’t match, meaning there’s no record of film 1 being rented out from inventory #5. This way you can see the difference between the left and right table, and pinpoint what wasn’t immediately visible when you used only the rental table with LEFT JOIN (left table/table1).

To double-check, try searching for inventory #5 in your rental table!

SELECT * FROM rental ORDER BY inventory_id ASC;

SELECT * FROM rental WHERE inventory_id=5;

#Practice Time

📰 Let’s combine the address table and the city table by city_id column. (LEFT JOIN and RIGHT JOIN difference)

Let’s use our city and address tables to try out our new LEFT JOIN and RIGHT JOIN queries. Here is a preview of our address and city table, and the outcomes we want. Take a moment to check out the picture:

Here is the query for the LEFT JOIN;

SELECT address.address, address.address2, city.city, city.city_id FROM address LEFT JOIN city ON address.city_id=city.city_id;

And the query for the RIGHT JOIN;

SELECT address.address, address.address2, city.city, city.city_id FROM address RIGHT JOIN city ON address.city_id=city.city_id;

If you run one query at a time, you will see a message at the bottom of the window (highlighted with red in our example images) that they return a different amount of rows. The LEFT JOIN will give you 603 rows. The RIGHT JOIN shows 604 rows.

Now just glancing at the LEFT JOIN results, we can see some null values in the address2 column. Let’s search for all the null values to see how many we have in each table. Try using the WHERE query:

👈 LEFT : based on table1
SELECT address.address, address.address2, city.city, city.city_id FROM address LEFT JOIN city ON address.city_id=city.city_id WHERE address2 IS NULL;

👉 RIGHT : based on table2
SELECT address.address, address.address2, city.city, city.city_id FROM address RIGHT JOIN city ON address.city_id=city.city_id WHERE address2 IS NULL;

Take a look at the results: