เปรียบเทียบแถวกับคิวรีย่อยที่สัมพันธ์กัน

แบบสอบถามที่สัมพันธ์กันคือแบบสอบถามย่อยที่อ้างอิงคอลัมน์จากแบบสอบถามภายนอก ซึ่งสร้างการขึ้นต่อกันที่ทําให้แบบสอบถามย่อยดําเนินการหนึ่งครั้งสําหรับแต่ละแถวที่ประมวลผลโดยแบบสอบถามภายนอก แม้ว่าสิ่งนี้อาจฟังดูไม่มีประสิทธิภาพ แต่คิวรีที่สัมพันธ์กันมีประโยชน์สําหรับการเปรียบเทียบและการคํานวณแบบแถวต่อแถวที่ยากหรือเป็นไปไม่ได้ที่จะแสดงเป็นอย่างอื่น

ทําความเข้าใจการดําเนินการแบบสอบถามย่อยที่สัมพันธ์กัน

แบบสอบถามย่อยที่สัมพันธ์กันจะอ้างอิงคอลัมน์อย่างน้อยหนึ่งคอลัมน์จากแบบสอบถามภายนอก ซึ่งจะสร้างการขึ้นต่อกันเชิงตรรกะระหว่างทั้งสอง ซึ่งแตกต่างจากแบบสอบถามย่อยปกติที่ดําเนินการครั้งเดียวและส่งคืนผลลัพธ์คงที่ แบบสอบถามย่อยที่สัมพันธ์กันจะดําเนินการซ้ําๆ หนึ่งครั้งสําหรับแต่ละแถวที่คิวรีภายนอกประมวลผล

คิดว่ามันเหมือนกับลูปที่ซ้อนกัน: สําหรับแต่ละแถวในแบบสอบถามภายนอก ฐานข้อมูลจะประเมินแบบสอบถามย่อยโดยใช้ค่าของแถวนั้น ลักษณะการทํางานนี้เปิดใช้งานการเปรียบเทียบแบบแถวต่อแถวที่มีประสิทธิภาพ แต่ยังหมายความว่าคุณต้องเข้าใจรูปแบบการดําเนินการเพื่อเขียนคิวรีที่มีประสิทธิภาพ

พิจารณาว่าแบบสอบถามทั้งสองนี้แตกต่างกันอย่างไร:

-- Non-correlated subquery (executes once)
SELECT ProductID, Name, ListPrice
FROM SalesLT.Product
WHERE ListPrice > (SELECT AVG(ListPrice) FROM SalesLT.Product);

-- Correlated subquery (executes per outer row)
SELECT p1.ProductID, p1.Name, p1.ListPrice
FROM SalesLT.Product AS p1
WHERE p1.ListPrice > (
    SELECT AVG(p2.ListPrice)
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p1.ProductCategoryID  -- References outer query
);

ในตัวอย่างที่ไม่สัมพันธ์กัน คิวรีย่อยจะคํานวณราคาเฉลี่ยเดียวในผลิตภัณฑ์ทั้งหมด ค่านี้จะถูกคํานวณหนึ่งครั้ง จากนั้นราคาของผลิตภัณฑ์แต่ละรายการจะถูกเปรียบเทียบกับตัวเลขคงที่นั้น

ในตัวอย่างที่สัมพันธ์กัน การอ้างอิง p1.ProductCategoryID แบบสอบถามย่อยจากแบบสอบถามภายนอก สิ่งนี้จะสร้างการขึ้นต่อกัน: สําหรับแต่ละแถวผลิตภัณฑ์ แบบสอบถามย่อยจะคํานวณราคาเฉลี่ยสําหรับประเภทเฉพาะนั้น ผลิตภัณฑ์ในหมวดหมู่ "จักรยาน" จะถูกเปรียบเทียบกับค่าเฉลี่ยของจักรยาน ในขณะที่ผลิตภัณฑ์ใน "อุปกรณ์เสริม" จะถูกเปรียบเทียบกับค่าเฉลี่ยของอุปกรณ์เสริม

กรองด้วยคิวรีย่อยที่สัมพันธ์กัน

