LEFT JOIN Với Mệnh Đề WHERE

Vũ Huy Tâm

Trong bài Một Vài Kiểu Viết Join tôi có đưa ra một số cách viết câu lệnh join tương đương nhau, trong đó điều kiện JOIN có thể chuyển qua lại với mệnh đề WHERE. Tuy điều này đúng với INNER JOIN, khi bạn dùng LEFT JOIN sẽ dẫn đến kết quả sai ngoài mong đợi. Hãy xem xét qua ví dụ: bạn có một database về bán hàng điện thoại, gồm có hai bảng là MatHang bao gồm các loại điện thoại, và BanHang chứa các giao dịch bán hàng. Dữ liệu như sau:

CREATE TABLE dbo.MatHang(ID INT, Ten VARCHAR(100))
 
INSERT INTO dbo.MatHang VALUES(1,'iPhone 4')
INSERT INTO dbo.MatHang VALUES(2,'iPhone 4S')
INSERT INTO dbo.MatHang VALUES(3,'iPhone 5')
INSERT INTO dbo.MatHang VALUES(4,'iPhone 5S')
 
CREATE TABLE dbo.BanHang(ID INT IDENTITY, MatHangID INT, Ngay DATETIME)
INSERT INTO dbo.BanHang VALUES(4,'2014-02-25')
INSERT INTO dbo.BanHang VALUES(2,'2014-02-26')
INSERT INTO dbo.BanHang VALUES(2,'2014-02-26')
INSERT INTO dbo.BanHang VALUES(3,'2014-02-26')
INSERT INTO dbo.BanHang VALUES(2,'2014-02-26')

Để đơn giản, bảng MatHang chỉ có 4 bản ghi cho 4 loại iphone, và bảng BanHang có 1 bản ghi bán iPhone 5S ngày 25/2, 3 bản ghi bán iPhone 4S ngày 26/2, và 1 bản ghi bán iPhone 5 ngày 26/2 (iPhone 4 chưa bán được cái nào).
Giờ tôi nhận được yêu cầu: Tìm tất cả các điện thoại bán được trong ngày 26/2. Tôi có thể dùng câu INNER JOIN như sau:

SELECT MH.ID, MH.Ten, BH.Ngay--, BH.Gia
FROM dbo.MatHang MH
JOIN dbo.BanHang BH ON MH.ID = BH.MatHangID
WHERE BH.Ngay = '2014-02-26'

Kết quả:
ID Ten Ngay
2 iPhone 4S 2014-02-26 00:00:00.000
2 iPhone 4S 2014-02-26 00:00:00.000
2 iPhone 4S 2014-02-26 00:00:00.000
3 iPhone 5 2014-02-26 00:00:00.000

Giờ yêu cầu khác đi một chút: Liệt kê tất cả các điện thoại kèm theo thông tin bán hàng nếu có trong ngày 26/2. Vì yêu cầu tìm tất cả các điện thoại hiện có trong database nên tôi cần dùng LEFT JOIN. Hãy thử lệnh sau:

SELECT MH.ID, MH.Ten, BH.Ngay--, BH.Gia
FROM dbo.MatHang MH
LEFT JOIN dbo.BanHang BH ON MH.ID = BH.MatHangID
WHERE BH.Ngay = '2014-02-26'

Và nhận được:
ID Ten Ngay
2 iPhone 4S 2014-02-26 00:00:00.000
2 iPhone 4S 2014-02-26 00:00:00.000
2 iPhone 4S 2014-02-26 00:00:00.000
3 iPhone 5 2014-02-26 00:00:00.000

Vậy kết quả vẫn không có gì khác với lệnh dùng INNER JOIN, và là sai vì iPhone 4 và iPhone 5S đáng ra phải có mặt trong kết quả. Hóa ra là SQL Server đã xử lý điều kiện LEFT JOIN trước và sau khi được kết quả mới thực hiện bước lọc ở mệnh đề WHERE. Sau khi thực hiện LEFT JOIN, tập trung gian có đầy đủ tất cả các bản ghi từ hai bảng, nhưng đến bước áp dụng mệnh đề WHERE chỉ các bản ghi có Ngày = ’2014-02-26′ được giữ lại. Vô hình chung mệnh đề WHERE ở trên đã loại bỏ tác dụng của LEFT JOIN và làm cho kết quả giống hệt với INNER JOIN.
Để được kết quả như mong đợi, bạn cần chuyển điều kiện WHERE lên cùng với điều kiện LEFT JOIN:

