Bài 4.4 Truy vấn con SQL (STRUCTURED QUERY LANGUAGE)

Vấn đề cần giải quyết

Cú pháp truy vấn con

• Truy vấn con thi hành môt lần trước truy vấn chính (truy vấn cha).
• Kết quả trả về từ truy vấn con được sử dụng trong câu truy vấn chính.

SELECT ds_cot
FROM bang
WHERE bieu_thuc toan_tu(SELECT bieu_thuc FROM bang)

Sử dụng truy vấn con

SELECT honv, tennv
FROM qlns.nhanvien
WHERE mucluong >(SELECT mucluong FROM qlns.nhanvien WHERE tennv = ‘Huyền')

Một số quy tắc khi sử dụng truy vấn con

• Câu truy vấn con phải nằm trong cặp ngoặc đơn.
• Đặt truy vấn con bên phải điều kiện so sánh.
• Mệnh đề ORDER BY trong truy vấn con là không cần thiết ngoại trừ khi có sử dụng mệnh đề TOP.
• Sử dụng các toán tử một dòng với các truy vấn con trả về một dòng và sử dụng các toán tử nhiều dòng với các truy vấn con trả về nhiều dòng.

Các dạng truy vấn con

• Truy vấn con một dòng
• Truy vấn con nhiều dòng

Truy vấn con một dòng

• Trả về duy nhất 1 dòng
• Sử dụng các toán tử so sánh một dòng
• = Bằng
• > Lớn hơn
• >= Lớn hơn hoặc bằng
• < Nhỏ hơn
• <= Nhỏ hơn học bằng
• <> Không bằng

Sử dụng hàm nhóm dữ liệu trong truy vấn con

Mệnh đề HAVING với truy vấn con

• Các truy vấn con được thi hành trước tiên.
• Kết quả được trả về cho mệnh đề HAVING trong truy vấn chính.

Câu lệnh sau sai ở chỗ nào?

Câu lệnh sau có trả về kết quả nào không?

Truy vấn con nhiều dòng

• Trả về nhiều dòng
• Sử dụng các toán tử so sánh nhiều dòng
• IN Bằng một trong các giá trị
• ANY Chỉ cần thỏa một trong các giá trị trả về bởi truy vấn con
• ALL Phải thỏa tất cả các giá trị trả về bởi truy vấn con

Sử dụng toán tử ANY

Sử dụng toán tử ALL

Cẩn thận với giá trị NULL trong truy vấn con

Tìm những nhân viên không quản lý bất cứ người nào

ELECT nv.tennv
FROM qlns.nhanvien nv
WHERE nv.manhanvien NOT IN (SELECT qly.manguoiquanly FROM lns.nhanvien qly)

Phần mở rộng của Truy vấn con

• Truy vấn con nhiều cột
• Truy vấn con trong mệnh đề FROM
• Sử dụng truy vấn con đơn trị (scalar subquery)
• Viết truy vấn con kết hợp (Correlated subquery)
• Sử dụng hàm EXISTS

Truy vấn con trả về nhiều cột

Liệt kê danh sách những nhân viên được quản lý bởi bởi cùng một người và làm trong cùng phòng với nhân viên có mã số là 143.

SELECT manhanvien, manguoiquanly, maphong
FROM qlns.nhanvien
WHERE (STR(manguoiquanly)+STR(maphong)) IN
     (SELECT STR(manguoiquanly)+STR(maphong)
      FROM qlns.nhanvien
      WHERE manhanvien = 143)
AND manhanvien NOT IN (143)

Sử dụng truy vấn con trong mệnh đề FROM

SELECT a.tennv, a.mucluong,a.maphong, b.tb_luong
FROM qlns.nhanvien a, 
		(SELECT maphong,AVG(mucluong) tb_luong FROM qlns.nhanvien GROUP BY maphong) b
WHERE a.maphong = b.maphong AND a.mucluong > b.tb_luong;

Sử dụng truy vấn con trong mệnh đề FROM

• Biểu thức truy vấn con đơn trị (A scalar subquery expression) là một truy vấn con trả về duy nhất một cột và một dòng.
• Truy vấn con đơn trị có thể sử dụng:
• Trong các điều kiện và biểu thức của CASE
• Trong tất cả các mệnh đề của SELECT ngoại trừ GROUP BY

Ví dụ Truy vấn con đơn trị

Truy vấn con kết hợp

Truy vấn con kết hợp (Correlated subqueries) được sử dụng cho việc xử lý từng dòng. Mỗi truy vấn con sẽ được thi hành một lần ứng với mỗi dòng của truy vấn cha

Sử dụng hàm EXISTS

• Hàm EXISTS kiểm tra tồn tại kết quả trả về từ câu truy vấn con.
• Tìm thấy một dòng trong truy vấn con:
• Kết thúc tìm kiếm trong truy vấn con
• Trả về kết quả TRUE
• Nếu chưa tìm thấy dòng nào:
• Trả về FALSE
• Tiếp tục tìm kiếm trong truy vấn con

Sử dụng câu truy vấn con (sub query)

1. Hiển thị danh sách các nhà cung cấp mà chưa có đơn đặt hàng
2. Hiển thị các đơn đặt hàng mà chưa có phiếu nhập
3. Hiển thị danh sách các đơn đặt hàng có tổng số lần nhập là lớn nhất

Giải bài tập :

--Câu 1
SELECT cc.*
FROM nhacc AS cc
WHERE
    cc.manhacc NOT IN ( SELECT dh.manhacc FROM dondh AS dh )
 
--Câu 2
SELECT dh.*
FROM dondh AS dh
WHERE dh.sodh NOT IN (SELECT pn.sodh FROM pnhap AS pn)
 
--Câu 3
SELECT  top 1 tmp.TongSLNhap AS 'TongSLNhap'
FROM dondh AS dh
    LEFT JOIN
    (
        SELECT SUM(cn.slnhap) AS 'TongSLNhap',pn.sodh
        FROM ctpnhap cn
            LEFT JOIN pnhap pn ON cn.sopn = pn.sopn
        GROUP BY pn.sodh
    ) AS tmp ON tmp.sodh = dh.sodh
ORDER BY TongSLNhap DESC

Sử dụng câu truy vấn kết hợp (correlative query)

1. Hiển thị danh sách các nhà cung cấp mà chưa có đơn đặt hàng
2. Hiển thị danh sách các nhà cung cấp gồm các cột: mã nhà cung cấp, tên nhà cung cấp và tổng số đơn đặt hàng
3. Hiển thị danh sách các nhà cung cấp gồm các cột: mã nhà cung cấp, tên nhà cung cấp, tổng số đơn đặt hàng và tổng số phiếu nhập

Giải bài tập :

-- Câu 1
SELECT cc.*
FROM nhacc AS cc
WHERE
    cc.manhacc NOT IN
            (    SELECT dh.manhacc
                FROM dondh AS dh
                WHERE dh.manhacc = cc.manhacc
            )
 
-- Câu 2
SELECT
    cc.manhacc,
    cc.tennhacc,
    (    SELECT COUNT(*)
        FROM dondh AS dh
        WHERE dh.manhacc = cc.manhacc
    ) AS 'TongDondh'
FROM nhacc AS cc
 
--Hiển thịdanh sách các nhà cung cấp gồm các cột:
--mã nhà cung cấp, tên nhà cung cấp,
--tổng số đơn đặt hàng và tổng số phiếu nhập
 
-- Câu 3
SELECT
    cc.manhacc,
    cc.tennhacc,
    (    SELECT COUNT(*)
        FROM dondh AS dh
        WHERE dh.manhacc = cc.manhacc
    ) AS 'TongDondh',
    (
        SELECT COUNT(*)
        FROM pnhap AS pn
            INNER JOIN dondh AS dh ON pn.sodh = dh.sodh
        WHERE dh.manhacc = cc.manhacc
    ) AS 'TongPnhap'
FROM nhacc AS cc

Bài viết tiếp theo

• Bài 4.5 Tích hợp kết quả từ nhiều câu truy vấn SQL (STRUCTURED QUERY LANGUAGE)
• Bài 5 Lập trình với CSDL