More complex JOINS – Many-To-Many Relationship

Back to: Structured Query Language (SQL)

---

In databases, data is often stored in multiple tables, making it necessary to combine them to fetch required information. MySQL JOIN statements enable merging tables based on common columns.

In this module, we’ll explore the MySQL LEFT JOIN keyword, a type of outer join that returns all records from the left table and matched records from the right table. We’ll cover its syntax, examples, and use cases to understand its functionality better.

One-To-Many

In the previous module we looked at two tables. One was the Customer table Customers which we only want to store data about ONCE. The Transactions table Orders can grow as new transactions occur, and “relates” back to specific customers from the Customers table. This is known as a one-to-many relationship.

In a relational database, a one-to-many relationship exists when one record is related to many records of another table. A one-to-many relationship is not a property of the data, but rather of the relationship itself. One-to-many often refer to a primary key to foreign key relationship between two tables, where the record in the first table can relate to multiple records in the second table. A foreign key is one side of the relationship that shows a row or multiple rows, with one of those rows being the primary key already listed on the first table.

Many-To-Many

Remember that the goal of a relational database is to minimise the amount of replicated data. There are many instances where a record in Table1 links to multiple records in Table2, whilst multiple records in Table2 also link to multiple record in Table1.

For example, an employee may be a member of multiple departments, and a department will contain more than one employee.

In the following example, we will look at Books and Authors. An author may have written multiple books, whilst a book may have more than one author. To deal with this we simply create a third table (called an associative table or join table or junction table) to link the two.

To understand the LEFT JOIN in a more depth manner, we need a table on which we will perform various operations. So here we take 3 tables called books, authors, and book_authors.

Query to create authors table:

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(255)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Hugh Jass'),
(2, 'Al Beback'),
(3, 'Bea O’Problem'),
(4, 'Carrie Oakey'),
(5, 'Dan Druff'),
(6, 'Paige Turner'),
(7, 'Rick Shaw'),
(8, 'Sue Flay'),
(9, 'Chris P. Bacon'),
(10, 'Anita Bath');

SELECT * FROM authors;

Query to create books table:

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(255),
    publication_year INT
);

INSERT INTO books (book_id, title, publication_year) VALUES
(101, 'Programming with Python', 2010),
(102, 'The Secret Database', 2015),
(103, 'Spreadsheets for Ever', 2020),
(104, 'Networks that Work', 2021),
(105, 'Python, More than a Snake', 2012),
(106, 'Web Development for Experts', 2015),
(107, 'Innovative Technologies', 2011),
(108, 'Elon Visits Mars', 2019);

SELECT * FROM books;

Query to create book_authors junction table:

CREATE TABLE book_authors (
    book_id INT,
    author_id INT,
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

INSERT INTO book_authors (book_id, author_id) VALUES
(101, 1),
(101, 2),
(102, 2),
(103, 3),
(104, 4),
(105, 5),
(106, 6),
(107, 7),
(108, 8),
(104, 9),
(105, 10),
(106, 10);

Here, book_id and author_id which are the PRIMARY KEY of books and authors table acts as a FOREIGN KEY for book_authors table.

After inserting some data into the tables, they look like:

Querying the database

MySQL LEFT JOIN with USING Clause:

SELECT *
FROM books
LEFT JOIN book_authors USING (book_id);

Output:

Explanation: The above query gets all the records from the left table books and the matching records from the right table book_authors. The query uses the USING Clause which performs the left join based on a column that is common to both the tables and has matching records.

Find authors and their books

How to get matching data across all three tables – here Book Author and Book Title:

SELECT authors.author_name, books.title
FROM authors
LEFT JOIN book_authors ON authors.author_id = book_authors.author_id
LEFT JOIN books ON book_authors.book_id = books.book_id;

Outputs:

Explanation: The query uses LEFT JOIN to combine the authors, book_authors, and books tables.

Find how many books each author has published using LEFT JOIN with GROUP BY Clause

We can use the GROUP BY clause with the LEFT JOIN keyword to view organized results based on specific columns.

Query:

SELECT authors.author_name, COUNT(books.book_id) as book_count
FROM authors
LEFT JOIN book_authors ON authors.author_id = book_authors.author_id
LEFT JOIN books ON book_authors.book_id = books.book_id
GROUP BY authors.author_name;

Outputs:

Explanation: The query uses LEFT JOIN to combine the authors, book_authors, and books tables. It groups results by author name using the GROUP BY clause, and the COUNT function calculates the number of books each author has written, providing the frequency of books per author.

Find authors and their book published prior to 2015 using WHERE clause

We can use the WHERE clause with LEFT JOIN to view the records that fulfill a specific request. this allows the filtering of data from tables.

SELECT authors.author_name, books.title
FROM authors
LEFT JOIN book_authors ON authors.author_id = book_authors.author_id
LEFT JOIN books ON book_authors.book_id = books.book_id
WHERE books.publication_year < 2015;

Outputs:

Explanation: WHERE Clause is used in MySQL to filter the result set based on a specific condition. This query performs a LEFT JOIN between the table authors and books to return the name of the author and the book he/she has written but only those authors who have published a book before 2015.

Find authors with no books (unmatched records)

This will not find any result because all of the authors are presently associated with at least one book – so I’ll add a new auther first.

Query:

-- Adding a new author who is NOT linked to any books
INSERT INTO authors
VALUES (11, 'Minnie Van');

-- The actual query to find authors with no books listed
SELECT authors.author_name
FROM authors
LEFT JOIN book_authors ON authors.author_id = book_authors.author_id
WHERE book_authors.author_id IS NULL;

Explanation: The query finds unmatched records between tables using a LEFT JOIN and a WHERE clause. For instance, it can identify customers who never placed an order. In this example, it finds authors with no books. No resultant rows indicate every author has published at least one book.

Displaying everything from all three tables

-- The query to list everything (here we can use JOIN, LEFT JOIN or RIGHT JOIN)
SELECT *
FROM authors
RIGHT JOIN book_authors ON authors.author_id = book_authors.author_id
RIGHT JOIN books ON book_authors.book_id = books.book_id;

Outputs:

Explanation: This query performs a join on more than two tables. You might encounter situations where you will have to join more than two tables to fetch the required data. So the above query performs a RIGHT JOIN on all three tables and returns all the records of the authors table along with the matching records of the other two books and book_authors tables.

---