Mastering MySQL JOINS

MySQL JOIN Explained with Real World Examples

JOINs in MySQL

This article explores the various types of SQL joins — INNER JOIN, LEFT JOIN, RIGHT JOIN, SELF JOIN, and FULL OUTER JOIN. It explains how each join operates, provides real-world examples, and addresses scenarios involving duplicate records.

What is a JOIN?

A join in a database links records between one or more tables based on a related column. This allows for the retrieval of data that is distributed across multiple tables.

Why Use a JOIN?

In a relational database, data is typically normalized and organized across multiple tables to minimize redundancy. When we need to retrieve information about a specific activity or object that spans several tables, we can use the SQL JOIN clause to query and combine the necessary data from multiple tables.

In this article, we will explore four types of joins supported by MySQL: INNER JOIN, LEFT JOIN, RIGHT JOIN, and SELF JOIN, as well as how to emulate a FULL OUTER JOIN, which is not directly supported by MySQL. To illustrate the differences among these joins, we will examine the Electro Sphere database, which contains the following tables.

The first table is the Clients table, containing the following columns:

• ClientID
• FullName
• ContactNumber

Clients Table

The second table is the Orders table, which includes the following columns:

• OrderID
• ClientID
• ProductID
• Quantity
• Cost
• Date

Orders Table

INNER JOIN

This type of JOIN retrieves records with matching values in the joined tables.

For example, if we want to obtain the Client ID, Full names and Order IDs of clients who have placed orders, we can use the INNER JOIN clause. This allows us to extract data from both the Clients and Orders tables based on the matching client ID values, as shown below.

SELECT clients.ClientID,
       clients.FullName,
       orders.OrderID
FROM   clients
INNER JOIN orders
      ON clients.ClientID = orders.ClientID;

This query selects the ClientID and FullName from the clients table and the OrderID from the orders table, where the ClientID matches in both tables.

The result of this query is as follows:

LEFT JOIN

A LEFT JOIN retrieves all records from the left table and the matched records from the right table. If there’s no match, NULL values are returned for the right table’s columns. This is useful for retrieving all entries from the left table, even when there are no corresponding entries in the right table.

For instance, if we want to list all clients along with their orders, including those who haven’t placed any orders, we would use a LEFT JOIN.

SELECT clients.ClientID,
       clients.FullName,
       orders.OrderID
FROM   clients
LEFT JOIN orders
      ON clients.ClientID = orders.ClientID;

The result of this query is as follows:

This returns all clients along with their corresponding orders, showing NULL for OrderID where no orders exist.

RIGHT JOIN

A RIGHT JOIN retrieves all records from the right table and the matched records from the left table. If there’s no match, NULL values are returned for the left table’s columns.

For example, if we want to list all orders along with the corresponding clients, including orders that do not have associated clients, we would use a RIGHT JOIN.

-- RIGHT JOIN
SELECT clients.ClientID,
       clients.FullName,
       orders.OrderID
FROM   clients
RIGHT JOIN orders
      ON clients.ClientID = orders.ClientID;

The result of this query is as follows:

SELF JOIN

A self join is a special type of join that allows a table to be joined with itself, enabling the comparison of rows or retrieval of related data sharing a common attribute. We can use INNER JOIN, LEFT JOIN, or RIGHT JOIN for this purpose.

For example, in a table of employees, if we want to find employees and their managers (where both employees and managers are listed in the same table), we can use a self join.

Employee Table

We have to join the table on the ManagerID to match it with the EmployeeID.

-- SELF JOIN
SELECT e1.EmployeeID, 
       e1.FullName AS EmployeeName, 
       e1.Role, 
       e2.FullName AS ManagerName
FROM Employee e1
LEFT JOIN Employee e2 ON e1.ManagerID = e2.EmployeeID;

The result of the query is as follows:

FULL OUTER JOIN

It returns all rows from both tables, and if there is no match, NULL values are returned for the columns of the table that lacks the matching row.

MySQL supports INNER, LEFT, and RIGHT JOINS, but does not support FULL OUTER JOIN. To emulate a FULL OUTER JOIN in MySQL, we can use a combination of LEFT JOIN and RIGHT JOIN, along with UNION ALL/ UNION. We can use UNION ALL to include duplicates in the result set or UNION to retrieve only unique records.

SELECT clients.ClientID, 
       clients.FullName, 
       orders.OrderID
FROM clients
LEFT JOIN orders ON clients.ClientID = orders.ClientID

UNION 

SELECT clients.ClientID, 
       clients.FullName, 
       orders.OrderID
FROM clients
RIGHT JOIN orders ON clients.ClientID = orders.ClientID;

The output of the query is as follows:

Join Operations with Duplicate Records

What happens when there are duplicate records in both the left and right tables? Let’s consider two simple tables, data_a and data_b, with sample data.

We will examine the result set obtained from INNER JOIN, LEFT JOIN, and RIGHT JOIN operations, and discuss the values that would appear in the resulting joined table for each matching scenario.

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

In conclusion, understanding the different types of SQL joins is essential for effective data manipulation and analysis in MySQL. By mastering these joins, you can gain deeper insights from your databases, ensuring that you retrieve the most relevant information for your applications.

I’d love to hear your thoughts — please share your experiences in the comments!

Github: sql-advanced-topics/MySQL Joins.sql at main · suchow07/sql-advanced-topics