Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Truy vấn & Joins

Hiểu sâu về cách SQL Server xử lý JOIN và các loại truy vấn phức tạp là nền tảng để viết T-SQL hiệu quả và tối ưu hiệu suất.

1. INNER JOIN

INNER JOIN trả về các hàng có kết quả khớp ở cả hai bảng theo điều kiện join.

-- Cú pháp cơ bản
SELECT o.OrderId, o.OrderDate, c.CustomerName
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.CustomerId;

-- Tương đương với (cú pháp cũ, không khuyến nghị)
SELECT o.OrderId, o.OrderDate, c.CustomerName
FROM Orders o, Customers c
WHERE o.CustomerId = c.CustomerId;

Lưu ý quan trọng

  • Hàng nào không có match ở bảng kia sẽ bị loại bỏ
  • Có thể join trên nhiều cột (composite key)
-- Join trên nhiều cột
SELECT *
FROM OrderDetails od
INNER JOIN Products p 
    ON od.ProductId = p.ProductId 
    AND od.WarehouseId = p.WarehouseId;

2. LEFT / RIGHT OUTER JOIN

LEFT JOIN (LEFT OUTER JOIN)

Trả về tất cả hàng từ bảng trái + hàng khớp từ bảng phải. Nếu không có match, cột bảng phải trả về NULL.

-- Lấy tất cả khách hàng, kể cả chưa có đơn hàng nào
SELECT c.CustomerId, c.CustomerName, o.OrderId, o.OrderDate
FROM Customers c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId;

-- Tìm khách hàng CHƯA có đơn hàng
SELECT c.CustomerId, c.CustomerName
FROM Customers c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId
WHERE o.OrderId IS NULL;   -- Anti-join pattern

RIGHT JOIN (RIGHT OUTER JOIN)

Trả về tất cả hàng từ bảng phải + hàng khớp từ bảng trái.

-- Tương đương với LEFT JOIN nhưng đổi thứ tự bảng
SELECT c.CustomerId, c.CustomerName, o.OrderId
FROM Orders o
RIGHT JOIN Customers c ON o.CustomerId = c.CustomerId;

Tip: Trong thực tế, hầu hết lập trình viên dùng LEFT JOIN hết, ít dùng RIGHT JOIN để code dễ đọc hơn.

3. FULL OUTER JOIN

Trả về tất cả hàng từ cả hai bảng. Nếu không có match, side không có sẽ là NULL.

-- Lấy tất cả employees và departments, kể cả không match
SELECT e.EmployeeId, e.Name, d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d ON e.DepartmentId = d.DepartmentId;

-- Tìm các hàng chỉ tồn tại ở một trong hai bảng (symmetric difference)
SELECT e.EmployeeId, e.Name, d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d ON e.DepartmentId = d.DepartmentId
WHERE e.EmployeeId IS NULL OR d.DepartmentId IS NULL;

4. CROSS JOIN

CROSS JOIN tạo ra tích Descartes (Cartesian product): mỗi hàng bảng A kết hợp với mọi hàng bảng B.

-- Tạo tất cả tổ hợp màu sắc và kích thước
SELECT c.ColorName, s.SizeName
FROM Colors c
CROSS JOIN Sizes s;
-- Nếu Colors có 5 hàng, Sizes có 3 hàng → 15 hàng kết quả

-- Ứng dụng thực tế: Tạo calendar/date dimension
SELECT d.DateValue, h.HourValue
FROM (VALUES ('2024-01-01'), ('2024-01-02'), ('2024-01-03')) AS d(DateValue)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) AS h(HourValue);

Cảnh báo

CROSS JOIN với bảng lớn có thể tạo ra hàng triệu hàng, gây OutOfMemory hoặc timeout.

5. SELF JOIN

SELF JOIN là join bảng với chính nó, thường dùng cho dữ liệu phân cấp (hierarchical data).

-- Lấy employee và tên manager của họ
SELECT 
    e.EmployeeId,
    e.Name AS EmployeeName,
    m.Name AS ManagerName
FROM Employees e
LEFT JOIN Employees m ON e.ManagerId = m.EmployeeId;

-- Tìm các cặp nhân viên cùng phòng ban
SELECT 
    e1.Name AS Employee1,
    e2.Name AS Employee2,
    e1.DepartmentId
FROM Employees e1
INNER JOIN Employees e2 
    ON e1.DepartmentId = e2.DepartmentId
    AND e1.EmployeeId < e2.EmployeeId  -- Tránh duplicate pairs
ORDER BY e1.DepartmentId;

6. Multiple Table Joins (Kết hợp nhiều bảng)

-- JOIN 4 bảng
SELECT 
    o.OrderId,
    o.OrderDate,
    c.CustomerName,
    p.ProductName,
    od.Quantity,
    od.UnitPrice,
    cat.CategoryName
FROM Orders o
INNER JOIN Customers c    ON o.CustomerId = c.CustomerId
INNER JOIN OrderDetails od ON o.OrderId = od.OrderId
INNER JOIN Products p     ON od.ProductId = p.ProductId
INNER JOIN Categories cat ON p.CategoryId = cat.CategoryId
WHERE o.OrderDate >= '2024-01-01'
ORDER BY o.OrderDate DESC;

Thứ tự JOIN ảnh hưởng đến hiệu suất

SQL Server Optimizer thường chọn thứ tự tối ưu, nhưng có thể gợi ý thứ tự bằng OPTION (FORCE ORDER):

-- Ép optimizer join theo thứ tự viết
SELECT ...
FROM SmallTable s
INNER JOIN LargeTable l ON s.Id = l.SmallId
OPTION (FORCE ORDER);

7. Subqueries (Truy vấn con)

Non-Correlated Subquery

Subquery chạy một lần độc lập, không phụ thuộc outer query.

-- Lấy sản phẩm có giá cao hơn giá trung bình
SELECT ProductId, ProductName, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);

-- IN với subquery
SELECT * FROM Orders
WHERE CustomerId IN (
    SELECT CustomerId FROM Customers WHERE Country = 'Vietnam'
);

Correlated Subquery

Subquery tham chiếu đến outer query, chạy lại cho mỗi hàng.

-- Lấy đơn hàng gần nhất của mỗi khách hàng
SELECT o1.OrderId, o1.CustomerId, o1.OrderDate
FROM Orders o1
WHERE o1.OrderDate = (
    SELECT MAX(o2.OrderDate)
    FROM Orders o2
    WHERE o2.CustomerId = o1.CustomerId  -- Tham chiếu outer query
);

Performance Warning: Correlated subquery chạy N lần (N = số hàng outer query). Với bảng lớn, nên viết lại thành JOIN hoặc dùng window function.

-- ✅ Viết lại với ROW_NUMBER() - hiệu quả hơn
WITH LatestOrders AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS rn
    FROM Orders
)
SELECT OrderId, CustomerId, OrderDate
FROM LatestOrders
WHERE rn = 1;

Scalar Subquery

Subquery trả về đúng một giá trị (một hàng, một cột).

SELECT 
    ProductId,
    ProductName,
    Price,
    (SELECT AVG(Price) FROM Products) AS AvgPrice,
    Price - (SELECT AVG(Price) FROM Products) AS DiffFromAvg
FROM Products;

8. EXISTS vs IN vs JOIN

EXISTS

Kiểm tra sự tồn tại - dừng ngay khi tìm thấy hàng đầu tiên.

-- Khách hàng có ít nhất 1 đơn hàng
SELECT c.CustomerId, c.CustomerName
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.CustomerId = c.CustomerId
);

IN

So sánh giá trị với một tập hợp kết quả.

SELECT ProductId, ProductName
FROM Products
WHERE CategoryId IN (1, 3, 5);

SELECT ProductId, ProductName
FROM Products
WHERE CategoryId IN (SELECT CategoryId FROM Categories WHERE IsActive = 1);

So sánh hiệu suất

Cách dùngKhi nào dùngLưu ý
EXISTSSubquery bảng lớn, chỉ cần kiểm tra tồn tạiDừng sớm khi tìm thấy match
INTập giá trị nhỏ/tĩnhCó thể chậm nếu subquery trả nhiều hàng
JOINCần lấy dữ liệu từ bảng joinOptimizer thường tối ưu tốt nhất
NOT INTránh dùng khi có NULLNULL trong tập hợp khiến toàn bộ kết quả FALSE
NOT EXISTSAnti-join an toàn với NULLKhuyến nghị thay thế NOT IN
 
-- ❌ NOT IN nguy hiểm với NULL
SELECT * FROM Orders
WHERE CustomerId NOT IN (SELECT CustomerId FROM BlacklistCustomers);
-- Nếu BlacklistCustomers có 1 hàng CustomerId = NULL → không hàng nào được trả về!

-- ✅ NOT EXISTS an toàn hơn
SELECT * FROM Orders o
WHERE NOT EXISTS (
    SELECT 1 FROM BlacklistCustomers bc WHERE bc.CustomerId = o.CustomerId
);

9. APPLY Operator

APPLY gọi một table-valued function hoặc subquery cho mỗi hàng của outer table.

CROSS APPLY

Giống INNER JOIN: chỉ trả về hàng outer khi subquery có kết quả.

-- Top 3 orders gần nhất cho mỗi khách hàng
SELECT c.CustomerId, c.CustomerName, o.OrderId, o.OrderDate
FROM Customers c
CROSS APPLY (
    SELECT TOP 3 OrderId, OrderDate
    FROM Orders
    WHERE CustomerId = c.CustomerId
    ORDER BY OrderDate DESC
) o;

-- Sử dụng với Table-Valued Function
SELECT e.EmployeeId, t.SaleAmount
FROM Employees e
CROSS APPLY dbo.GetTopSalesForEmployee(e.EmployeeId, 5) t;

OUTER APPLY

Giống LEFT JOIN: trả về tất cả hàng outer, dù subquery không có kết quả (NULL).

-- Tất cả khách hàng, kể cả chưa có đơn hàng
SELECT c.CustomerId, c.CustomerName, o.OrderId, o.OrderDate
FROM Customers c
OUTER APPLY (
    SELECT TOP 1 OrderId, OrderDate
    FROM Orders
    WHERE CustomerId = c.CustomerId
    ORDER BY OrderDate DESC
) o;

APPLY vs JOIN

-- CROSS APPLY có thể làm những việc mà JOIN không làm được:
-- Gọi TVF với tham số từ outer table
SELECT p.ProductId, p.ProductName, r.RelatedProductId
FROM Products p
CROSS APPLY dbo.GetRelatedProducts(p.ProductId, p.CategoryId) r;

10. Set Operations: UNION / UNION ALL / INTERSECT / EXCEPT

UNION vs UNION ALL

-- UNION: loại bỏ duplicate (tốn thêm chi phí DISTINCT)
SELECT ProductId, ProductName FROM ActiveProducts
UNION
SELECT ProductId, ProductName FROM ArchivedProducts;

-- UNION ALL: giữ tất cả hàng, nhanh hơn
SELECT ProductId, ProductName FROM ActiveProducts
UNION ALL
SELECT ProductId, ProductName FROM ArchivedProducts;

INTERSECT

Trả về hàng có trong cả hai tập hợp.

-- Sản phẩm vừa có trong ActiveProducts vừa có trong FeaturedProducts
SELECT ProductId, ProductName FROM ActiveProducts
INTERSECT
SELECT ProductId, ProductName FROM FeaturedProducts;

EXCEPT

Trả về hàng có trong tập đầu nhưng không có ở tập sau.

-- Sản phẩm active nhưng chưa được featured
SELECT ProductId, ProductName FROM ActiveProducts
EXCEPT
SELECT ProductId, ProductName FROM FeaturedProducts;

Lưu ý

  • Số cột và kiểu dữ liệu phải tương thích
  • UNION / INTERSECT / EXCEPT đều loại bỏ duplicate
  • UNION ALL là ngoại lệ duy nhất không loại duplicate
  • ORDER BY chỉ được phép ở cuối cùng
SELECT ProductId, ProductName, 'Active' AS Source FROM ActiveProducts
UNION ALL
SELECT ProductId, ProductName, 'Archived' AS Source FROM ArchivedProducts
ORDER BY ProductName;  -- ORDER BY chỉ ở cuối cùng

11. Derived Tables (Inline Views)

Derived table là subquery trong mệnh đề FROM, hoạt động như một bảng tạm thời.