คิวรีย่อยที่สัมพันธ์กันในส่วน WHERE คําสั่งเปิดใช้งานเงื่อนไขการกรองเฉพาะแถวที่เป็นไปไม่ได้กับการเปรียบเทียบแบบคงที่ แทนที่จะเปรียบเทียบกับค่าคงที่เดียว แต่ละแถวจะได้รับการประเมินเทียบกับค่าที่คํานวณแบบไดนามิกตามแอตทริบิวต์ของแถวนั้น

รูปแบบนี้มีประโยชน์เมื่อคุณต้องการระบุค่าผิดปกติภายในกลุ่ม ค้นหาเรกคอร์ดที่เกินขีดจํากัดของประเภทของตนเอง หรือใช้กฎทางธุรกิจที่แตกต่างกันไปตามบริบท ตัวอย่างต่อไปนี้พบผลิตภัณฑ์ที่มีราคาสูงกว่าค่าเฉลี่ยของหมวดหมู่ ซึ่งหมายความว่าอุปกรณ์เสริมที่มีราคาต่ําอาจถูกตั้งค่าสถานะว่ามีราคาแพงในขณะที่จักรยานที่มีราคาสูงกว่าอาจไม่:

SELECT 
    p.ProductID,
    p.Name,
    p.ListPrice,
    pc.Name AS Category
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > (
    SELECT AVG(p2.ListPrice)
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p.ProductCategoryID
)
ORDER BY pc.Name, p.ListPrice DESC;

คุณสามารถใช้รูปแบบเดียวกันเพื่อระบุลูกค้าที่มีพฤติกรรมแตกต่างจากพื้นฐานส่วนบุคคลของพวกเขา

คําค้นหาต่อไปนี้จะค้นหาลูกค้าที่ทําการสั่งซื้ออย่างน้อยหนึ่งใบที่เกินมูลค่าการสั่งซื้อเฉลี่ยของตนเอง ซึ่งจะช่วยระบุรูปแบบการซื้อที่ผิดปกติหรือธุรกรรมที่มีมูลค่าสูง:

SELECT DISTINCT
    c.CustomerID,
    c.FirstName,
    c.LastName
FROM SalesLT.Customer AS c
INNER JOIN SalesLT.SalesOrderHeader AS soh
    ON c.CustomerID = soh.CustomerID
WHERE soh.TotalDue > (
    SELECT AVG(soh2.TotalDue)
    FROM SalesLT.SalesOrderHeader AS soh2
    WHERE soh2.CustomerID = c.CustomerID
);

ใช้กับ EXISTS การสืบค้นย่อยที่สัมพันธ์กัน

EXISTSตัวดําเนินการรวมกับแบบสอบถามย่อยที่สัมพันธ์กันจะทดสอบว่ามีแถวที่ตรงกันในตารางที่เกี่ยวข้องหรือไม่ โดยส่งกลับผลลัพธ์จริงหรือเท็จอย่างง่าย รูปแบบนี้มีประสิทธิภาพสูงเนื่องจากกลไกฐานข้อมูลสามารถหยุดการค้นหาได้ทันทีที่พบแถวแรกที่ตรงกัน ซึ่งแตกต่างจากการสืบค้นย่อยที่ส่งคืนข้อมูลจริง จะต้อง EXISTS ยืนยันการมีอยู่หรือขาดหายไปเท่านั้น

ใช้ EXISTS เมื่อคุณต้องการตอบคําถาม เช่น "ลูกค้ารายใดที่สั่งซื้อ" หรือ "ผลิตภัณฑ์ใดที่ไม่เคยขาย" โดยทั่วไปคิวรีย่อยจะใช้ SELECT 1 เนื่องจากค่าจริงไม่สําคัญ:

-- Find customers who have placed at least one order
SELECT CustomerID, FirstName, LastName
FROM SalesLT.Customer AS c
WHERE EXISTS (
    SELECT 1
    FROM SalesLT.SalesOrderHeader AS soh
    WHERE soh.CustomerID = c.CustomerID
);

-- Find customers who have never placed an order
SELECT CustomerID, FirstName, LastName
FROM SalesLT.Customer AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM SalesLT.SalesOrderHeader AS soh
    WHERE soh.CustomerID = c.CustomerID
);

