MySQL Part 9: Joins

Introduction

In this article, we discuss joining data in MySQL.  In the last article, we discussed logical operators.  Click the link below to check out that article.

MySQL Part 8: Logical Operations

To begin your MySQL session in the goormIDE, please be sure to type this code into the terminal.

mysql-ctl cli

Please ensure that the bike_shop.sql file is selected and available.  If not, double-click it on the left-hand side.  This is where we will type most of our code. If there is information saved in there, go ahead and clear out that information.

Joins

Joins in databases are very powerful and help combine two or more tables.  If you are familiar with VLOOKUPs in Excel, they somewhat behave in similar fashions.

There are many different ways to join data.  This article will focus specifically on left joins, but the images for the other join types are listed below.  

Source: https://www.dofactory.com/sql/join

Data

Before we get started on the joins, let's familiarize ourselves with our datasets.  

SELECT * FROM bikes; 

SELECT * FROM customers; 

SELECT * FROM orderlines;

Join Example Part 1

In this example, we will combine the orderlines table with the bikes table.  This join allows us to reference the actual name of the bike with the orders.  A technical definition of a left join is provided below.

"A left join requires a join predicate. When joining two tables using a left join, the concepts of left and right tables are introduced.

The left join selects data starting from the left table. For each row in the left table, the left join compares with every row in the right table.

If the values in the two rows satisfy the join condition, the left join clause creates a new row whose columns contain all columns of the rows in both tables and includes this row in the result set.

If the values in the two rows are not matched, the left join clause still creates a new row whose columns contain columns of the row in the left table and NULL for columns of the row in the right table.

In other words, the left join selects all data from the left table whether there are matching rows existing in the right table or not.

In case there are no matching rows from the right table found, the left join uses NULLs for columns of the row from the right table in the result set."  - https://www.mysqltutorial.org/mysql-join/.

The column we reference in the orderlines table will be the product_id.  The column we need to match in the bikes table is the bike_id column.

The orderlines table originally had 40 rows.  After the left join, we still maintained the 40 rows, but we added additional columns that are highlighted below.

SELECT * 
    FROM orderlines

    LEFT JOIN bikes
    ON orderlines.product_id = bikes.bike_id;

Join Example Part 2

In this example, combine the orderlines table, the bikes table, and the customers table.  This join will combine all the data in all three tables.  These types of joins are common in the real world and often are more complicated than the examples presented.  Keep in mind, we are only discussing left joins in this article.  For more information about the other common joins, please check out https://www.mysqltutorial.org/mysql-join/.

In the code below, we will do the same as before, but we will add the join from the orderlines table to the customers table.  The customer_id from the orderlines table will join to the bike_shop_id from the customers table.

SELECT * 
    FROM orderlines

    LEFT JOIN bikes
    ON orderlines.product_id = bikes.bike_id
    
    LEFT JOIN customers
    ON orderlines.customer_id = customers.bike_shop_id;

Conclusion

This article demonstrated a straightforward example of how to join three tables together.  The benefit of this exercise is that you can have more meaningful data for analysis later on.  The links to the other articles are listed below.  These articles help you set up in the goormIDE, help you get the data loaded into MySQL, and other helpful MySQL functions and commands.

For a complete list of the entire set of MySQL-related articles, please see the links below.

MySQL Part 1: goormIDE
MySQL Part 2: Create Database
MySQL Part 3: Create Tables
MySQL Part 4: String Functions
MySQL Part 5: Refining Select Statements
MySQL Part 6: Aggregations
MySQL Part 7: Dates
MySQL Part 8: Logical Operations
MySQL Part 9: Joins