JOIN three tables

Mastering SQL JOINs: Combining Data from Three Tables

Learn how to effectively join three or more tables in SQL to retrieve comprehensive datasets, covering essential concepts and practical examples for MySQL.

In relational databases, data is often distributed across multiple tables to maintain normalization and reduce redundancy. To retrieve a complete picture of related information, you frequently need to combine data from several tables. This article focuses on the fundamental technique of joining three tables using SQL, a common requirement in database queries. We'll explore the logic behind multi-table joins and provide practical examples using MySQL.

Understanding Multi-Table JOINs

Joining multiple tables extends the concept of a two-table join. Each subsequent JOIN clause connects a new table to the result set formed by the previous joins. The key is to identify the common columns (foreign keys) that link these tables together. For instance, if you have Customers, Orders, and Order_Items tables, you might join Customers to Orders on customer_id, and then join the result to Order_Items on order_id.

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--o{ ORDER_ITEM : contains
    CUSTOMER { 
        INT customer_id PK
        VARCHAR name
        VARCHAR email
    }
    ORDER { 
        INT order_id PK
        INT customer_id FK
        DATE order_date
        DECIMAL total_amount
    }
    ORDER_ITEM { 
        INT item_id PK
        INT order_id FK
        INT product_id
        INT quantity
        DECIMAL price
    }

Entity-Relationship Diagram for Customer, Order, and Order_Item tables

The most common type of join used for combining related data is the INNER JOIN. An INNER JOIN returns only the rows where there is a match in all joined tables. If a row in one table does not have a corresponding match in the other joined tables, it will not be included in the result set. Other join types like LEFT JOIN or RIGHT JOIN can be used when you need to include all rows from one table, even if there are no matches in the others.

Setting Up Sample Tables

Before we dive into the join queries, let's create some sample tables and populate them with data. This will allow us to demonstrate the JOIN operations effectively. We'll use three tables: authors, books, and genres.

CREATE TABLE authors (
    author_id INT PRIMARY KEY AUTO_INCREMENT,
    author_name VARCHAR(100) NOT NULL
);

CREATE TABLE genres (
    genre_id INT PRIMARY KEY AUTO_INCREMENT,
    genre_name VARCHAR(50) NOT NULL
);

CREATE TABLE books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    author_id INT,
    genre_id INT,
    publication_year INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id),
    FOREIGN KEY (genre_id) REFERENCES genres(genre_id)
);

INSERT INTO authors (author_name) VALUES
('J.K. Rowling'),
('George Orwell'),
('Jane Austen');

INSERT INTO genres (genre_name) VALUES
('Fantasy'),
('Dystopian'),
('Classic'),
('Romance');

INSERT INTO books (title, author_id, genre_id, publication_year) VALUES
('Harry Potter and the Sorcerer''s Stone', 1, 1, 1997),
('1984', 2, 2, 1949),
('Pride and Prejudice', 3, 3, 1813),
('Animal Farm', 2, 2, 1945),
('Harry Potter and the Chamber of Secrets', 1, 1, 1998);

SQL statements to create and populate sample tables

Joining Three Tables with INNER JOIN

To retrieve information about books, their authors, and their genres, we need to join all three tables. We'll start by joining books with authors on author_id, and then join the result of that operation with genres on genre_id.

SELECT
    b.title,
    a.author_name,
    g.genre_name,
    b.publication_year
FROM
    books b
INNER JOIN
    authors a ON b.author_id = a.author_id
INNER JOIN
    genres g ON b.genre_id = g.genre_id
ORDER BY
    b.title;

SQL query to join three tables (books, authors, genres)

In this query:

• FROM books b: We start with the books table and give it an alias b for brevity.
• INNER JOIN authors a ON b.author_id = a.author_id: This joins books with authors using the author_id column, which is common to both tables. We alias authors as a.
• INNER JOIN genres g ON b.genre_id = g.genre_id: This then joins the result of the previous join with the genres table, using the genre_id column. We alias genres as g.
• SELECT b.title, a.author_name, g.genre_name, b.publication_year: We select the desired columns from each of the joined tables.

Handling Missing Data with LEFT JOIN

What if you wanted to see all authors, even those who haven't written any books in our books table, along with their book and genre information if available? An INNER JOIN would exclude such authors. In this scenario, a LEFT JOIN is more appropriate. A LEFT JOIN returns all rows from the left table (the first table in the FROM clause or the table immediately preceding the LEFT JOIN clause) and the matching rows from the right table. If there's no match, NULL values will appear for the right table's columns.

INSERT INTO authors (author_name) VALUES ('New Author');

SELECT
    a.author_name,
    b.title,
    g.genre_name
FROM
    authors a
LEFT JOIN
    books b ON a.author_id = b.author_id
LEFT JOIN
    genres g ON b.genre_id = g.genre_id
ORDER BY
    a.author_name, b.title;

SQL query using LEFT JOIN to include all authors

In this LEFT JOIN example, 'New Author' will appear in the result set, but their title and genre_name columns will show NULL because they have no corresponding entries in the books table. Notice how the order of joins matters with LEFT JOIN – the 'left' table dictates which rows are always included.