SELECT MH.ID, MH.Ten, BH.Ngay--, BH.Gia
FROM dbo.MatHang MH
LEFT JOIN dbo.BanHang BH ON MH.ID = BH.MatHangID AND BH.Ngay = '2014-02-26'

Khi đó, tất cả các bản ghi của bảng MatHang mới được giữ lại, còn các bản ghi từ bảng BanHang sẽ được kéo sang tùy theo nó có thỏa mãn điều kiện join hay không, theo đúng định nghĩa của LEFT JOIN.
Một cách viết khác cho kết quả tương tự là dùng subquery:

SELECT MH.ID, MH.Ten, BH.Ngay--, BH.Gia
FROM dbo.MatHang MH
LEFT JOIN (SELECT * FROM dbo.BanHang WHERE Ngay = '2014-02-26') BH ON MH.ID = BH.MatHangID

Cách này hơi dài hơn nhưng có thể dễ hiểu hơn: bạn áp dụng lọc trước trên bảng rồi mới LEFT JOIN với nó.
Nói tóm lại, khi bạn LEFT JOIN với một tập con của bảng (cần áp dụng lọc lên bảng), bạn cần phải đưa điều kiện lọc vào mệnh đề join hoặc dùng subquery. Nếu bạn đưa điều kiện lọc vào mệnh đề WHERE, kết quả nhận được sẽ không đúng vì khi đó nó xử sự giống như INNER JOIN. Trên thực tế, tình huống duy nhất mà bạn đưa điều kiện lọc vào WHERE khi dùng LEFT JOIN là WHERE Col IS NULL để tìm ra các bản ghi không tồn tại trong bảng, ví dụ tìm các điện thoại không bán được cái nào trong ngày 26/2:

SELECT MH.ID, MH.Ten, BH.Ngay--, BH.Gia
FROM dbo.MatHang MH
LEFT JOIN dbo.BanHang BH ON MH.ID = BH.MatHangID AND BH.Ngay = '2014-02-26'
WHERE BH.ID IS NULL

Bạn nguoihanoi có một đoạn comment rất hay có thể bổ sung cho bài này, nên tôi copy lên đây để nhiều người nhìn được:
1. ON và WHERE là 2 tiến trình độc lập, các điều kiện ở ON thực hiện trước rồi mới đến các điều kiện ở WHERE
2. ON
Mệnh đề ON có 2 công việc
- Liên kết giữa bảng trái và phải để xác định số dòng trả về tại thời điểm này.
- Xác định giá trị các trường của bảng phải trong kết quả trả về tại thời điểm này.
Mệnh đề ON không làm công việc lọc kết quả. Nếu không có mệnh đề WHERE, kết quả luôn có số dòng lớn hơn hoặc bằng bảng trái.

3. WHERE
Mệnh đề WHERE có nhiệm vụ lọc kết quả sau khi đã thực hiện bởi ON.

4. trường ở WHERE
Mệnh đề WHERE chỉ nên chứa trường so sánh của bảng trái.




Tags:

14 Comments
Posted on 26/2/2014 | Categories: SQL Server Programming

Các bài viết tương tự

