Giới thiệu về mệnh đề MySQL JOIN
Cơ sở dữ liệu quan hệ bao gồm nhiều bảng có liên quan liên kết với nhau bằng cách sử dụng các cột chung, được gọi là cột khóa ngoại ( foreign key). Do đó, dữ liệu trong mỗi bảng có thể là không đầy đủ những thông tin cần thiết để show ra.
Ví dụ, trong cơ sở dữ liệu mẫu , chúng ta có các bảng ordersvà orderdetails được liên kết bằng cột orderNumber:
Để có được thông tin đầy đủ của đơn đặt hàng, bạn cần truy vấn dữ liệu từ cả hai ordersbảng orderdetails.
Đó là lý do JOIN xuất hiện.
JOIN là một phương pháp liên kết dữ liệu giữa một (self-join) hoặc nhiều bảng dựa trên các giá trị của cột chung giữa các bảng.
MySQL hỗ trợ các kiểu kết nối sau:
Để nối các bảng, bạn sử dụng mệnh đề cross join, inner join, left join, hoặc right join. Mệnh đề nối được dùng trong mệnh đề SELECT xuất hiện sau mệnh đề FROM.
Lưu ý rằng MySQL chưa hỗ trợ FULL OUTER JOIN.
Thiết lập bảng mẫu (Setting up sample tables).
Đầu tiên, tạo hai bảng có tên là membersvà committees:
CREATE TABLE members (
member_id INT AUTO_INCREMENT,
name VARCHAR(100),
PRIMARY KEY (member_id)
);
CREATE TABLE committees (
committee_id INT AUTO_INCREMENT,
name VARCHAR(100),
PRIMARY KEY (committee_id)
);
Thứ hai, chèn một số hàng vào bảng membersvà committees:
INSERT INTO members(name)
VALUES('John'),('Jane'),('Mary'),('David'),('Amelia');
INSERT INTO committees(name)
VALUES('John'),('Mary'),('Amelia'),('Joe');
Thứ ba, truy vấn dữ liệu từ các bảng membersvà committees:
SELECT * FROM members;
Kết quả:
SELECT * FROM committees;
Kết quả:
Một số thành viên là thành viên ủy ban, và một số thì không. Mặt khác, một số thành viên ủy ban có mặt trong bảng members, một số thì không.
Mệnh đề INNER JOIN của MySQL
Sau đây là cú pháp cơ bản của mệnh đề inner join nối hai bảng table_1 và table_2:
SELECT column_list FROM table_1 INNER JOIN table_2 ON join_condition;
Mệnh đề inner join nối hai bảng dựa trên một điều kiện được gọi là vị từ nối.
Mệnh đề inner join so sánh từng hàng từ bảng đầu tiên với mọi hàng từ bảng thứ hai.
Nếu các giá trị từ cả hai hàng thỏa mãn điều kiện nối, mệnh đề inner join sẽ tạo một hàng mới có cột chứa tất cả các cột của hai hàng từ cả hai bảng và đưa hàng mới này vào tập kết quả. Nói cách khác, mệnh đề inner join chỉ bao gồm các hàng phù hợp từ cả hai bảng.
Nếu điều kiện nối sử dụng toán tử đẳng thức ( =) và tên cột trong cả hai bảng được dùng để so sánh, thì bạn có thể sử dụng mệnh USING để thay thế:
SELECT column_list FROM table_1 INNER JOIN table_2 USING (column_name);
Câu lệnh sau đây sử dụng mệnh đề inner join để tìm các thành viên cũng là thành viên của ủy ban:
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
INNER JOIN committees c ON c.name = m.name;
Kết quả:
Trong ví dụ này, mệnh đề inner join sử dụng các giá trị trong các cột name trong cả hai bảng members và committees.
Biểu đồ Venn sau đây minh minh họa cho inner join:
=> Theo hình trên thì phần được lấy ra sẽ là phần màu xanh nghĩa là kết quả khi dùng mệnh đề inner join sẽ là những bản ghi có sự giống nhau giữa hai bảng.
MySQL LEFT JOIN clause
Tương tự như phép nối bên trong, left join cũng yêu cầu một vị từ nối. Khi join hai bảng bằng left join thì bạn cần biết các khái niện bảng bên trái và bảng bên phải.
Left Join chọn dữ liệu bắt đầu từ bảng bên trái. Đối với mỗi hàng trong bảng bên trái, phép nối bên trái so sánh với mọi hàng trong bảng bên phải.
Nếu các giá trị trong hai hàng thỏa mãn điều kiện nối, mệnh đề left join sẽ tạo một hàng mới có các cột chứa tất cả các cột của các hàng trong cả hai bảng và đưa hàng này vào tập kết quả.
Nếu các giá trị trong hai hàng không khớp nhau, mệnh đề left join vẫn tạo một hàng mới có các cột chứa các cột của hàng trong bảng bên trái và chứa các cột NULL của hàng trong bảng bên phải.
Nói cách khác, left join chọn tất cả dữ liệu từ bảng bên trái cho dù có các hàng phù hợp tồn tại trong bảng bên phải hay không.
Trong trường hợp không tìm thấy hàng phù hợp từ bảng bên phải, phép nối bên trái sử dụng NULL cho các cột của hàng từ bảng bên phải trong tập hợp kết quả.
Đây là cú pháp cơ bản của mệnh đề nối trái nối hai bảng:
SELECT column_list FROM table_1 LEFT JOIN table_2 ON join_condition;
Left Join cũng hỗ trợ mệnh USING nếu cột được sử dụng để khớp trong cả hai bảng giống nhau:
SELECT column_list FROM table_1 LEFT JOIN table_2 USING (column_name);
Ví dụ sau sử dụng mệnh đề Left Join để nối bảng members với committees:
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
LEFT JOIN committees c USING(name);
Kết quả:
Biểu đồ Venn sau đây minh họa left join:
Câu lệnh này sử dụng mệnh đề left join với cú pháp USING:
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
LEFT JOIN committees c USING(name);
Để tìm các thành viên không phải là thành viên ủy ban, bạn thêm một mệnh đề WHERE và toán tử IS NULL như sau:
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
LEFT JOIN committees c USING(name)
WHERE c.committee_id IS NULL;
Kết quả:
Nói chung, mẫu truy vấn này có thể tìm các hàng trong bảng bên trái không có hàng tương ứng trong bảng bên phải.
Sơ đồ Venn này minh họa cách sử dụng left join để chọn các hàng chỉ tồn tại trong bảng bên trái:
MySQL RIGHT JOIN clause
Mệnh đề right join tương tự như mệnh đề left join ngoại trừ việc xử lý các bảng bên trái và bên phải bị đảo ngược. Right join bắt đầu chọn dữ liệu từ bảng bên phải thay vì bảng bên trái.
Mệnh đề right join chọn tất cả các hàng từ bảng bên phải và khớp với các hàng trong bảng bên trái. Nếu một hàng từ bảng bên phải không có hàng phù hợp từ bảng bên trái, thì cột của bảng bên trái sẽ có NULL trong tập kết quả cuối cùng.
Đây là cú pháp của phép nối đúng:
SELECT column_list FROM table_1 RIGHT JOIN table_2 ON join_condition;
Tương tự như mệnh đề left join thì mệnh đề right join cũng hỗ trợ cú pháp USING:
SELECT column_list FROM table_1 RIGHT JOIN table_2 USING (column_name);
Để tìm các hàng trong bảng bên phải không có hàng tương ứng trong bảng bên trái, bạn cũng sử dụng một mệnh đề WHERE với toán tử IS NULL:
SELECT column_list FROM table_1 RIGHT JOIN table_2 USING (column_name) WHERE column_table_1 IS NULL;
Câu lệnh này sử dụng left join để nối các bảng members và committees:
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
RIGHT JOIN committees c on c.name = m.name;
Kết quả:
Biểu đồ Venn này minh họa phép nối đúng:
Câu lệnh sau sử dụng mệnh đề left join với cú pháp USING:
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
RIGHT JOIN committees c USING(name);
Để tìm các thành viên ủy ban không có trong bảng members, bạn sử dụng truy vấn sau:
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
RIGHT JOIN committees c USING(name)
WHERE m.member_id IS NULL;
Kết quả:
Sơ đồ Venn này minh họa cách sử dụng left join để chọn dữ liệu chỉ tồn tại trong bảng bên phải:
MySQL CROSS JOIN clause
Không giống như inner join, left join, và right join, mệnh đề cross join không có điều kiện nối.
Phép nối chéo tạo ra tích Descartes của các hàng từ các bảng đã nối. Phép nối chéo kết hợp từng hàng từ bảng đầu tiên với mọi hàng từ bảng bên phải để tạo tập hợp kết quả.
Giả sử bảng đầu tiên có n hàng và bảng thứ hai có m hàng. Phép nối chéo nối các bảng sẽ trả về nxm các hàng.
Sau đây là cú pháp của mệnh đề cross join:
SELECT select_list FROM table_1 CROSS JOIN table_2;
Ví dụ này sử dụng mệnh đề cross join để nối bảng members với committees:
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
CROSS JOIN committees c;
Kết quả:
Trong hướng dẫn này, bạn đã học các câu lệnh nối khác nhau của MySQL, bao gồm nối chéo, nối bên trong, nối trái và nối phải, để truy vấn dữ liệu từ hai bảng.