Truy vấn nhiều bảng với JOIN trong SQL

• Nối nhiều bảng
• Các kiểu Join
• inner join trong SQL
• inner join ba bảng SQL
• left join trong SQL
• right join trong SQL
• full outer join trong SQL

Truy vấn nhiều bảng, khớp nối các bảng với where

Một trong nhưng lợi ích chính của SQL là khả năng kết hợp dữ liệu từ hai hay nhiều bảng lại với nhau. Việc kết hợp các bảng lại như vậy gọi là JOIN, SQL sẽ tạo ra một bảng tạm thời chứa dữ liệu kết quả từ JOIN.

Trước khi tìm hiểu cú pháp mệnh đề join, trong một số trường hợp có thể lấy dữ liệu kết hợp của các bảng chỉ bằng cách đơn giản là dùng mệnh đề where, hãy xét ví dụ sau: Có CSDL trong đó bảng Donhang và bảng Khachhang (thông tin cấu trúc CSDL ví dụ này xem tại SQL Online)

Khớp nối hai bảng

Giờ chúng ta cần duyệt qua các dòng của cả hai bảng, với mỗi đơn hàng trong bảng Donhang lấy ra ID của đơn hàng, ngày đặt hàng. Mỗi đơn hàng đều có KhachhangID, căn cứ vào đó lấy ra tên khách hàng đặt đơn hàng trong bảng Khachhang

Như vậy dữ liệu lấy ra từ hai bảng, hai bảng này sẽ khớp nối dữ liệu với nhau, trong câu truy vấn trước tên ở mệnh đề from chỉ ra tên hai bảng cần kết nối (cách nhau bởi dấu phảy), và ở mệnh đề where là điều kiện khớp nối.

Ví dụ:

SELECT Donhang.DonhangID, Khachhang.Hoten, Donhang.Ngaydathang
FROM Donhang, Khachhang
WHERE Donhang.KhachhangID=Khachhang.KhachhangID

Điều kiện khớp nối dữ liệu hai bảng: Ở mệnh đề where ở câu truy vấn trên thì đoạn mã Donhang.KhachhangID=Khachhang.KhachhangID dùng để khớp nối bảng.

Mọi cặp bản ghi (dòng dữ liệu): bản ghi ở bảng Donhang, bản ghi ở Khachhang có cùng giá trị KhachhangID sẽ kết hợp với nhau để tạo ra dòng dữ liệu tạm thời để select chọn:

Sự kết hợp giữa hai bảng

Kết quả trên bạn thấy tên khách hàng (Hoten) lấy ở bảng Khachhang, với mỗi khách hàng lấy được thì các ngày đặt hàng và ID của đơn hàng (Ngaydathang,DonhangID) của khách hàng đó được lấy ở bảng Donhang

Ở đây có một file CSDL mẫu dạng SQLite, bạn có thể tải về nghiên cứu, thực hành: CSDL SQLite mẫu. Hoặc sử dụng trực tiếp công cụ Online: Chạy SQL

Đặt lại tên bảng khi truy vấn với AS

Từ khóa as bạn đã từng dùng để đặt tên cột tùy biến, với cũng có thể dùng as để đặt tên tùy biến (tên tắt dùng trong câu truy vấn), mục đích để câu truy vấn phức tạp, dài trở lên ngắn gọn, dễ đọc hơn.

Ví dụ trên viết có sử dụng tên tắt cho bảng

SELECT o.DonhangID, c.Hoten, o.Ngaydathang
FROM Donhang As o, Khachhang AS c
WHERE o.KhachhangID=c.KhachhangID

Bảng Donhang tương ứng với tên tắt o, bảng Khachhang thành c.

Các kiểu JOIN

Cách khớp nối bảng ở trên (sử dụng điều kiện khớp nối ở mệnh đề where) sử dụng với khớp nối thông thường, thực tế SQL sử dụng mệnh đề join với nhiều cách kết nối bảng khách nhau tùy mục đích lấy dữ liệu. Trong cú pháp có từ khóa join và hai bảng cần kết nối, một bảng bên trái (bảng 1) một bảng bên phải (bảng 2) từ khóa JOIN (Chi tiết cú pháp phía dưới) Các kiểu JOIN trong SQL gồm có:

• inner join
  Trả về các dòng hình thành bởi cặp bản ghi của hai bảng, với điều kiện cặp bản ghi thứ nhất (thuộc bảng 1, bảng bên trái) có 1 trường dữ liệu nào đó để so sánh bằng với giá trị của một trường dữ liệu chỉ ra trong bản ghi (record) thứ 2 (bảng 2, bảng bên phải). (nhớ lại phép giao hai tập hợp)
• left join
  Mọi bản ghi bảng bên trái được trả về, bản ghi nào phù hợp với bản ghi bên phải thì nó được bổ sung thêm dữ liệu từ bản ghi bảng bên phải (nếu không có thì nhận NULL)
• right join
  Mọi bản ghi bảng bên phải được trả về, sau bổ sung dữ liệu phù hợp từ bảng bên trái.
• outer join
  (full join) mọi bản ghi ở bảng trái và bảng phải kết hợp lại

Hình ảnh trực quan cho các trường hợp JOIN trong SQL

Quy tắc chung viết join ... on : bảng trái là bảng nằm bên trái (phía trước) từ khóa join, bảng phái là bảng sau (bên phải) từ khóa join, trong mệnh đề join thì điều kiện kết hợp vết sau từ khóa on

Sử dụng INNER JOIN trong SQL

Cú pháp cơ bản như sau:

SELECT ...
FROM bảng_trái INNER JOIN bảng_phải
ON điều_kiện_khớp_nối
[mệnh đề where, order ... nếu có]

