[Tài liệu học SQL] Phần 6 – SQL nâng cao với SUBQUERY và CTE

Trong quá trình sử dụng SQL, đôi khi chúng ta có thể gặp phải những vấn đề phức tạp đòi hỏi nhiều thứ hơn là chỉ một truy vấn đơn giản.
Ví dụ: chúng ta muốn so sánh kết quả của các truy vấn khác nhau hoặc thực hiện các phép tính trên các tập hợp con dữ liệu hoặc sử dụng lại cùng một truy vấn nhiều lần. Làm cách nào chúng ta có thể giải quyết được các vấn đề này trong SQL?
Hãy cùng tìm hiểu về Subquery và CTE và ứng dụng của chúng để giải quyết những bài toán trên nhé.

Đây là phần khép lại cho chuỗi series tài liệu tự học SQL của DATAPOT. Tài liệu gồm 6 phần, vì vậy đừng bỏ lỡ các phần tài liệu vô cùng hữu dụng phía trước nhé.

Truy vấn con (Subquery)

Truy vấn con (Subquery) thường được dùng trong các câu truy vấn đòi hỏi tính toán phức tạp.

Chúng ta sử dụng Subquery khi cần tính toán dữ liệu, tạo ra một bảng dữ liệu tạm thời mới.

Truy vấn con được sử dụng một lần trong câu lệnh truy vấn nơi nó xác định.

Có thể sử dụng truy vấn con (Subquery) trong các câu lệnh (Statement) như là SELECT, FROM, JOIN hoặc bất kỳ phép toán tập hợp nào (Set operators).

Truy vấn con (Subquery) dùng với IN

Subquery khi được sử dụng với IN sẽ được coi là một điều kiện trong hàm IN.

Cú pháp của Subquery dùng với IN:

FROM Table_2)

      SELECT
          Col_1,
          Col_2,...
      FROM Table_1
      WHERE Col_1 IN(
                SELECT Col_1
                FROM Table_2)

Ví dụ: Từ bảng Person thuộc bộ dữ liệu AdventureWorksFull, truy vấn các cột BusinessEntityID, LastName. Với điều kiện thoả mãn BusinessEntityID có JobTitle là Marketing Specialist.

Giải thích truy vấn con:

• FROM: Dữ liệu được truy vấn từ bảng HumanResources.Employee.
• WHERE: Lọc bản ghi thoả mãn điều kiện BusinessEntityID có JobTitle = ‘Marketing Specialist’.
• SELECT: Truy vấn cột BusinessEntityID.

Giải thích truy vấn chính:

• FROM: Dữ liệu được truy vấn từ bảng Person.Person.
• WHERE: Lọc bản ghi thoả mãn điều kiện của truy vấn con.
• SELECT: Truy vấn các cột BusinessEntityID, LastName.

Truy vấn con (Subquery) sử dụng với mệnh đề FROM

Subquery còn được sử dụng như một bảng dữ liệu khi được sử dụng trong mệnh đề FROM.

Cú pháp của Subquery trong mệnh đề FROM:

      SELECT
          Col_1,
          Col_2,...
      FROM (SELECT
                    Col_1,
                    Col_2
              FROM Table_1) AS Subquery_Name

Ví dụ: Từ bảng Product được gán tên SP và bảng ProductDescription (Điều kiện ModifiedDate = ‘2007-06-01’). Truy vấn các cột Name, ProductCategory và ProductNumber từ bảng Product và truy vấn cột Description từ bảng ProductDescription.

Giải thích câu lệnh truy vấn con:

• FROM: Dữ liệu được truy vấn từ bảng Sales.ProductDescription.
• WHERE: Lọc bản ghi thoả mãn điều kiện ModifieDate là 2007-06-01.
• SELECT: Truy vấn cột Description.
• AS: Gán tên truy vấn con là SPD.

Giải thích câu lệnh truy vấn chính:

• FROM: Dữ liệu được truy vấn từ bảng SalesLT.Product được gán tên SP và câu lệnh truy vấn con.
• SELECT: SP.Name, SP.ProductCategoryID, SP.ProductNumber, SPD. Description.

Truy vấn con tương quan (Correlated Subquery)

Một trong những ứng dụng khác của Subquery là Correlated Subquery, bạn có thể tham khảo thêm sau đây.

Truy vấn con tương quan là một truy vấn con sử dụng các giá trị từ truy vấn chính trong mệnh đề WHERE của nó.

Cú pháp của truy vấn con tương quan:

      SELECT
          Col_1,
          Col_2,...
      FROM Table_1 AS tb1
      WHERE Col_Name Operator (
                               SELECT 
                                     Col_3
                               FROM Table_2 AS tb2
                               WHERE tb1.Related_Col = tb2.Related_Col)

• Col_1, Col_2, Col_3: Cột cần truy vấn.
• Table_1: Dữ liệu truy vấn trong bảng chính.
• Col_Name: Tên cột cần so sánh.
• Operator: Toán tử so sánh.
• Table_2: Dữ liệu dùng để truy vấn trong bảng con.
• tb1.Related_Col = tb2.Related_Col: Truy vấn trên bảng con với điều kiện phụ thuộc vào bảng chính.

Ví dụ: Từ bảng Product thuộc bộ dữ liệu adventureworks, truy vấn các cột ProductID, Name, ListPric. Lọc điều kiện thoả mãn các sản phẩm cao hơn mức giá trung bình của những sản phẩm có cùng ProductCategoryID.

