Thứ tự xử lý truy vấn

Giới thiệu về Oracle SQL

Hadrien Lacroix

Content Developer

Vì sao thứ tự xử lý quan trọng?

Tối ưu truy vấn
- Không có kết quả không mong muốn
- Chạy nhanh hơn

Ví dụ

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Ví dụ

    SELECT BillingCountry, AVG(Total) > 100) AS Average
->  FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Ví dụ

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
->  WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Ví dụ

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
->  GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Ví dụ

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
->  HAVING AVG(Total) > 100
    ORDER BY Average DESC

Ví dụ

->  SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Ví dụ

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
->  ORDER BY Average DESC

Có thể sai ở đâu?

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100
ORDER BY Average DESC

Bí danh không dùng được trong WHERE, GROUP BY, HAVING
Bí danh có thể dùng trong ORDER BY

Có thể sai ở đâu?

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100
ORDER BY Average DESC

Giá trị tổng hợp không lọc trong WHERE
Giá trị tổng hợp có thể lọc trong HAVING

Có thể sai ở đâu?

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100)
ORDER BY Average DESC

Hàng đơn lẻ không lọc trong HAVING
Hàng đơn lẻ có thể lọc trong WHERE

Thứ tự thực thi truy vấn

FROM và JOIN: xác định dữ liệu được truy vấn
WHERE: lọc từng hàng
GROUP BY: nhóm hàng
HAVING: lọc nhóm
SELECT: chọn cột và áp dụng hàm
DISTINCT: loại trùng lặp
UNION, UNION ALL, INTERSECT, MINUS: toán tử tập hợp
ORDER BY: sắp xếp hàng

Luyện tập nào!

Giới thiệu về Oracle SQL