EXISTS จะมีค่ามากยิ่งขึ้นเมื่อคุณต้องการตรวจสอบเงื่อนไขที่ซับซ้อนซึ่งรวมเกณฑ์หลายเกณฑ์เข้าด้วยกัน คุณสามารถเพิ่มตรรกะการกรองใดๆ ภายในแบบสอบถามย่อย และแบบสอบถามภายนอกจะรวมเฉพาะแถวที่มีแถวที่เกี่ยวข้องที่ตรงกันอย่างน้อยหนึ่งแถว

ตัวอย่างต่อไปนี้แสดงให้เห็นถึงการค้นหาสินค้าที่มีคําสั่งซื้อและหมวดหมู่ที่มีปริมาณมากซึ่งสินค้าทุกรายการตรงตามเกณฑ์ราคา:

-- Find products that have been ordered in quantities greater than 10
SELECT p.ProductID, p.Name
FROM SalesLT.Product AS p
WHERE EXISTS (
    SELECT 1
    FROM SalesLT.SalesOrderDetail AS sod
    WHERE sod.ProductID = p.ProductID
      AND sod.OrderQty > 10
);

-- Find categories where all products are priced above $100
SELECT pc.ProductCategoryID, pc.Name
FROM SalesLT.ProductCategory AS pc
WHERE NOT EXISTS (
    SELECT 1
    FROM SalesLT.Product AS p
    WHERE p.ProductCategoryID = pc.ProductCategoryID
      AND p.ListPrice <= 100
);

คํานวณค่าด้วยแบบสอบถามย่อยที่สัมพันธ์กันใน SELECT

การสืบค้นย่อยที่สัมพันธ์กันในส่วน SELECT คําสั่งจะคํานวณค่าแยกต่างหากสําหรับแต่ละแถวในชุดผลลัพธ์ของคุณ รูปแบบนี้ช่วยให้คุณสามารถรวมค่ารวมหรือค่าที่ได้รับจากตารางที่เกี่ยวข้องควบคู่ไปกับรายละเอียดของแถวหลัก โดยไม่ต้องยุบผลลัพธ์เป็นกลุ่ม

วิธีการนี้มีประโยชน์เมื่อคุณต้องการแสดงข้อมูลตามบริบท เช่น การแสดงผลิตภัณฑ์แต่ละรายการควบคู่ไปกับราคาเฉลี่ยของหมวดหมู่ หรือพนักงานแต่ละคนควบคู่ไปกับจํานวนพนักงานทั้งหมดของแผนก คิวรีย่อยจะดําเนินการหนึ่งครั้งต่อแถว โดยใช้ค่าของแถวนั้นเพื่อกรองการคํานวณ:

-- Show each product with its category's average price
SELECT 
    p.ProductID,
    p.Name,
    p.ListPrice,
    (
        SELECT AVG(p2.ListPrice)
        FROM SalesLT.Product AS p2
        WHERE p2.ProductCategoryID = p.ProductCategoryID
    ) AS CategoryAvgPrice,
    p.ListPrice - (
        SELECT AVG(p2.ListPrice)
        FROM SalesLT.Product AS p2
        WHERE p2.ProductCategoryID = p.ProductCategoryID
    ) AS DifferenceFromAvg
FROM SalesLT.Product AS p;

คุณยังสามารถใช้รูปแบบนี้เพื่อนับเรกคอร์ดที่เกี่ยวข้องหรือดึงค่าเฉพาะจากตารางที่เกี่ยวข้อง แบบสอบถามต่อไปนี้สร้างสรุปลูกค้าที่มีจํานวนใบสั่งของลูกค้าแต่ละรายและวันที่สั่งซื้อล่าสุด ซึ่งคํานวณแยกกันสําหรับแต่ละแถวของลูกค้า:

-- Show each customer with their order count
SELECT 
    c.CustomerID,
    c.FirstName,
    c.LastName,
    (
        SELECT COUNT(*)
        FROM SalesLT.SalesOrderHeader AS soh
        WHERE soh.CustomerID = c.CustomerID
    ) AS OrderCount,
    (
        SELECT MAX(soh.OrderDate)
        FROM SalesLT.SalesOrderHeader AS soh
        WHERE soh.CustomerID = c.CustomerID
    ) AS LastOrderDate
FROM SalesLT.Customer AS c;

ค้นหา top-N ต่อกลุ่มด้วยคําค้นหาย่อยที่สัมพันธ์กัน

หนึ่งในการประยุกต์ใช้ประโยชน์มากที่สุดของแบบสอบถามย่อยที่สัมพันธ์กันคือการค้นหารายการ N อันดับแรกภายในแต่ละกลุ่ม รูปแบบนี้จะตอบคําถามเช่น "ผลิตภัณฑ์ที่แพงที่สุดสามรายการในแต่ละหมวดหมู่คืออะไร" หรือ "ใครคือพนักงานขายห้าอันดับแรกในแต่ละภูมิภาค"

แบบสอบถามย่อยที่สัมพันธ์กันจะตรวจสอบแต่ละแถวและพิจารณาว่าแถวนั้นอยู่ใน N อันดับแรกสําหรับกลุ่มหรือไม่ โดยตรวจสอบว่ามีแถวอื่นๆ ในกลุ่มเดียวกันที่มีอันดับสูงกว่ากี่แถว วิธีนี้ใช้ได้ดีเมื่อฟังก์ชันหน้าต่างไม่พร้อมใช้งาน หรือเมื่อคุณต้องการตรรกะการจัดอันดับที่ซับซ้อนซึ่งฟังก์ชันหน้าต่างไม่สามารถแสดงได้

แบบสอบถามต่อไปนี้จะค้นหาผลิตภัณฑ์ที่มีราคาแพงที่สุดสามอันดับแรกต่อประเภทโดยการเลือกผลิตภัณฑ์ที่มีรหัสปรากฏใน 3 อันดับแรกสําหรับประเภท:

SELECT 
    pc.Name AS Category,
    p.Name AS Product,
    p.ListPrice
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ProductID IN (
    SELECT TOP 3 p2.ProductID
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p.ProductCategoryID
    ORDER BY p2.ListPrice DESC
)
ORDER BY pc.Name, p.ListPrice DESC;

วิธีการอื่นจะนับจํานวนรายการที่อยู่ในอันดับที่สูงกว่าแถวปัจจุบัน ถ้ารายการน้อยกว่า N มีค่าสูงกว่าแถวปัจจุบันจะอยู่ใน N ด้านบน เทคนิคนี้จัดการกับความสัมพันธ์ที่แตกต่างกันและมีประโยชน์เมื่อคุณต้องการไอเท็มทั้งหมดที่ผูกกับตําแหน่งที่ N:

-- Find products that are in the top 3 by price within their category
SELECT 
    pc.Name AS Category,
    p.Name AS Product,
    p.ListPrice
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE (
    SELECT COUNT(*)
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p.ProductCategoryID
      AND p2.ListPrice > p.ListPrice
) < 3
ORDER BY pc.Name, p.ListPrice DESC;

เปรียบเทียบแถวที่ต่อเนื่องกัน

การสืบค้นย่อยที่สัมพันธ์กันสามารถเข้าถึงค่าจากแถวก่อนหน้าหรือแถวถัดไปตามเกณฑ์การสั่งซื้อ ซึ่งทําให้สามารถเปรียบเทียบช่วงเวลาต่อช่วงเวลาและการวิเคราะห์แนวโน้มได้ รูปแบบนี้มีประโยชน์สําหรับการคํานวณการเปลี่ยนแปลงระหว่างเรกคอร์ดที่ต่อเนื่องกัน เช่น การเปรียบเทียบแต่ละคําสั่งซื้อกับคําสั่งซื้อก่อนหน้า หรือการติดตามว่ามูลค่ามีวิวัฒนาการอย่างไรเมื่อเวลาผ่านไป

แบบสอบถามย่อยจะค้นหาแถวที่เกี่ยวข้องโดยการกรองแถวที่อยู่ก่อน (หรือหลัง) แถวปัจจุบันในลําดับตรรกะ จากนั้นจึงจัดลําดับผลลัพธ์เพื่อให้ได้แถวที่อยู่ติดกันทันที:

-- Show each order with the previous order's total
SELECT 
    soh.SalesOrderID,
    soh.OrderDate,
    soh.TotalDue,
    (
        SELECT TOP 1 soh2.TotalDue
        FROM SalesLT.SalesOrderHeader AS soh2
        WHERE soh2.CustomerID = soh.CustomerID
          AND soh2.OrderDate < soh.OrderDate
        ORDER BY soh2.OrderDate DESC
    ) AS PreviousOrderTotal