Giải thích truy vấn con:

• FROM: Dữ liệu truy vấn từ bảng Product.
• WHERE: ProductCategoryID của PD2 bằng với ProductCategoryID của PD1.
• SELECT: Truy vấn giá trung bình của sản phẩm.

Giải thích câu lệnh truy vấn:

• FROM: Dữ liệu truy vấn từ bảng Product.
• WHERE: Lọc điều kiện sản phẩm thoả mãn điều kiện giá cao hơn mức giá trung bình của nhưng sản phẩm có chung ProductCategoryID.

Bảng chứa dữ liệu tạm thời (Common Table Expressions)

Bảng chứa dữ liệu tạm thời (Common Table Expressions) sẽ tính toán và tạo ra các bảng chứa dữ liệu tạm thời để có thể tái sử dụng trong câu lệnh truy vấn chính.

Do vậy mà CTE không làm cho cơ sở dữ liệu (Database) nặng lên.

Đặc điểm:

• CTE không sử dụng trong mệnh đề WHERE kết hợp với từ khóa IN hoặc EXISTS.

Cú pháp của CTE:

      WITH CTE_Name (Col_1, Col_2, ...) AS (      
      SELECT
          Col_1,
          Col_2,...
      FROM Table
      WHERE Condition
      SELECT
           Col_3,
           Col_4,...
              FROM CTE_Name

• WITH CTE_Name (Col_1, Col_2, …): Đặt tên cho CTE và liệt kê các cột nếu bạn muốn chỉ định tên cột. Nếu không, bạn có thể bỏ qua phần (Col_1, Col_2, …). Tên CTE sẽ được sử dụng để tham chiếu trong phần câu truy vấn chính.
• AS(….. WHERE Condition): Đây là câu truy vấn tạo CTE.
• Col_1, Col_2, Col_3, Col_4: Cột muốn truy vấn.
• SELECT … FROM CTE_Name: Câu truy vấn chính có sử dụng CTE.

Ví dụ: Từ bảng Product và bảng chứa dữ liệu tạm thời (CTE) kết hợp điểm chung (INNER JOIN) giữa bảng Product và bảng ProductCategory. Truy vấn các cột Name, ProductNumber, StandardCost. Với điều kiện StandardCost lớn hơn 1000.

• Giải thích câu lệnh truy vấn:
• FROM: Dữ liệu truy vấn lấy từ bảng ProductInformation.
• SELECT TOP 10 *: Truy vấn 10 hàng đầu của bảng ProductInformation.
• Giải thích CTE:
• WITH ProductInformation: CTE được đặt tên ProductInformation.
• FROM: Dữ liệu được truy vấn từ bảng SalesLT.Product được gán tên SP.
• INNER JOIN: Kết hợp điểm chung của bảng ProductCategory được gán tên SPC.
• ON: Khai báo điều kiện kết hợp bảng từ cột khoá chính ProductCategoryID trong bảng Product và khoá ngoại ProductCategoryID trong bảng ProductCategory.
• WHERE: Lọc điều kiện thoả mãn StandardCost > 1000
• SELECT: Truy vấn các cột SPC.Name, SP.ProductNumber, SP.StandardCost.

Một số lưu ý thường gặp khi sử dụng Subquery và CTE

Lưu ý 1: Sử dụng Subquery trong mệnh để FROM cần được ALIAS.

Nếu không được gán tên ALIAS, câu truy vấn sẽ báo lỗi.

Do vậy, khi sử dụng Subquery cần phải nhớ gán tên alias.

Lưu ý 2: Nên sử dụng CTE thay cho Subquery cho câu lệnh truy vấn không bị rối.

Lưu ý 3: Có thể có nhiều CTE trong 1 câu lệnh.

Ưu và nhược điểm

– CTE có thể được sử dụng nhiều lần trong một truy vấn, trong khi SUBQUERY chỉ có thể được sử dụng một lần. Điều này có thể làm cho định nghĩa truy vấn ngắn hơn nhiều nhưng không nhất thiết mang lại hiệu suất được cải thiện.

– SUBQUERY có thể được sử dụng trong mệnh đề WHERE kết hợp với từ khóa IN hoặc EXISTS, nhưng bạn không thể thực hiện điều này với CTE.

– CTE có thể được sử dụng để thực hiện các truy vấn đệ quy, rất hữu ích cho việc xử lý dữ liệu phân cấp hoặc dạng cây, nhưng các truy vấn phụ thì không thể.

Bằng cách sử dụng những kỹ thuật này, chúng ta có thể viết các truy vấn nâng cao và mạnh mẽ có thể trả lời nhiều câu hỏi thú vị và hữu ích về dữ liệu. Chúng tôi cũng có thể cải thiện khả năng đọc và khả năng duy trì các truy vấn của mình bằng cách chia nhỏ các truy vấn phức tạp thành các đơn vị nhỏ hơn, dễ quản lý hơn. 

DATAPOT mong rằng chuỗi series này hữu ích đối với những bạn đang muốn tìm hiểu về SQL. Mong rằng sẽ nhận được phản hồi, nhận xét và đóng góp đến từ mọi người để DATAPOT nỗ lực đem lại giá trị nhiều hơn nữa. Cảm ơn các bạn đã luôn quan tâm và ủng hộ chuỗi series này! 

Lưu Trọng Hưng

Data Analyst