Sling Academy
Home/MySQL/Left Join in MySQL 8: A Practical Guide

Left Join in MySQL 8: A Practical Guide

Last updated: January 27, 2024

Introduction

Join operations are a cornerstone of relational database systems, allowing for the combination of rows from two or more tables based on related columns. The LEFT JOIN, specifically, is powerful for querying data from multiple tables where records in the left table might not have matching records in the right table. This tutorial covers the LEFT JOIN in MySQL 8 with practical examples.

Understanding LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match. The syntax for a LEFT JOIN is as follows:

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Basic LEFT JOIN Example

Start with two simple tables, customers and orders:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT
);

Basic query using LEFT JOIN:

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

Output:

| name         | order_date |
|--------------|------------|
| John Doe     | NULL       |
| Jane Smith   | 2021-07-08 |
| Alice Johnson| NULL       |

Here, customers without orders will show NULL for order_date.

Filtering Results with WHERE Clause

Sometimes, you want to filter the results obtained from a LEFT JOIN:

SELECT customers.name, orders.order_date
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id
WHERE orders.order_date IS NULL;

Output will display customers without orders.

Joining Multiple Tables

A LEFT JOIN can also be used to join more than two tables. For Example:

SELECT customers.name, orders.order_date, products.product_name
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
LEFT JOIN order_details ON orders.order_id = order_details.order_id
LEFT JOIN products ON order_details.product_id = products.product_id;

Even though this is more complex, the fundamental LEFT JOIN concept remains the same.

Using LEFT JOIN with Aggregate Functions

LEFT JOIN can be effectively paired with aggregate functions like SUM, AVG, COUNT, etc.:

SELECT customers.name, COUNT(orders.order_id) AS number_of_orders
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.name;

Output:

| name         | number_of_orders |
|--------------|------------------|
| John Doe     | 0                |
| Jane Smith   | 2                |
| Alice Johnson| 0                |

This query provides the total number of orders per customer.

Advanced LEFT JOIN Techniques

Now, let’s dive into advanced LEFT JOIN techniques such as using subqueries and joins with using conditions.

LEFT JOIN with Subqueries

SELECT customers.name, order_info.latest_order_date
FROM customers
LEFT JOIN (
    SELECT customer_id, MAX(order_date) AS latest_order_date
    FROM orders
    GROUP BY customer_id
) AS order_info ON customers.customer_id = order_info.customer_id;

This gives you the latest order date for each customer.

LEFT JOIN with USING Clause

When the columns in both tables have the same names, you can use the USING clause:

SELECT customers.name, orders.order_date
FROM customers
LEFT JOIN orders USING (customer_id);

This simplifies the syntax when column names match.

NULL Handling with COALESCE Function

COALESCE function can be used with LEFT JOIN to handle NULL values more gracefully:

SELECT customers.name, COALESCE(orders.order_date, 'No Order') AS order_status
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

Customers without orders show ‘No Order’ instead of NULL.

Conclusion

LEFT JOIN is crucial for constructing queries that require information from multiple tables while also dealing with the absence of related records. This guide provides insight into various LEFT JOIN applications to enhance your SQL querying capabilities.

Next Article: Using LEFT JOIN with GROUP BY in MySQL 8: A Practical Guide

Previous Article: Inner Joins in MySQL 8: A Practical Guide

Series: MySQL Tutorials: From Basic to Advanced

MySQL

You May Also Like

  • MySQL: How to reset the AUTO_INCREMENT value of a table
  • MySQL: How to add a calculated column to SELECT query
  • MySQL: Eliminate orphan rows in one-to-many relationships
  • MySQL: Using R-Tree Indexes for Spatial Data Types
  • How to Create Custom Collations in MySQL
  • Using Hash Indexes in MySQL: A Practical Guide
  • Understanding Full-Text Indexes in MySQL
  • Partial Indexes in MySQL: A Practical Guide
  • MySQL: How to Remove FOREIGN KEY Constraints
  • Using ENUM in MySQL 8: A Practical Guide (with Examples)
  • MySQL: Creating a Fixed-Size Table by Using Triggers
  • One-to-Many Relationship in MySQL 8: A Practical Guide
  • Using Regular Expressions in MySQL 8: The Complete Guide
  • Using Loops in MySQL: A Practical Guide (with Examples)
  • How to Execute an SQL File in VS Code
  • Making use of the JSON_REMOVE() function in MySQL 8
  • MySQL 8: How to count rows in related tables
  • Replication in MySQL 8: A Comprehensive Guide
  • MySQL 8: FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() functions – Explained with examples