Join trong SQL Server

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 JOINCROSS 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;
GO

Thứ hai, tạo hai bảng mới có tên là candidatesemployees 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 candidatesemployees:

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ả:

INNER JOIN trong SQL Server

Biểu đồ Venn sau đây minh họa kết quả của INNER JOIN hai tập kết quả:

INNER JOIN trong SQL Server

Bạn có thể xem chi tiết về mệnh đề INNER JOIN trong SQL Server ở bài viết sau:

Inner Join trong SQL Server | Comdy
Tìm hiểu cách sử dụng mệnh đề INNER JOIN trong SQL Server để truy vấn dữ liệu từ nhiều bảng.

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ả:

LEFT JOIN trong SQL Server

Biểu đồ Venn sau đây minh họa kết quả của LEFT JOIN hai tập kết quả:

LEFT JOIN trong SQL Server

Để 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ả:

LEFT JOIN trong SQL Server

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:

LEFT JOIN trong SQL Server

Bạn có thể xem chi tiết về mệnh đề LEFT JOIN trong SQL Server ở bài viết sau:

LEFT JOIN trong SQL Server | Comdy
Cách sử dụng mệnh đề LEFT JOIN trong SQL Server để truy vấn dữ liệu từ nhiều bảng.

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 candidatesemployees:

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ả:

RIGHT JOIN trong SQL Server

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ả:

RIGHT JOIN trong SQL Server

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ả:

RIGHT JOIN trong SQL Server

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:

RIGHT JOIN trong SQL Server

Bạn có thể xem chi tiết về mệnh đề RIGHT JOIN trong SQL Server ở bài viết sau:

RIGHT JOIN trong SQL Server | Comdy
Cách sử dụng mệnh đề RIGHT JOIN trong SQL Server để truy vấn dữ liệu từ hai bảng.

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 candidatesemployees:

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ả:

FULL JOIN trong SQL Server

Biểu đồ Venn minh họa cho FULL OUTER JOIN của hai tập kết quả:

FULL JOIN trong SQL Server

Để 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ả:

FULL JOIN trong SQL Server

Và sơ đồ Venn minh họa hoạt động trên:

FULL JOIN trong SQL Server

Bạn có thể xem chi tiết về mệnh đề FULL OUTER JOIN trong SQL Server ở bài viết sau:

FULL OUTER JOIN trong SQL Server | Comdy
Cách sử dụng mệnh đề FULL OUTER JOIN trong SQL Server để truy vấn dữ liệu từ hai hoặc nhiều bảng.

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.

CROSS JOIN trong SQL Server

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ả:

Ví dụ về CROSS JOIN trong SQL Server

Bạn có thể xem chi tiết về mệnh đề CROSS JOIN trong SQL Server ở bài viết sau:

CROSS JOIN trong SQL Server | Comdy
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.

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 t1t2 đượ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:

Ví dụ Self Join trong SQL Server
Ví dụ Self Join trong SQL Server

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  MireyaFabiola.

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ả:

Ví dụ Self Join trong SQL Server

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ả:

Ví dụ Self Join trong SQL Server

Bạn có thể xem chi tiết về self join trong SQL Server ở bài viết sau:

Self Join trong SQL Server | Comdy
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ó.

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.

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *