ฉันจะสร้างคำสั่ง SQL เพื่อค้นหาบันทึกที่ไม่เชื่อมโยงได้อย่างไร

ฉันมีสองตารางดังนี้:

tblCountry (countryID, countryCode)

tblProjectCountry(ProjectID, countryID)

ตาราง tblCountry คือรายชื่อประเทศทั้งหมดพร้อมรหัส และตาราง tblProjectCountry เชื่อมโยงบางประเทศกับโครงการบางโครงการ ฉันต้องการคำสั่ง SQL ที่ให้รายชื่อประเทศพร้อมรหัสประเทศที่ไม่มีบันทึกที่เกี่ยวข้องในตาราง tblProjectCountry จนถึงตอนนี้ฉันมาถึงที่นี่:

SELECT     tblCountry.countryID, tblCountry.countryCode
FROM         tblProjectCountry INNER JOIN
                      tblCountry ON tblProjectCountry.countryID = tblCountry.countryID
WHERE     (SELECT     COUNT(ProjectID)
                         FROM         tblProjectCountry 
                         WHERE     (ProjectID = 1) AND (countryID = tblCountry.countryID)) = 0

ข้อความข้างต้นแยกวิเคราะห์ว่าถูกต้องแต่ไม่ได้ให้ผลลัพธ์ที่ตรงกับที่ฉันต้องการ ใครสามารถช่วยได้บ้าง?


sql inner-join exists
person William Calleja    schedule 22.03.2010    source แหล่งที่มา

คำตอบ (4)


arrow_upward
3
arrow_downward

มันใช้ได้ไหม?

SELECT countryID, countryCode 
  FROM tblCountry 
  WHERE countryID NOT IN ( SELECT countryID FROM tblProjectCountry )
person tim_yates    schedule 22.03.2010
comment
แม้ว่ามันจะได้ผล แต่ฉันขอบอกว่านี่ไม่ใช่คำตอบที่ถูกต้องที่สุด เห็นได้ชัดว่าคุณไม่ควรใช้ IN In SQL To JOIN กับตารางอื่นตาม sqlservercode.blogspot.com/2007/04/ คำตอบของฉันแสดงวิธีใช้ EXISTS แทน - person rohancragg; 22.03.2010

arrow_upward
3
arrow_downward

ทางเลือกอื่น:

SELECT outerTbl.countryID, outerTbl.countryCode 
    FROM tblCountry AS outerTbl
    WHERE NOT EXISTS 
        (
            SELECT countryID FROM tblProjectCountry WHERE countryID = outerTbl.countryID
        )

สิ่งนี้ใช้สิ่งที่เรียกว่า แบบสอบถามย่อยที่สัมพันธ์กัน

โปรดทราบว่าฉันยังใช้ คีย์เวิร์ด EXISTS (ดูเพิ่มเติม)

บน SQL Server โดยทั่วไปไม่มีอยู่ คิดว่ามีประสิทธิภาพมากกว่า ใน ของคุณ ระยะทางอาจแตกต่างกันไป

person rohancragg    schedule 22.03.2010
comment
ภายใน SELECT countryID FROM ... สามารถแทนที่ด้วย SELECT TOP 1 1 FROM... หลักฐานการดำรงอยู่ก็เพียงพอแล้ว - person Grzegorz Gierlik; 22.03.2010
comment
ขอบคุณ ฉันไม่เคยคิดถึงเรื่องนั้นเลย แม้ว่าฉันจะคิดเสมอว่าการเลือกสิ่งที่ไม่เคย 'ใช้' ก็ไม่มีประโยชน์ - person rohancragg; 23.03.2010

arrow_upward
1
arrow_downward

มีอย่างน้อยสองวิธีในการค้นหาเรกคอร์ดที่ไม่เกี่ยวข้อง

1. การใช้ LEFT JOIN

SELECT DISTINCT -- each country only once
  tblCountry.countryID,
  tblCountry.tblCountry 
FROM
  tblCountry 
  LEFT JOIN
    tblProjectCountry
  ON
    tblProjectCountry.countryID = tblCountry.countryID
WHERE
  tblProjectCountry.ProjectID IS NULL -- get only records with no pair in projects table
ORDER BY
  tblCountry.countryID

ตามที่ erikkallen กล่าวถึงสิ่งนี้อาจทำงานได้ไม่ดี

2. การใช้ NOT EXISTS

เวอร์ชันต่างๆ ของการใช้ NOT EXISTS หรือ IN ได้รับการแนะนำโดย rohancragg และคนอื่นๆ:

SELECT
  tblCountry.countryID,
  tblCountry.tblCountry 
FROM
  tblCountry 
WHERE
  -- get only records with no pair in projects table
  NOT EXISTS (SELECT TOP 1 1 FROM tblProjectCountry WHERE tblProjectCountry.countryID = tblCountry.countryID) 
ORDER BY
  tblCountry.countryID

ขึ้นอยู่กับ DBMS ของคุณและขนาดของประเทศและตารางโครงการ ทั้งสองเวอร์ชันจะทำงานได้ดีกว่า

ในการทดสอบ MS SQL 2005 ของฉัน ไม่มีความแตกต่างอย่างมีนัยสำคัญระหว่างแบบสอบถามแรกและที่สองสำหรับตารางที่มี ~250 ประเทศและ ~5,000 โครงการ อย่างไรก็ตาม บนโต๊ะที่มี โครงการ เวอร์ชันที่สองมากกว่า 3 ล้านรายการ (โดยใช้ NOT EXISTS) ทำงานได้ดีกว่ามาก

เช่นเดียวกับทุกครั้ง มันคุ้มค่าที่จะตรวจสอบทั้งสองเวอร์ชัน

person Grzegorz Gierlik    schedule 22.03.2010
comment
เซิร์ฟเวอร์ SQL จะไม่รู้จักสิ่งนี้ว่าเป็นการต่อต้านการเข้าร่วม ดังนั้นมันจะถูกบังคับให้ทำการรวมตัวกรองด้านซ้าย + ซึ่งอาจดีกว่าหรืออาจจะไม่ดีกว่า แต่อาจจะแย่กว่านั้นมาก - person erikkallen; 22.03.2010
comment
จริง. ฉันได้ตรวจสอบแล้วว่าใน MS SQL 2005 พร้อมตารางที่มี 253 ประเทศเข้าร่วมกับตารางที่มี ~ 5k แถวและมากกว่า 3 ล้านแถว ในกรณีของตารางที่มีมากกว่า 3M แถว LEFT JOIN จะช้ากว่ามาก อย่างไรก็ตาม บนโต๊ะที่มี 5,000 แถว ค่าใช้จ่ายใกล้เคียงกับรุ่นที่มี NOT EXISTS (SELECT TOP 1 1 FROM...) แนะนำโดย @rohancragg. - person Grzegorz Gierlik; 22.03.2010

arrow_upward
0
arrow_downward

SELECT ... ID ที่ไม่อยู่ใน (SELECT ... )

person Axarydax    schedule 22.03.2010
comment
เห็นได้ชัดว่าคุณไม่ควรใช้ IN In SQL To JOIN กับตารางอื่นตาม sqlservercode.blogspot.com/2007/04/ - person rohancragg; 22.03.2010
comment
มันไร้สาระ ฉันไม่ควรใช้ฟีเจอร์ภาษาบางอย่างเพราะฉันอาจทำผิดพลาดเชิงตรรกะได้! - person Axarydax; 22.03.2010
comment
คุณไม่ควรทำอะไรเลยเพราะฉันเห็นโพสต์ในบล็อกที่บอกว่าคุณไม่ควร - person erikkallen; 22.03.2010