Truy vấn con ① — Scalar, IN và tổng hợp trong WHERE

Dữ liệu dùng trong bài viết này — bảng employee

Truy vấn con là một câu lệnh SELECT được viết bên trong một truy vấn khác.

Bạn có thể tính trước "lương trung bình của tất cả nhân viên" rồi dùng kết quả đó để thu hẹp về "những nhân viên có lương cao hơn mức trung bình" — một truy vấn theo từng bước được viết trong một câu lệnh duy nhất.

Trong bài viết này chúng ta sẽ đi qua ba dạng dùng trong mệnh đề WHERE — truy vấn con scalar, truy vấn con IN, và tổng hợp bên trong WHERE (truy vấn con tương quan) — lần lượt từng cái một.

Trước khi vào các bài tập, hãy xem định nghĩa cột và một mẫu dữ liệu trong bảng employee.

① Chạy PRAGMA table_info(employee); để kiểm tra tên cột, kiểu, và khóa chính.

② Chạy SELECT * FROM employee LIMIT 5; để xem trước 5 hàng đầu tiên. Lưu ý rằng một số nhân viên có NULL ở cột dept_id — chúng ta sẽ xử lý điều đó trong một bài tập sau.

Truy vấn con scalar — dùng kết quả 1 hàng 1 cột như một giá trị

Truy vấn con scalar (một truy vấn con mà kết quả đúng một hàng và một cột) trả về một giá trị đơn, nên bạn có thể viết nó trực tiếp ở vế phải của một toán tử so sánh như = hoặc >.

(SELECT AVG(salary) FROM employee) trả về một giá trị — "lương trung bình của tất cả nhân viên" — nên WHERE salary > (SELECT AVG(salary) FROM employee) thu hẹp về "những nhân viên có lương cao hơn mức trung bình."

Điểm mấu chốt là bao nó trong dấu ngoặc và đảm bảo nó luôn vừa trong một hàng và một cột (trả về nhiều hàng là một lỗi).

Các hàm tổng hợp như AVG / MAX / MIN / COUNT trả về một giá trị đơn, nên chúng thường được dùng trong truy vấn con scalar.

-- Truy vấn con scalar: lấy những nhân viên có lương cao nhất
SELECT name, salary
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee);

Hãy hình dung yêu cầu "liệt kê những nhân viên có lương cao hơn lương trung bình của tất cả nhân viên." (Chạy đúng thì phần giải thích sẽ hiện ra.)

① Lấy 2 cột name và salary từ bảng employee.

② Trong điều kiện WHERE, thu hẹp về các hàng có salary lớn hơn lương trung bình của tất cả nhân viên. Tính lương trung bình bằng một truy vấn con scalar.

③ Sắp xếp theo salary giảm dần.

Truy vấn con IN — lọc theo các giá trị nằm trong bảng khác

Khi một truy vấn con trả về nhiều hàng trong một cột, hãy dùng IN thay vì =.

Viết WHERE column IN (SELECT another_column FROM another_table ...) chỉ giữ lại những hàng có giá trị nằm trong tập mà truy vấn con trả về.

Truy vấn con ở vế phải của IN chỉ SELECT một cột duy nhất (trả về nhiều cột là một lỗi).

Để phủ định nó bạn dùng NOT IN, nhưng có một cạm bẫy: nếu NULL bị trộn vào phía truy vấn con, kết quả thay đổi (chúng ta đề cập điều này ở cuối bài viết).

-- Truy vấn con IN: nhân viên thuộc phòng ban đặt tại Tokyo
SELECT name, dept_id
FROM employee
WHERE dept_id IN (
  SELECT dept_id FROM department WHERE location = 'Tokyo'
);

Hãy hình dung yêu cầu "liệt kê những nhân viên xuất hiện ít nhất một lần trong các bản ghi doanh số (nhân viên thực sự đã ghi nhận doanh số)."

① Lấy 2 cột emp_id và name từ bảng employee.

