MySQL - 完全連接 (Full Join)

Hello there, aspiring database enthusiasts! Today, we're going to dive into the exciting world of MySQL and explore a powerful tool in our SQL toolkit: the Full Join. Don't worry if you're new to programming; I'll guide you through this concept step by step, just as I've done for countless students over my years of teaching. So, grab a cup of coffee (or tea, if that's your preference), and let's embark on this learning adventure together!

What is a Full Join?

Before we jump into the nitty-gritty of Full Joins in MySQL, let's start with a simple analogy. Imagine you're planning a party, and you have two guest lists: one from your best friend and one from your sibling. A Full Join is like combining these lists in a way that includes everyone from both lists, even if they appear on only one list.

In database terms, a Full Join combines rows from two or more tables based on a related column between them, including all rows from all tables, even if there isn't a match in the other table(s).

The Syntax

Here's the basic syntax for a Full Join in SQL:

SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

Now, I can almost hear you saying, "But wait! MySQL doesn't have a FULL JOIN keyword!" And you'd be absolutely right! MySQL doesn't directly support FULL JOIN, but fear not – we have a clever workaround using UNION. Let's explore this in detail.

MySQL Full Join (Using UNION)

Since MySQL doesn't have a built-in FULL JOIN, we can simulate it using a combination of LEFT JOIN and RIGHT JOIN with UNION. Here's how it works:

SELECT t1.column1, t1.column2, t2.column1, t2.column2
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
UNION
SELECT t1.column1, t1.column2, t2.column1, t2.column2
FROM table1 t1
RIGHT JOIN table2 t2 ON t1.id = t2.id;

Let's break this down:

1. We perform a LEFT JOIN to get all rows from table1, along with matching rows from table2.
2. We then UNION this with a RIGHT JOIN, which gets all rows from table2, along with matching rows from table1.
3. The UNION combines these results, effectively giving us a FULL JOIN.

A Practical Example

To make this more concrete, let's use a real-world example. Imagine we're managing a small library, and we have two tables: books and borrowers.

First, let's create these tables:

CREATE TABLE books (
book_id INT PRIMARY KEY,
title VARCHAR(100),
author VARCHAR(100)
);

CREATE TABLE borrowers (
borrower_id INT PRIMARY KEY,
book_id INT,
borrower_name VARCHAR(100)
);

INSERT INTO books VALUES (1, 'To Kill a Mockingbird', 'Harper Lee');
INSERT INTO books VALUES (2, '1984', 'George Orwell');
INSERT INTO books VALUES (3, 'Pride and Prejudice', 'Jane Austen');

INSERT INTO borrowers VALUES (1, 1, 'Alice');
INSERT INTO borrowers VALUES (2, NULL, 'Bob');
INSERT INTO borrowers VALUES (3, 4, 'Charlie');

Now, let's perform our Full Join:

SELECT b.book_id, b.title, br.borrower_id, br.borrower_name
FROM books b
LEFT JOIN borrowers br ON b.book_id = br.book_id
UNION
SELECT b.book_id, b.title, br.borrower_id, br.borrower_name
FROM books b
RIGHT JOIN borrowers br ON b.book_id = br.book_id;

This query will give us the following result:

book_id	title	borrower_id	borrower_name
1	To Kill a Mockingbird	1	Alice
2	1984	NULL	NULL
3	Pride and Prejudice	NULL	NULL
NULL	NULL	2	Bob
NULL	NULL	3	Charlie

Let's analyze this result:

• We see all books, even those not borrowed (like '1984' and 'Pride and Prejudice').
• We see all borrowers, even those not currently borrowing a book (like Bob).
• We even see Charlie, who is borrowing a book not in our books table (book_id 4).

This is the power of a Full Join – it gives us a complete picture of our data, highlighting both matches and mismatches between our tables.

Full Join with WHERE Clause

Sometimes, we might want to filter our Full Join results. We can do this by adding a WHERE clause to our query. For example, let's say we want to find all books that aren't currently borrowed:

SELECT b.book_id, b.title, br.borrower_id, br.borrower_name
FROM books b
LEFT JOIN borrowers br ON b.book_id = br.book_id
WHERE br.borrower_id IS NULL
UNION
SELECT b.book_id, b.title, br.borrower_id, br.borrower_name
FROM books b
RIGHT JOIN borrowers br ON b.book_id = br.book_id
WHERE b.book_id IS NULL;

This query will return:

book_id	title	borrower_id	borrower_name
2	1984	NULL	NULL
3	Pride and Prejudice	NULL	NULL
NULL	NULL	2	Bob
NULL	NULL	3	Charlie

This result shows us:

• Books that aren't borrowed ('1984' and 'Pride and Prejudice')
• Borrowers who aren't currently borrowing a book (Bob)
• Borrowers who are borrowing a book not in our books table (Charlie)

Conclusion

And there you have it, my dear students! We've journeyed through the land of Full Joins in MySQL, from understanding the concept to implementing it with real-world examples. Remember, while MySQL doesn't have a built-in FULL JOIN, we can achieve the same result using a combination of LEFT JOIN, RIGHT JOIN, and UNION.

Full Joins are incredibly useful when you need to see all data from multiple tables, regardless of whether there are matching values between them. They're like the inclusive friend at a party who makes sure everyone is involved, whether they know each other or not!

As you continue your MySQL adventure, keep practicing with different scenarios. The more you play with these concepts, the more comfortable you'll become. And who knows? You might just find yourself becoming the go-to database guru among your friends!

Happy coding, and may your queries always return the results you're looking for!

Credits: Image by storyset