-- Derived table: lấy top customers theo doanh thu
SELECT dt.CustomerId, dt.CustomerName, dt.TotalRevenue
FROM (
    SELECT 
        c.CustomerId,
        c.CustomerName,
        SUM(o.TotalAmount) AS TotalRevenue
    FROM Customers c
    INNER JOIN Orders o ON c.CustomerId = o.CustomerId
    GROUP BY c.CustomerId, c.CustomerName
) AS dt   -- Bắt buộc phải đặt alias
WHERE dt.TotalRevenue > 10000
ORDER BY dt.TotalRevenue DESC;

CTE vs Derived Table

-- CTE (Common Table Expression) - thường dễ đọc hơn
WITH CustomerRevenue AS (
    SELECT 
        c.CustomerId,
        c.CustomerName,
        SUM(o.TotalAmount) AS TotalRevenue
    FROM Customers c
    INNER JOIN Orders o ON c.CustomerId = o.CustomerId
    GROUP BY c.CustomerId, c.CustomerName
)
SELECT * FROM CustomerRevenue WHERE TotalRevenue > 10000;

Note: CTE và Derived table về mặt hiệu suất thường tương đương - optimizer xử lý chúng giống nhau. CTE có thể tái sử dụng trong cùng query.

12. Query Hints (Gợi ý truy vấn)

NOLOCK (READ UNCOMMITTED)

-- Đọc dữ liệu chưa được commit (dirty read), tránh blocking
SELECT * FROM Orders WITH (NOLOCK)
WHERE OrderDate >= '2024-01-01';

-- Cảnh báo: có thể đọc dữ liệu "phantom" hoặc dữ liệu bị rollback!

FORCESEEK

-- Ép optimizer dùng Index Seek thay vì Scan
SELECT * FROM Orders WITH (FORCESEEK)
WHERE CustomerId = 12345;

-- Ép seek trên index cụ thể
SELECT * FROM Orders WITH (FORCESEEK (IX_Orders_CustomerId (CustomerId)))
WHERE CustomerId = 12345;

INDEX hint

-- Ép dùng index cụ thể
SELECT * FROM Orders WITH (INDEX = IX_Orders_OrderDate)
WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31';

-- Ép Table Scan (bỏ qua tất cả indexes)
SELECT * FROM SmallTable WITH (INDEX = 0);

OPTION hints

-- Force recompile plan cho query này
SELECT * FROM Orders WHERE CustomerId = @Id 
OPTION (RECOMPILE);

-- Giới hạn parallelism
SELECT * FROM BigTable 
OPTION (MAXDOP 4);

-- Optimize for specific parameter value
SELECT * FROM Orders WHERE CustomerId = @Id
OPTION (OPTIMIZE FOR (@Id = 1000));

-- Optimize for unknown (avoid parameter sniffing)
SELECT * FROM Orders WHERE CustomerId = @Id
OPTION (OPTIMIZE FOR UNKNOWN);

Best practice: Chỉ dùng hints khi bạn biết rõ lý do - hints có thể trở nên không còn tối ưu khi data distribution thay đổi.

Q&A - Phỏng vấn

Junior Level

Q: Sự khác biệt giữa INNER JOIN và LEFT JOIN là gì?

A: INNER JOIN chỉ trả về hàng có match ở cả hai bảng. LEFT JOIN trả về tất cả hàng bảng trái, nếu không có match bảng phải thì các cột đó là NULL. Dùng LEFT JOIN khi cần giữ lại tất cả records của bảng chính dù không có dữ liệu liên quan.

Q: UNION khác UNION ALL như thế nào?

A: UNION loại bỏ các hàng trùng lặp (thực hiện DISTINCT ngầm), UNION ALL giữ tất cả hàng kể cả duplicate. UNION chậm hơn vì phải so sánh để loại duplicate. Dùng UNION ALL khi biết chắc không có duplicate hoặc không quan tâm đến duplicate để tăng hiệu suất.

Q: Subquery là gì? Cho ví dụ?

A: Subquery là truy vấn SQL nằm bên trong một truy vấn khác (trong WHERE, FROM, SELECT, HAVING). Ví dụ: SELECT * FROM Products WHERE Price > (SELECT AVG(Price) FROM Products) - subquery tính giá trung bình, outer query lọc sản phẩm đắt hơn trung bình.

Mid Level

Q: Khi nào nên dùng EXISTS thay vì IN?