Xem lại hình ảnh về Inner Join, nó giống với phép toán giao tập hợp. inner join chọn các bản ghi mà phải phù hợp ở cả hai bảng

Ví dụ INNER JOIN hai bảng

SELECT Donhang.DonhangID, Khachhang.Hoten, Donhang.Ngaydathang
FROM Donhang INNER JOIN Khachhang
ON Donhang.KhachhangID = Khachhang.KhachhangID

Trong đó bảng Donhang là bảng trái vì bên trái từ khóa INNER JOIN, Khachhang là bảng phải. Biểu thức sau từ khóa ON cụ thể ở ví dụ này là Donhang.KhachhangID = Khachhang.KhachhangID là biểu thức khớp nối.
Từ đây SQL lấy ghi trong Donhang, sau đó kiểm tra trong Khachhang xem có bản ghi nào thỏa mãn mệnh đề ON. Nếu thỏa mãn, hai bản ghi từ hai bảng sẽ kết hợp lại và trả về (dùng select đế lấy).

Kết quả chạy thử:

Ví dụ INNER JOIN ba bảng trong SQL

Bạn có thể viết truy vấn phức tạp, sử dụng INNER JOIN với ba bảng khác nhau - kết hợp dữ liệu lại. Giả sử có ba bảng B1,B2,B3 thì bạn có thể viết theo hướng B1 và B2 inner join với nhau (sẽ tạo ra dữ liệu tam), dữ liệu tạm này như là một bảng tiếp tục inner join với B3. Bằng cách như vậy ba bảng sẽ kết hợp lại với nhau.

SELECT Donhang.DonhangID, Khachhang.HoTen, Shippers.Hoten AS TenShipper
FROM (Donhang INNER JOIN Khachhang ON Donhang.KhachhangID = Khachhang.KhachhangID)
INNER JOIN Shippers ON Donhang.ShipperID = Shippers.ShipperID

Kết quả

Nếu bạn dùng từ khóa AS có thể cú pháp sẽ ngắn gọi và dễ đọc, chính câu truy vấn trên bạn có thể viết như sau mà kết quả tương đương

SELECT t.DonhangID, t.HoTen, s.Hoten AS TenShipper
FROM (Donhang INNER JOIN Khachhang ON Donhang.KhachhangID = Khachhang.KhachhangID) as t
INNER JOIN Shippers as s ON t.ShipperID = S.ShipperID

Sử dụng Sử dụng LEFT JOIN trong SQL

LEFT JOIN về cú pháp tương tự INNER JOIN, tuy nhiên sự làm việc sẽ khác, cụ thể như sau. Mỗi dòng của bảng trái được lấy ra để kết hợp với bảng phải, nếu bảng phải có các dòng phù hợp thì các dòng này được kết hợp với dòng được lấy ra đó của bảng trái để tạo kết quả trả về. Nếu bảng phải không có dữ liệu nào phù hợp thì nó vẫn trả về chính dòng lấy ra của bảng trái (còn các trường dữ liệu tương ứng với bảng phải do không có sẽ nhận null).

Ví dụ:

SELECT Khachhang.Hoten, Donhang.DonhangID
FROM Khachhang LEFT JOIN Donhang ON Khachhang.KhachhangID = Donhang.KhachhangID
ORDER BY Khachhang.Hoten

Kết quả chạy thử:

Bảng bên trái là Khachhang, bên phải là Donhang. Quan sát kết quả ta thấy mọi HoTen ở bảng bên trái đều được lấy ra, sau đó DonhangID ở bảng bên phải phù hợp được dùng để kết hợp với HoTen nếu không có giá trị nào phù hợp thì nhận giá trị null (như Bùi Nam Khánh là tên trong bảng khách hàng, nhưng không có đơn hàng nào)

Từ kết cú pháp kết hợp bằng left join, nếu bạn muốn dữ liệu của bảng trái (bảng 1), mà không có dòng nào được kết hợp với bảng phải (bảng 2). Hãy nghĩ tới phép toán hiệu hai tập hợp.

Nếu vậy trong truy vấn left join ở trên hãy cho thêm mệnh đề Where và điều kiện dữ dữ liệu bảng phải tương đương NULL (IS NULL)

SELECT Khachhang.Hoten, Donhang.DonhangID
FROM Khachhang LEFT JOIN Donhang ON Khachhang.KhachhangID = Donhang.KhachhangID
WHERE Donhang.KhachhangID IS NULL
ORDER BY Khachhang.Hoten

Truy vấn trên cơ bản trả về dữ liệu là danh sách Khachhang không có đơn hàng nào.

Sử dụng Sử dụng RIGHT JOIN trong SQL

Trường hợp này hoạt động giống với LEFT JOIN theo chiều ngược lại (SQLite không hỗ trợ RIGHT bạn có thể đảo thứ tự bảng để sử dụng LEFT).

Ví dụ:

SELECT Donhang.OrderID, NhanVien.Ten, NhanVien.Ho
FROM Donhang RIGHT JOIN NhanVien ON Donhang.NhanvienID = NhanVien.NhanvienID
ORDER BY Donhang.DonhangID

Sử dụng Sử dụng FULL OUTER JOIN trong SQL

Xét tất cả các kết quả, với SQLite không hỗ trợ (có thể thay thế bằng LEFT JOIN kết hợp với UNION)

SELECT Khachhang.HoTen, Donhang.DonhangID
FROM Khachhang
FULL OUTER JOIN Donhang ON Khachhang.KhachhangID=Donhang.KhachhangID
ORDER BY Khachhang.HoTen

FULL OUTER JOIN tương đương với sự kết hợp UNION hai mệnh truy vấn LEFT JOIN và RIGHT JOIN