② Xây dựng tập các ID nhân viên xuất hiện trong bảng sales bằng một truy vấn con, và dùng IN để thu hẹp về các hàng có emp_id nằm trong tập đó.

③ Sắp xếp theo emp_id tăng dần.

Tổng hợp bên trong WHERE — so với trung bình phòng ban bằng truy vấn con tương quan

Trước khi đến truy vấn con tương quan, hãy làm rõ cấu trúc hai tầng của một truy vấn con.

Có một SELECT chính lấy ra danh sách nhân viên, và bên trong nó, trong dấu ngoặc, bạn viết một SELECT khác (truy vấn con).

Các truy vấn con cho đến giờ là truy vấn con không tương quan — được tính chỉ một lần ở đầu, độc lập với SELECT chính (ví dụ, lương trung bình toàn công ty là cùng một giá trị đơn dù bạn nhìn vào nhân viên nào).

Ngược lại, một truy vấn con dùng một cột của nhân viên mà SELECT chính đang xử lý được gọi là truy vấn con tương quan (một truy vấn con được tính lại cho từng nhân viên).

Ví dụ, để liệt kê "người có lương cao nhất trong phòng ban (nhân viên có lương bằng mức tối đa của chính phòng ban họ)," lương cao nhất khác nhau theo từng phòng ban, nên với mỗi nhân viên bạn cần lương cao nhất của chỉ riêng phòng ban đó.

Trong ví dụ dưới đây, nhân viên ở truy vấn chính được đặt bí danh emp và phía truy vấn con là dept_member, và WHERE dept_member.dept_id = emp.dept_id tương quan chúng.

Khi nhân viên thay đổi, dept_id được truyền vào cũng thay đổi, và kết quả được tính lại.

-- Truy vấn con tương quan: nhân viên có lương bằng mức tối đa của chính phòng ban họ (người có lương cao nhất trong phòng ban)
SELECT emp.name, emp.dept_id, emp.salary
FROM employee emp
WHERE emp.salary = (
    SELECT MAX(dept_member.salary)
    FROM employee dept_member
    WHERE dept_member.dept_id = emp.dept_id
  );

Hãy hình dung yêu cầu "trong từng phòng ban, liệt kê những nhân viên có lương cao hơn lương trung bình của phòng ban đó."

① Đặt bí danh cho bảng employee là emp và lấy 3 cột name, dept_id, và salary.

② Giới hạn về các hàng có dept_id không phải NULL (nhân viên không thuộc phòng ban nào nằm ngoài phạm vi).

③ Thu hẹp về các hàng có salary lớn hơn lương trung bình của cùng phòng ban với nhân viên đó. Tính trung bình phòng ban bằng một truy vấn con đặt bí danh dept_member, tương quan với dept_member.dept_id = emp.dept_id.

④ Sắp xếp theo dept_id tăng dần, và trong cùng một phòng ban thì theo salary giảm dần.

Cạm bẫy NOT IN và NULL

Phủ định của IN là NOT IN, nhưng nếu NULL nằm trong truy vấn con ở vế phải của NOT IN, kết quả thay đổi mạnh.

Đó là vì khi nó trở thành NOT IN (..., NULL), SQL kiểm tra "giá trị không bằng bất kỳ cái nào trong số chúng" theo dạng value <> ... AND value <> NULL, nhưng value <> NULL không cho ra true cũng không cho ra false (nó là NULL), nên toàn bộ AND không bao giờ thành true và không hàng nào sống sót.

Hãy hình dung yêu cầu "liệt kê tên những nhân viên không phải quản lý (sếp) của ai." Cột manager_id trong employee chứa NULL cho những nhân viên không có sếp. (Chạy đúng thì phần giải thích sẽ hiện ra.)

① Chạy NOT IN trong trình soạn thảo nguyên trạng và xác nhận kết quả trả về 0 hàng.

② Loại trừ các hàng có manager_id là NULL ở phía truy vấn con, và sửa nó để những nhân viên không phải quản lý của ai trả về đúng.

③ Sắp xếp theo emp_id tăng dần.