Bagaimana cara membuat pernyataan SQL yang menemukan catatan yang tidak terkait?

Saya memiliki dua tabel sebagai berikut:

tblCountry (countryID, countryCode)

tblProjectCountry(ProjectID, countryID)

Tabel tblCountry adalah daftar semua negara beserta kodenya dan tabel tblProjectCountry mengaitkan negara tertentu dengan proyek tertentu. Saya memerlukan pernyataan SQL yang memberi saya daftar negara dengan kode negaranya yang TIDAK memiliki catatan terkait di tabel tblProjectCountry. sejauh ini saya sampai di sini:

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

Pernyataan di atas dianggap benar tetapi tidak memberikan hasil persis yang saya cari. Adakah yang bisa membantu?


sql inner-join exists
person William Calleja    schedule 22.03.2010    source sumber

Jawaban (4)


arrow_upward
3
arrow_downward

Apakah ini berhasil?

SELECT countryID, countryCode 
  FROM tblCountry 
  WHERE countryID NOT IN ( SELECT countryID FROM tblProjectCountry )
person tim_yates    schedule 22.03.2010
comment
Meskipun berhasil, menurut saya ini bukanlah jawaban yang paling benar. Tampaknya, Anda tidak boleh menggunakan IN In SQL To JOIN dengan tabel lain menurut sqlservercode.blogspot.com/2007/04/ Jawaban saya menunjukkan cara menggunakan EXISTS. - person rohancragg; 22.03.2010

arrow_upward
3
arrow_downward

Alternatif lain:

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

Ini menggunakan apa yang disebut subkueri berkorelasi

Perhatikan bahwa saya juga menggunakan kata kunci EXISTS (lihat juga)

Di SQL Server, NOT EXISTS umumnya dianggap lebih berperforma. Di RDMS lain jarak tempuh mungkin berbeda.

person rohancragg    schedule 22.03.2010
comment
Bagian dalam SELECT countryID FROM ... bisa diganti dengan SELECT TOP 1 1 FROM.... Bukti keberadaannya cukup baik. - person Grzegorz Gierlik; 22.03.2010
comment
terima kasih, saya tidak pernah memikirkan hal itu, meskipun saya selalu berpikir tidak ada gunanya memilih sesuatu yang tidak pernah 'dipakai' - person rohancragg; 23.03.2010

arrow_upward
1
arrow_downward

Setidaknya ada dua cara untuk menemukan catatan yang tidak terkait.

1. Menggunakan 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

Seperti yang disebutkan erikkallen, ini bisa berkinerja tidak baik.

2. Menggunakan NOT EXISTS

Berbagai versi penggunaan NOT EXISTS atau IN disarankan oleh rohancragg dan lainnya:

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

Tergantung pada DBMS Anda dan ukuran negara serta tabel proyek, kedua versi dapat bekerja lebih baik.

Dalam pengujian saya pada MS SQL 2005 tidak ada perbedaan yang signifikan antara kueri pertama dan kedua untuk tabel dengan ~250 negara dan ~5000 proyek. Namun pada tabel dengan lebih dari 3M proyek versi kedua (menggunakan NOT EXISTS) berkinerja jauh lebih baik.

Jadi seperti biasa, ada baiknya untuk memeriksa kedua versi.

person Grzegorz Gierlik    schedule 22.03.2010
comment
Server SQL tidak akan mengenali ini sebagai anti-gabung sehingga akan dipaksa untuk melakukan filter gabung + kiri, yang mungkin lebih baik atau tidak, tetapi mungkin jauh lebih buruk. - person erikkallen; 22.03.2010
comment
BENAR. Saya telah memeriksanya di MS SQL 2005 dengan tabel dengan 253 negara BERGABUNG ke tabel dengan ~5k baris dan lebih dari 3M baris. Dalam kasus tabel dengan lebih dari 3 juta baris LEFT JOIN jauh lebih lambat. Namun pada tabel dengan 5k baris biayanya mirip dengan versi dengan NOT EXISTS (SELECT TOP 1 1 FROM...) yang disarankan oleh @rohancragg. - person Grzegorz Gierlik; 22.03.2010

arrow_upward
0
arrow_downward

PILIH... DIMANA ID TIDAK DI DALAM (PILIH... )

person Axarydax    schedule 22.03.2010
comment
rupanya, Anda tidak boleh menggunakan IN In SQL Untuk BERGABUNG dengan tabel lain menurut sqlservercode.blogspot.com/2007/04/ - person rohancragg; 22.03.2010
comment
ini konyol, saya tidak boleh menggunakan beberapa fitur bahasa karena saya mungkin melakukan kesalahan logis?! - person Axarydax; 22.03.2010
comment
Anda tidak boleh melakukan apa pun karena saya melihat postingan blog yang mengatakan Anda tidak boleh melakukan apa pun. - person erikkallen; 22.03.2010