MySQL Multi-Table Operations

Introduction

In this lab, we will explore the fundamentals of working with multiple tables in MySQL. Understanding how tables relate to each other and how to combine data from multiple tables is crucial for building real-world applications. We'll learn about table relationships, different types of JOIN operations, and how to maintain data integrity using foreign key constraints. By the end of this lab, you'll have hands-on experience with creating related tables, querying data across tables, and managing relationships between tables.

Understanding Table Relationships

In this step, we'll explore the concept of table relationships and examine how our tables are connected. Understanding table relationships is fundamental to working with relational databases.

First, let's connect to MySQL:

sudo mysql -u root

Once connected, select our database:

USE bookstore;

Let's examine the structure of our tables and their relationships:

SHOW CREATE TABLE books;

You'll see output that includes the foreign key constraints:

| books | CREATE TABLE `books` (
  `book_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `author_id` int(11) DEFAULT NULL,
  `publisher_id` int(11) DEFAULT NULL,
  `publication_year` int(11) DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`book_id`),
  KEY `author_id` (`author_id`),
  KEY `publisher_id` (`publisher_id`),
  CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`author_id`),
  CONSTRAINT `books_ibfk_2` FOREIGN KEY (`publisher_id`) REFERENCES `publishers` (`publisher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |

Let's break down the relationships in our database:

1. Each book has one author (many-to-one relationship)

   • The author_id in the books table references author_id in the authors table
   • Multiple books can have the same author

2. Each book has one publisher (many-to-one relationship)

   • The publisher_id in the books table references publisher_id in the publishers table
   • Multiple books can have the same publisher

To see these relationships in action, let's try a simple query to see how many books each author has written:

SELECT author_id, COUNT(*) as book_count
FROM books
GROUP BY author_id;

Basic INNER JOIN Operations

In this step, we'll learn about INNER JOIN, the most common type of join operation. An INNER JOIN returns only the rows where there is a match in both tables being joined.

Let's start with a simple INNER JOIN to get book titles along with their authors' names:

SELECT
    books.title,
    authors.first_name,
    authors.last_name
FROM books
INNER JOIN authors ON books.author_id = authors.author_id;

This query:

1. Starts with the books table (FROM books)
2. Joins it with the authors table (INNER JOIN authors)
3. Matches records based on author_id (ON books.author_id = authors.author_id)
4. Selects the columns we want to see

You should see output like:

+----------------------------+------------+-----------+
| title                      | first_name | last_name |
+----------------------------+------------+-----------+
| The MySQL Guide            | John       | Smith     |
| Data Design Patterns       | Emma       | Wilson    |
| Database Fundamentals      | Michael    | Brown     |
| SQL for Beginners          | Sarah      | Johnson   |
| Advanced Database Concepts | John       | Smith     |
+----------------------------+------------+-----------+

We can also join with the publishers table:

SELECT
    books.title,
    publishers.name as publisher_name,
    books.publication_year,
    books.price
FROM books
INNER JOIN publishers ON books.publisher_id = publishers.publisher_id;

+----------------------------+--------------------+------------------+-------+
| title                      | publisher_name     | publication_year | price |
+----------------------------+--------------------+------------------+-------+
| The MySQL Guide            | Tech Books Pro     |             2023 | 45.99 |
| Data Design Patterns       | Tech Books Pro     |             2022 | 39.99 |
| Database Fundamentals      | Database Press     |             2021 | 29.99 |
| SQL for Beginners          | Database Press     |             2023 | 34.99 |
| Advanced Database Concepts | Query Publications |             2023 | 54.99 |
+----------------------------+--------------------+------------------+-------+

LEFT JOIN Operations

In this step, we'll explore LEFT JOIN operations. A LEFT JOIN returns all records from the left table (the first table mentioned) and matching records from the right table. If there's no match, NULL values are returned for the right table's columns.

Let's add an author who hasn't published any books yet:

INSERT INTO authors (first_name, last_name, email)
VALUES ('David', 'Clark', '[email protected]');

Now, let's use a LEFT JOIN to see all authors, including those who haven't published books:

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

You should see output like:

+------------+-----------+------------+
| first_name | last_name | book_count |
+------------+-----------+------------+
| John       | Smith     | 2          |
| Emma       | Wilson    | 1          |
| Michael    | Brown     | 1          |
| Sarah      | Johnson   | 1          |
| David      | Clark     | 0          |
+------------+-----------+------------+

Notice that David Clark appears in the results with a book count of 0, even though he hasn't published any books. This is the key difference between LEFT JOIN and INNER JOIN.

Multiple Table Joins

In this step, we'll learn how to join more than two tables together. This is commonly needed when you need to combine data from several related tables.

Let's create a query that combines information from all three tables:

SELECT
    b.title,
    CONCAT(a.first_name, ' ', a.last_name) as author_name,
    p.name as publisher_name,
    b.publication_year,
    b.price
FROM books b
INNER JOIN authors a ON b.author_id = a.author_id
INNER JOIN publishers p ON b.publisher_id = p.publisher_id
ORDER BY b.title;

+----------------------------+---------------+--------------------+------------------+-------+
| title                      | author_name   | publisher_name     | publication_year | price |
+----------------------------+---------------+--------------------+------------------+-------+
| Advanced Database Concepts | John Smith    | Query Publications |             2023 | 54.99 |
| Data Design Patterns       | Emma Wilson   | Tech Books Pro     |             2022 | 39.99 |
| Database Fundamentals      | Michael Brown | Database Press     |             2021 | 29.99 |
| SQL for Beginners          | Sarah Johnson | Database Press     |             2023 | 34.99 |
| The MySQL Guide            | John Smith    | Tech Books Pro     |             2023 | 45.99 |
+----------------------------+---------------+--------------------+------------------+-------+

Note how we:

1. Used table aliases (b, a, p) to make the query more readable
2. Combined the author's first and last names using CONCAT()
3. Joined both the authors and publishers tables to the books table

Let's try another example that shows books and their authors, including authors without books, along with publisher information when available:

SELECT
    CONCAT(a.first_name, ' ', a.last_name) as author_name,
    b.title,
    p.name as publisher_name
FROM authors a
LEFT JOIN books b ON a.author_id = b.author_id
LEFT JOIN publishers p ON b.publisher_id = p.publisher_id
ORDER BY author_name;

+---------------+----------------------------+--------------------+
| author_name   | title                      | publisher_name     |
+---------------+----------------------------+--------------------+
| David Clark   | NULL                       | NULL               |
| Emma Wilson   | Data Design Patterns       | Tech Books Pro     |
| John Smith    | The MySQL Guide            | Tech Books Pro     |
| John Smith    | Advanced Database Concepts | Query Publications |
| Michael Brown | Database Fundamentals      | Database Press     |
| Sarah Johnson | SQL for Beginners          | Database Press     |
+---------------+----------------------------+--------------------+

Working with Foreign Key Constraints

In this final step, we'll explore how foreign key constraints help maintain data integrity between related tables. Foreign key constraints ensure that relationships between tables remain valid by preventing operations that would create orphaned records.

Let's try to understand how foreign key constraints work through some examples:

First, let's try to add a book with an invalid author_id:

-- This will fail due to foreign key constraint
INSERT INTO books (title, author_id, publisher_id, publication_year, price)
VALUES ('Failed Book', 999, 1, 2023, 29.99);

You'll see an error message because author_id 999 doesn't exist in the authors table:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bookstore`.`books`, CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`author_id`))```

Similarly, we can't delete an author who has published books without first dealing with their books:

```sql
-- This will fail due to foreign key constraint
DELETE FROM authors WHERE author_id = 1;

To safely delete an author and their books, we need to either:

1. Delete the books first, then the author
2. Use CASCADE DELETE (which we'll explore in advanced labs)

Let's see how to properly remove a book and its author:

-- First, delete the books by this author
DELETE FROM books WHERE author_id = 1;

-- Now we can safely delete the author
DELETE FROM authors WHERE author_id = 1;

Summary

In this lab, we've covered the essential aspects of working with multiple tables in MySQL:

1. Understanding table relationships and how they're implemented
2. Using INNER JOIN to combine matching records from multiple tables
3. Using LEFT JOIN to include all records from one table with matching records from another
4. Combining data from multiple tables using multiple joins
5. Working with foreign key constraints to maintain data integrity

These skills form the foundation for working with relational databases effectively. Understanding how to combine and relate data across multiple tables is crucial for building robust database applications.