FROM SalesLT.SalesOrderHeader AS soh
ORDER BY soh.CustomerID, soh.OrderDate;

เลือกระหว่างการสืบค้นย่อยที่สัมพันธ์กันและทางเลือกอื่น

การสืบค้นย่อยที่สัมพันธ์กันไม่ใช่วิธีที่ดีที่สุดเสมอไป ตารางต่อไปนี้ช่วยคุณเลือกเทคนิคที่เหมาะสม:

ข้อควรพิจารณาเกี่ยวกับประสิทธิภาพการทำงาน

คิวรีย่อยที่สัมพันธ์กันอาจส่งผลต่อประสิทธิภาพการทํางานเมื่อปรับให้เหมาะสมไม่ถูกต้อง เนื่องจากคิวรีย่อยจะดําเนินการหนึ่งครั้งสําหรับแต่ละแถวในคิวรีภายนอกคิวรีที่สัมพันธ์กันที่ออกแบบมาไม่ดีอาจส่งผลให้มีการดําเนินการคิวรีย่อยหลายพันหรือหลายล้านรายการบนตารางขนาดใหญ่

ปฏิบัติตามแนวทางเหล่านี้เพื่อเพิ่มประสิทธิภาพการสืบค้นย่อยที่สัมพันธ์กัน:

• สร้างดัชนีบนคอลัมน์สหสัมพันธ์: ตรวจสอบให้แน่ใจว่าคอลัมน์ที่อ้างอิงในส่วนคําสั่งของ WHERE แบบสอบถามย่อยที่เชื่อมโยงกลับไปยังแบบสอบถามภายนอกได้รับการจัดทําดัชนี ตัวอย่างเช่น ถ้าคิวรีย่อยของคุณกรอง ProductCategoryIDเป็น ดัชนีในคอลัมน์นั้นจะช่วยให้ฐานข้อมูลค้นหาแถวที่ตรงกันได้อย่างรวดเร็วแทนที่จะสแกนทั้งตารางสําหรับแถวด้านนอกแต่ละแถว

• รวมคอลัมน์เพิ่มเติมในดัชนี: หากคําค้นหาย่อยกรองหรือรวมในคอลัมน์อื่นๆ ด้วย ให้พิจารณาดัชนีแบบรวม ดัชนีรองรับ (ProductCategoryID, ListPrice) ทั้งการค้นหาสหสัมพันธ์และการกรองหรือการรวมตามราคาในการค้นหาดัชนีเดียว

• ประเมินแนวทางอื่น: การสืบค้นย่อยที่สัมพันธ์กันจํานวนมากสามารถเขียนใหม่เป็นการรวมหรือฟังก์ชันหน้าต่างที่มีประสิทธิภาพดีกว่า ถ้าคุณกําลังค้นหาค่าสูงสุดต่อกลุ่ม ฟังก์ชันหน้าต่างที่มี ROW_NUMBER() มักจะมีประสิทธิภาพดีกว่าแบบสอบถามย่อยที่สัมพันธ์กันซึ่งเลือก MAX() สําหรับแต่ละแถว

• ตรวจสอบแผนการดําเนินการ: ใช้ SET STATISTICS IO ON และตรวจสอบแผนการดําเนินการจริงเพื่อทําความเข้าใจว่าเครื่องมือเพิ่มประสิทธิภาพประมวลผลแบบสอบถามย่อยที่สัมพันธ์กันของคุณอย่างไร เครื่องมือเพิ่มประสิทธิภาพอาจแปลงเป็นการรวมภายใน หรืออาจดําเนินการทีละแถวตามที่เขียนไว้

• ทดสอบด้วยปริมาณข้อมูลที่สมจริง: คิวรีย่อยที่สัมพันธ์กันซึ่งทํางานได้ดีในชุดข้อมูลการทดสอบขนาดเล็กอาจช้าลงด้วยตารางขนาดการผลิต เปรียบเทียบกับข้อมูลที่เป็นตัวแทนเสมอก่อนปรับใช้กับการผลิต

สําหรับข้อมูลเพิ่มเติมเกี่ยวกับคิวรีย่อย โปรดดู คิวรีย่อย (Transact-SQL) และ มีอยู่ (Transact-SQL)