Join trong SQL Server
Trong hướng dẫn này, bạn sẽ tìm hiểu về các loại join khác nhau trong SQL Server cho phép bạn kết hợp dữ liệu từ hai bảng.
Trong cơ sở dữ liệu quan hệ, dữ liệu được phân phối trong nhiều bảng logic. Để có được một bộ dữ liệu có ý nghĩa hoàn chỉnh, bạn cần truy vấn dữ liệu từ các bảng này bằng cách sử dụng các phép nối (join).
SQL Server hỗ trợ nhiều loại liên kết bao gồm INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN và CROSS JOIN. Mỗi loại join xác định cách SQL Server sử dụng dữ liệu từ một bảng để chọn các bản ghi trong bảng khác.
Chúng tôi sẽ tạo các bảng dữ liệu mẫu để minh họa các loại join trong SQL Server.
Tạo bảng dữ liệu mẫu
Đầu tiên, tạo một lược đồ mới có tên hr:
CREATE SCHEMA hr;
GOThứ hai, tạo hai bảng mới có tên là candidates và employees trong lược đồ hr:
CREATE TABLE hr.candidates(
id INT PRIMARY KEY IDENTITY,
fullname VARCHAR(100) NOT NULL
);
CREATE TABLE hr.employees(
id INT PRIMARY KEY IDENTITY,
fullname VARCHAR(100) NOT NULL
);Thứ ba, INSERT một số bản ghi vào bảng candidates và employees:
INSERT INTO
hr.candidates(fullname)
VALUES
('John Doe'),
('Lily Bush'),
('Peter Drucker'),
('Jane Doe');
INSERT INTO
hr.employees(fullname)
VALUES
('John Doe'),
('Jane Doe'),
('Michael Scott'),
('Jack Sparrow');Chúng ta hãy gọi bảng candidates là bảng bên trái và bảng employees là bảng bên phải.
INNER JOIN trong SQL Server
INNER JOIN tạo ra một tập dữ liệu bao gồm các bản ghi từ bảng bên trái có các bản ghi khớp từ bảng bên phải.
Ví dụ sau sử dụng mệnh đề INNER JOIN để lấy các bản ghi từ bảng candidates có các bản ghi tương ứng có cùng giá trị trong cột fullname của bảng employees:
SELECT
c.id candidate_id,
c.fullname candidate_name,
e.id employee_id,
e.fullname employee_name
FROM
hr.candidates c
INNER JOIN hr.employees e
ON e.fullname = c.fullname;Đây là kết quả:
Biểu đồ Venn sau đây minh họa kết quả của INNER JOIN hai tập kết quả:
Bạn có thể xem chi tiết về mệnh đề INNER JOIN trong SQL Server ở bài viết sau:
Comdy
LEFT JOIN trong SQL Server
LEFT JOIN truy vấn dữ liệu bắt đầu từ bảng bên trái và các bản ghi khớp trong bảng bên phải.
LEFT JOIN trả về tất cả các bản ghi từ bảng bên trái và các bản ghi khớp từ bảng bên phải.
Nếu một bản ghi trong bảng bên trái không có một bản ghi phù hợp trong bảng bên phải, các cột của bảng bên phải sẽ có giá trị NULL.
LEFT JOIN còn được gọi là LEFT OUTER JOIN. Từ khóa OUTER là tùy chọn.
Câu lệnh sau đây nối bảng candidates với bảng employees bằng cách sử dụng mệnh đề LEFT JOIN:
SELECT
c.id candidate_id,
c.fullname candidate_name,
e.id employee_id,
e.fullname employee_name
FROM
hr.candidates c
LEFT JOIN hr.employees e
ON e.fullname = c.fullname;Đây là kết quả:
Biểu đồ Venn sau đây minh họa kết quả của LEFT JOIN hai tập kết quả:
Để có được các bản ghi chỉ tồn tại trong bảng bên trái nhưng không có trong bảng bên phải, bạn thêm một mệnh đề WHERE vào truy vấn trên như sau:
SELECT
c.id candidate_id,
c.fullname candidate_name,
e.id employee_id,
e.fullname employee_name
FROM
hr.candidates c
LEFT JOIN hr.employees e
ON e.fullname = c.fullname
WHERE
e.id IS NULL;Đây là kết quả:
Và sơ đồ Venn sau đây minh họa kết quả của LEFT JOIN truy vấn các bản ghi chỉ tồn tại trong bảng bên trái:
Bạn có thể xem chi tiết về mệnh đề LEFT JOIN trong SQL Server ở bài viết sau:
Comdy
RIGHT JOIN trong SQL Server
RIGHT JOIN hoặc RIGHT OUTER JOIN truy vấn dữ liệu bắt đầu từ bảng bên phải. Nó là một phiên bản đảo ngược của LEFT JOIN.
RIGHT JOIN trả về một tập kết quả chứa tất cả các bản ghi từ bảng bên phải và các bản ghi khớp trong bảng bên trái.
Nếu một bản ghi trong bảng bên phải không có bản ghi phù hợp trong bảng bên trái, tất cả các cột trong bảng bên trái sẽ chứa NULL.
Ví dụ sau sử dụng RIGHT JOIN để truy vấn các bản ghi từ bảng candidates và employees:
SELECT
c.id candidate_id,
c.fullname candidate_name,
e.id employee_id,
e.fullname employee_name
FROM
hr.candidates c
RIGHT JOIN hr.employees e
ON e.fullname = c.fullname;Đây là kết quả:
Lưu ý: tất cả các bản ghi từ bảng bên phải (
employees) được bao gồm trong tập kết quả.
Và biểu đồ Venn minh họa RIGHT JOIN của hai tập kết quả:
Tương tự, bạn có thể nhận được các bản ghi chỉ tồn tại trong bảng bên phải bằng cách thêm một mệnh đề WHERE vào truy vấn trên như sau:
SELECT
c.id candidate_id,
c.fullname candidate_name,
e.id employee_id,
e.fullname employee_name
FROM
hr.candidates c
RIGHT JOIN hr.employees e
ON e.fullname = c.fullname
WHERE
c.id IS NULL;Đây là kết quả:
Và biểu đồ Venn minh họa kết quả của RIGHT JOIN truy vấn các bản ghi chỉ tồn tại trong bảng bên phải:
Bạn có thể xem chi tiết về mệnh đề RIGHT JOIN trong SQL Server ở bài viết sau:
Comdy
FULL OUTER JOIN trong SQL Server
FULL OUTER JOIN hoặc FULL JOIN trả về một tập kết quả có chứa tất cả các bản ghi từ cả hai bảng bên trái và bên phải, với các bản ghi khớp từ cả hai bên nếu có. Trong trường hợp không có kết quả khớp, bên bị thiếu sẽ có các giá trị NULL.
Ví dụ sau đây cho thấy cách thực hiện FULL OUTER JOIN giữa bảng candidates và employees:
SELECT
c.id candidate_id,
c.fullname candidate_name,
e.id employee_id,
e.fullname employee_name
FROM
hr.candidates c
FULL JOIN hr.employees e
ON e.fullname = c.fullname;Đây là kết quả:
Biểu đồ Venn minh họa cho FULL OUTER JOIN của hai tập kết quả:
Để truy xuất các bản ghi tồn tại bảng bên trái hoặc bên phải, bạn loại trừ các bản ghi chung cho cả hai bảng bằng cách thêm một mệnh đề WHERE như trong truy vấn sau:
SELECT
c.id candidate_id,
c.fullname candidate_name,
e.id employee_id,
e.fullname employee_name
FROM
hr.candidates c
FULL JOIN hr.employees e
ON e.fullname = c.fullname
WHERE
c.id IS NULL OR
e.id IS NULL;Đây là kết quả:
Và sơ đồ Venn minh họa hoạt động trên:
Bạn có thể xem chi tiết về mệnh đề FULL OUTER JOIN trong SQL Server ở bài viết sau:
Comdy
CROSS JOIN trong SQL Server
Trong phần này, bạn sẽ tìm hiểu cách sử dụng mệnh đề CROSS JOIN trong SQL Server để truy vấn dữ liệu từ hai hoặc nhiều bảng không liên quan.
Phần sau đây minh họa cú pháp của CROSS JOIN trong SQL Server để truy vấn dữ liệu từ hai bảng:
SELECT
select_list
FROM
T1
CROSS JOIN T2;Mệnh đề CROSS JOIN sẽ nối mỗi bản ghi từ bảng đầu tiên (T1) với mỗi bản ghi từ bảng thứ hai (T2). Nói cách khác CROSS JOIN trả về tích Đề Các các bản ghi từ cả hai bảng.
Không giống như mệnh đề INNER JOIN, LEFT JOIN, RIGHT JOIN hay FULL OUTER JOIN. CROSS JOIN không thiết lập mối quan hệ giữa các bảng được join.
Giả sử bảng T1 chứa ba bản ghi 1, 2 và 3 và bảng T2 chứa ba bản ghi A, B và C.
Mệnh đề CROSS JOIN lấy một bản ghi từ bảng đầu tiên (T1) và sau đó tạo một bản ghi mới cho mỗi bản ghi trong bảng thứ hai (T2). Sau đó, nó làm tương tự cho bản ghi tiếp theo trong bảng đầu tiên (T1), v.v.
Trong hình minh họa này, CROSS JOIN tạo ra tổng cộng chín bản ghi. Nói chung, nếu bảng đầu tiên có n bản ghi và bảng thứ hai có m bản ghi, CROSS JOIN sẽ tạo ra n * m bản ghi.
Câu lệnh sau đây trả về một tập kết quả là sự kết hợp của tất cả các sản phẩm và cửa hàng. Tập kết quả có thể được sử dụng cho thủ tục kiểm kê trong thời gian đóng cửa cuối tháng và cuối năm:
SELECT
product_id,
product_name,
store_id,
0 AS quantity
FROM
production.products
CROSS JOIN sales.stores
ORDER BY
product_name,
store_id;Đây là kết quả:
Bạn có thể xem chi tiết về mệnh đề CROSS JOIN trong SQL Server ở bài viết sau:
Comdy
Self Join trong SQL Server
Trong phần này, bạn sẽ tìm hiểu cách sử dụng self join (tự join) trong SQL Server để join một bảng với chính nó.
Self Join cho phép bạn join một bảng vào chính nó. Nó rất hữu ích để truy vấn dữ liệu phân cấp hoặc so sánh các bản ghi trong cùng một bảng.
Self Join sử dụng mệnh đề INNER JOIN hoặc LEFT JOIN. Vì truy vấn sử dụng tham chiếu đến cùng một bảng, nên bí danh bảng được sử dụng để gán các tên khác nhau cho cùng một bảng trong truy vấn.
Lưu ý: việc tham chiếu đến cùng một bảng nhiều lần trong một truy vấn mà không sử dụng bí danh bảng sẽ dẫn đến lỗi.
Sau đây minh họa cú pháp join bảng T với chính nó:
SELECT
select_list
FROM
T t1
[INNER | LEFT] JOIN T t2 ON
join_predicate; Truy vấn tham chiếu bảng T hai lần. Các bí danh bảng t1 và t2 được sử dụng để gán các tên bảng T khác nhau trong truy vấn.
Hãy xem bảng staffs sau từ cơ sở dữ liệu mẫu:
Bảng staffs lưu trữ các thông tin nhân viên như id, tên, họ và email. Nó cũng có một cột có tên là manager_id để chỉ định người quản lý trực tiếp. Ví dụ, Mireya báo cáo lên Fabiola vì giá trị trong manager_id của Mireya là Fabiola.
Fabiola không có người quản lý nên cột id người quản lý có NULL.
Để biết ai báo cáo cho ai, bạn sử dụng self join được trình bày trong truy vấn sau:
SELECT
e.first_name + ' ' + e.last_name employee,
m.first_name + ' ' + m.last_name manager
FROM
sales.staffs e
INNER JOIN sales.staffs m ON m.staff_id = e.manager_id
ORDER BY
manager;Đây là kết quả:
Trong ví dụ này, chúng tôi đã tham chiếu bảng staffs hai lần: bí danh e cho nhân viên và bí danh m cho người quản lý. Biểu thức join khớp mối quan hệ nhân viên với người quản lý bằng cách sử dụng các giá trị trong cột e.manager_id và cột m.staff_id.
Cột nhân viên không có Fabiola Jackson vì hiệu ứng của mệnh đề INNER JOIN. Nếu bạn thay thế mệnh đề INNER JOIN bằng mệnh đề LEFT JOIN như trong truy vấn sau, bạn sẽ nhận được tập kết quả bao gồm Fabiola Jackson trong cột nhân viên:
SELECT
e.first_name + ' ' + e.last_name employee,
m.first_name + ' ' + m.last_name manager
FROM
sales.staffs e
LEFT JOIN sales.staffs m ON m.staff_id = e.manager_id
ORDER BY
manager;Đây là kết quả:
Bạn có thể xem chi tiết về self join trong SQL Server ở bài viết sau:
Comdy
Trong hướng dẫn này, bạn đã học được các loại join khác nhau trong SQL Server để kết hợp dữ liệu từ hai bảng.