Self Join trong SQL Server

Trong hướng dẫ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ó.

Cú pháp Self Join trong SQL Server

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.

Ví dụ Self Join trong SQL Server

Hãy lấy một số ví dụ để tìm hiểu cách thức self join hoạt động trong SQL Server.

1) Sử dụng Self Join để truy vấn dữ liệu phân cấp

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

Sử dụng Self Join để so sánh các bản ghi trong bảng

Xem bảng customers sau :

Câu lệnh sau đây sử dụng self join để tìm các khách hàng có địa chỉ trong cùng thành phố.

SELECT
    c1.city,
    c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2
FROM
    sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id > c2.customer_id
    AND c1.city = c2.city
ORDER BY
    city,
    customer_1,
    customer_2;

Đây là kết quả:

Điều kiện sau đây đảm bảo rằng câu lệnh không so sánh cùng một khách hàng:

c1.customer_id > c2.customer_id

Và điều kiện sau đây để khớp thành phố của hai khách hàng:

AND c1.city = c2.city

Lưu ý: nếu bạn thay đổi toán tử lớn hơn (>) bằng toán tử không bằng (<>), bạn sẽ nhận được nhiều bản ghi hơn:

SELECT
    c1.city,
    c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2
FROM
    sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id <> c2.customer_id
AND c1.city = c2.city
ORDER BY
    city,
    customer_1,
    customer_2;

Đây là kết quả:

Chúng ta hãy xem sự khác biệt giữa > và <> trong mệnh đề ON bằng cách giới hạn ở một thành phố để dễ so sánh hơn.

Truy vấn sau đây trả về các khách hàng có địa chỉ ở Albany:

SELECT 
   customer_id, first_name + ' ' + last_name c, 
   city
FROM 
   sales.customers
WHERE
   city = 'Albany'
ORDER BY 
   c;

Đây là kết quả:

Truy vấn này sử dụng toán tử > trong mệnh đề ON:

SELECT
    c1.city,
    c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2
FROM
    sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id > c2.customer_id
    AND c1.city = c2.city
WHERE c1.city = 'Albany'
ORDER BY
    c1.city,
    customer_1,
    customer_2;

Đây là kết quả:

Truy vấn này sử dụng toán tử <> trong mệnh đề ON:

SELECT
    c1.city,
    c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2
FROM
    sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id <> c2.customer_id
    AND c1.city = c2.city
WHERE c1.city = 'Albany'
ORDER BY
    c1.city,
    customer_1,
    customer_2;

Đây là kết quả:

Trong hướng dẫn này, bạn đã học cách sử dụng self join trong SQL Server để truy vấn dữ liệu phân cấp và so sánh các bản ghi trong cùng một bảng.