A: Dùng EXISTS khi subquery trả về nhiều hàng (bảng lớn) vì EXISTS dừng ngay khi tìm thấy một hàng match (short-circuit). NOT IN phải tránh nếu tập có thể chứa NULL vì kết quả sẽ luôn rỗng. NOT EXISTS là lựa chọn an toàn hơn. Với tập giá trị nhỏ và tĩnh thì IN vẫn dùng được.

Q: Correlated subquery là gì? Tại sao có thể gây vấn đề hiệu suất?

A: Correlated subquery tham chiếu đến cột của outer query, nghĩa là nó được thực thi lại cho mỗi hàng của outer query. Nếu outer query trả về N hàng thì subquery chạy N lần. Với bảng lớn điều này rất tốn kém. Giải pháp: viết lại bằng JOIN hoặc window functions (ROW_NUMBER, RANK…).

Q: CROSS APPLY và OUTER APPLY khác nhau thế nào?

A: CROSS APPLY tương tự INNER JOIN - chỉ trả về outer rows khi subquery/TVF có kết quả. OUTER APPLY tương tự LEFT JOIN - trả về tất cả outer rows, nếu không có match thì cột bên trong là NULL. APPLY thường dùng để gọi table-valued function với tham số từ outer table, hoặc để lấy top-N rows per group.

Q: Tại sao NOT IN nguy hiểm khi có NULL trong tập hợp?

A: Vì SQL dùng logic ba giá trị (TRUE/FALSE/UNKNOWN). Khi so sánh bất kỳ giá trị nào với NULL, kết quả là UNKNOWN. Trong NOT IN, nếu tập chứa NULL thì điều kiện x NOT IN (..., NULL, ...) tương đương x != NULL là UNKNOWN, khiến không hàng nào được trả về. Luôn dùng NOT EXISTS thay thế.

Senior Level

Q: Giải thích cơ chế Join Algorithms của SQL Server (Nested Loops, Hash Join, Merge Join)?

A:

  • Nested Loops: Với mỗi hàng outer table, duyệt qua inner table để tìm match. Hiệu quả khi outer table nhỏ và inner table có index trên join key. O(N×M) worst case.
  • Hash Join: Build hash table từ bảng nhỏ hơn (build input), sau đó probe với bảng lớn hơn. Hiệu quả cho large, unsorted, unindexed inputs. Tốn bộ nhớ - có thể spill ra TempDB.
  • Merge Join: Cả hai inputs phải được sắp xếp theo join key. Rất hiệu quả khi inputs đã có order (từ index). O(N+M). Optimizer chọn dựa trên ước tính cardinality và có index hay không.

Q: Làm thế nào để viết lại correlated subquery lấy top-N per group hiệu quả hơn trong SQL Server?

A: Dùng window functions thay vì correlated subquery:

-- ❌ Correlated subquery - chạy N lần
SELECT * FROM Orders o1
WHERE OrderDate IN (
    SELECT TOP 3 OrderDate FROM Orders o2
    WHERE o2.CustomerId = o1.CustomerId
    ORDER BY OrderDate DESC
);

-- ✅ ROW_NUMBER() - hiệu quả hơn nhiều
WITH RankedOrders AS (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY CustomerId 
        ORDER BY OrderDate DESC
    ) AS rn
    FROM Orders
)
SELECT * FROM RankedOrders WHERE rn <= 3;

Window function tiếp cận theo set-based, SQL Server có thể tối ưu tốt hơn nhiều so với row-by-row correlated subquery.

Q: Khi nào APPLY operator tốt hơn JOIN?

A: APPLY tốt hơn JOIN trong các trường hợp:

  1. TVF với tham số từ outer row: CROSS APPLY dbo.fn(e.EmployeeId) - JOIN không thể làm điều này.
  2. Top-N per group: CROSS APPLY (SELECT TOP 3 ... WHERE CustomerId = c.CustomerId ORDER BY ...) - thường hiệu quả hơn ROW_NUMBER() cho cardinality thấp.
  3. Tránh outer reference trong subquery phức tạp: APPLY cho phép tái sử dụng alias trong cùng mệnh đề APPLY.

JOIN thường được optimizer tối ưu tốt hơn khi chỉ cần equi-join đơn giản.