Comments
  • ecompc (27/02/2014 10:32 pm)

    Bài viết quá hay
    Thank tác giả

  • Quân (08/03/2014 2:33 am)

    Chào bác Tâm,
    Theo thiển ý của em, do SQL nối 2 bảng lại rồi mới lọc dòng. Nên khi nối sẽ tạo ra những dòng chỉ có ở bảng MạtHang, bảng bán hàng NULL, và những dòng có bán hàng. Vì vậy, làm cách này được không nhỉ>

    SELECT MH.ID, MH.Ten, BH.Ngay--, BH.Gia
    FROM dbo.MatHang MH
    LEFT JOIN dbo.BanHang BH ON MH.ID = BH.MatHangID
    WHERE BH.Ngay = '2014-02-26' or BH.ID IS NULL
    • Vũ Huy Tâm (10/03/2014 10:56 am)

      Không được bạn ah, nó sẽ loại cả iPhone 5S vì BH.ID vẫn có giá trị (BH.ID=1)

      • Quân (13/03/2014 10:32 am)

        Chào bác Tâm,
        bây giờ em mới hiểu ý của bác. Do đọc cái đề bài không kỹ. Hehe.
        Bác muôn hiện thông tin tất cả các mặt hàng, tuy nhiên: nếu mặt hàng nào bán trong ngày 26/2 thì hiện ra, còn khác ngày đó thì để NULL đúng không ạ?
        Cho em hỏi, hai cách dùng của bác, giữa where và subquery, tốc độ cái nào tối ưu hơn ạ? Em còn gà mấy cái vụ này lắm.

  • Vũ Huy Tâm (10/03/2014 11:00 am)

    Cám ơn bác Quân, xem lại mới thấy trong bài chưa bỏ trường Gia (chắc chưa bác nào chạy thử code :) )

  • nguoihanoi (10/03/2014 11:24 am)

    Chào chủ xị

    “Trên thực tế, tình huống duy nhất mà bạn đưa điều kiện lọc vào WHERE khi dùng LEFT JOIN là WHERE Col IS NULL để tìm ra các bản ghi không tồn tại trong bảng, ví dụ tìm các điện thoại không bán được cái nào trong ngày 26/2:”

    Với WHERE Col IS NUL thì nó không còn là left join nữa, câu lệnh trở thành inner join (về mục đích). Mặc dù kết quả có thể vẫn đúng, nhưng tui nghĩ với yêu cầu đó thì nên dùng subquery …

    Ngoài ra WHERE còn được sử dụng trong nhiều yêu cầu khác của left join, chứ không chỉ là duy nhất như trường hơp bác đề cập.

    Đó là thiển ý của tui, bác xét lại xem sao.

    • Vũ Huy Tâm (10/03/2014 11:39 am)

      “Với WHERE Col IS NUL thì nó không còn là left join nữa, câu lệnh trở thành inner join (về mục đích)”
      Tôi chưa hiểu sao bác lại gọi là nó trở thành inner join. Mục đích của query trên là lấy phần “không tồn tại” (phần chưa có bản ghi bán hàng), nếu làm inner join thì ko ra được như thế.

      “Ngoài ra WHERE còn được sử dụng trong nhiều yêu cầu khác của left join, chứ không chỉ là duy nhất như trường hơp bác đề cập.”
      Xin bác cho một vài ví dụ :) Tôi viết dựa trên kinh nghiệm của bản thân, nhưng những kinh nghiệm khác đều welcome

    • Quân (13/03/2014 9:58 am)

      Chào bác nguoihanoi
      Chắc bác nhầm, WHERE COL IS NOT NULL mới trở về INNER JOIN chứ nhỉ

  • nguoihanoi (12/03/2014 11:18 pm)

    Inner join, left join, subquery, apply … về thực chất cũng đều là join, sự khác biệt có chăng là mục đích, hay logic, hay bản chất, của từng loại có lẽ chữ mục đích không đúng lắm

    1 yêu cầu có thể có nhiều cách thức thực hiện khác nhau, nên tui nói với yêu cầu đó nên thực hiện với subquery, chứ không phải câu lệnh của bác cho kết quả sai. Tui chỉ bảo, với where như thế, nó có mục đích của inner join.

    Tui nói thế vì left join khá rối rắm, rất dễ có bug, nên chỉ dùng nó khi không còn cách nào khác.

    Sau đây là những luật vàng về left join

    Về mặt logic:

    1. on và where là 2 tiến trình độc lập, các điều kiện ở on thực hiện trước rồi mới đến các điều kiện ở where

    2. on
    Mệnh đề on có 2 công việc
    - Liên kết giữa bảng trái và phải để xác định số dòng trả về tại thời điểm này.
    - Xác định giá trị các trường của bảng phải trong kết quả trả về tại thời điểm này.
    - Mệnh đề on không làm công việc lọc kết quả. Nếu không có mệnh đề where, kết quả luôn có số dòng lớn hơn hoặc bằng bảng trái.

    3. where
    Mệnh đề where có nhiệm vụ lọc kết quả sau khi đã thực hiện bởi on.

    4. trường ở where
    Mệnh đề where chỉ nên chứa trường so sánh của bảng trái.

    Nếu mệnh đề where chứa trường của bảng phải thì câu lệnh không còn là left join nữa, mà về bản chất là inner join, và nó có thể viết dưới dạng inner join hay subquery hay các dạng khác. Đồng thời đây là ổ bug, nên phải rất cẩn thận nếu muốn dùng trường của bảng phải ở mệnh đề where, kết quả có thể đúng với dữ liệu tạm thời, nhưng có thể sai khi dữ liệu lớn và đa dạng. Vì thế tốt hơn hết nên dùng loại khác chứ không phải left join với trường của bảng phải ở mệnh đề where.

    Thấy thiên hạ khâm phục luật vàng vật lý của Niu-ton, nên tui tranh thủ đưa ra vài luật vàng linh tinh, hy vọng bữa nào xuống Hồ Gươm, các cave xúm lại xin chữ ký :) :(

    Bác bảo tui phải đưa ví dụ về trường hợp sử dụng where trong left join thì thú thật với bác, tui chỉ đoán mò, nên làm gì có ví dụ :) :( :) ON MH.ID=BH.MatHangID WHERE MH.ID < 4 trong trường hợp chỉ lưu tâm những mặt hàng nhất định.

    • Vũ Huy Tâm (14/03/2014 4:40 pm)

      Đoạn 4 điểm logic của bác rất hay, tôi đã copy lên bài chính để nhiều người nhìn thấy.
      Tuy nhiên điểm này thì tôi không đồng ý:
      “Nếu mệnh đề where chứa trường của bảng phải thì câu lệnh không còn là left join nữa, mà về bản chất là inner join, và nó có thể viết dưới dạng inner join hay subquery hay các dạng khác”
      mệnh đề WHERE Col IS NULL là một ngoại lệ, như tôi đã nói ở comment trước là nó lấy phần “không tồn tại” của bảng phải. Tôi không biết nếu bỏ LEFT JOIN thì phải viết thế nào cho yêu cầu trên. Dùng 1 INNER JOIN và một subquery (not exists) và UNION với nhau? Xin bác cho code cụ thể

  • Learning (02/04/2014 11:58 pm)

    “Tôi không biết nếu bỏ LEFT JOIN thì phải viết thế nào cho yêu cầu trên. Dùng 1 INNER JOIN và một subquery (not exists) và UNION với nhau? Xin bác cho code cụ thể ”
    Yêu cầu này à bác Tâm: “Tìm các điện thoại không bán được cái nào trong ngày 26/2″ ?
    Nếu đúng thì viết thế này là ok mà bác:

    SELECT ID, Ten FROM MatHang
    WHERE ID Not In(SELECT MatHangID FROM BanHang WHERE Ngay = '2014-02-26' )
  • Văn Tuyên (23/04/2014 1:13 am)

    Không phải vây. không phải cứ dùng where với left join là sai.
    Cứ thử xét vd sau: Liệt kê tất cả các điện thoại kèm theo thông tin bán hàng nếu có đối với tên =’iPhone 4′(khác đei62 kiện chủ thớt đưa ra là 26/2)

    SELECT MH.ID, MH.Ten, BH.Ngay--, BH.Gia
    FROM dbo.MatHang MH
    LEFT JOIN dbo.BanHang BH ON MH.ID = BH.MatHangID 
    WHERE Ten = 'iPhone 4'

    sẽ cho kết quả đúng và giống với

    SELECT MH.ID, MH.Ten, BH.Ngay--, BH.Gia
    FROM (SELECT * FROM dbo.MatHang WHERE  Ten = 'iPhone 4') MH
    LEFT JOIN dbo.BanHang BH ON MH.ID = BH.MatHangID

    và khác với

    SELECT MH.ID, MH.Ten, BH.Ngay--, BH.Gia
    FROM dbo.MatHang MH
    LEFT JOIN dbo.BanHang BH ON MH.ID = BH.MatHangID and Ten = 'iPhone 4'
    • Red Devilic (24/04/2014 11:12 pm)

      Mấy hôm trước gặp một trường hợp tương tự, hôm nay đọc kĩ lại bài này mới hiểu thêm một chút :D

      Thanks anh Tâm và bác nguoihanoi

  • vantien52 (05/07/2014 6:16 am)

    thực sự thì mình có nên đặt điều kiện vào khi join không hay là để ở mệnh đề where sẽ hợp lý hơn! xin các bác cho ý kiến! cám ơn.

Leave a Reply

Hướng dẫn: Để nhập mã T-SQL bạn dùng thẻ <pre lang="tsql"> và </pre>.
Ví dụ: <pre lang="tsql">SELECT * FROM MyTable</pre>