Operator SET - EXCEPT dan INTERSECT (Transact-SQL)
Berlaku untuk:
SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
Mengembalikan baris yang berbeda dengan membandingkan hasil dua kueri.
EXCEPT mengembalikan baris yang berbeda dari kueri input kiri yang tidak dihasilkan oleh kueri input kanan.
INTERSECT mengembalikan baris berbeda yang merupakan output oleh operator kueri input kiri dan kanan.
Untuk menggabungkan kumpulan hasil dari dua kueri yang menggunakan EXCEPT atau INTERSECT, aturan dasarnya adalah:
Angka dan urutan kolom harus sama dalam semua kueri.
Tipe data harus kompatibel.
Sintaks
{ <query_specification> | ( <query_expression> ) }
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }
Catatan
Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 dan yang lebih lama, lihat Dokumentasi versi sebelumnya.
Argumen
<> query_specification | (<query_expression>)
Adalah spesifikasi kueri atau ekspresi kueri yang mengembalikan data untuk dibandingkan dengan data dari spesifikasi kueri atau ekspresi kueri lain. Definisi kolom yang merupakan bagian dari operasi EXCEPT atau INTERSECT tidak harus sama. Tapi, mereka harus sebanding melalui konversi implisit. Saat jenis data berbeda, aturan untuk prioritas jenis data menentukan jenis data yang dijalankan untuk perbandingan.
Hasilnya didasarkan pada aturan yang sama untuk menggabungkan ekspresi ketika jenisnya sama tetapi berbeda dalam presisi, skala, atau panjang. Untuk informasi selengkapnya, lihat Presisi, Skala, dan Panjang (Transact-SQL).
Spesifikasi atau ekspresi kueri tidak dapat mengembalikan kolom tipe pengguna xml, teks, ntext, gambar, atau nonbinary CLR karena tipe data ini tidak sebanding.
EXCEPT
Mengembalikan nilai yang berbeda dari kueri sebelah kiri operator EXCEPT. Nilai-nilai tersebut mengembalikan selama kueri yang tepat juga tidak mengembalikan nilai-nilai tersebut.
INTERSECT
Mengembalikan nilai berbeda yang dikembalikan oleh kueri di sisi kiri dan kanan operator INTERSECT.
Keterangan
Jenis data kolom yang sebanding dikembalikan oleh kueri ke kiri dan kanan operator EXCEPT atau INTERSECT. Jenis data ini dapat mencakup jenis data karakter dengan kolamen yang berbeda. Ketika mereka melakukannya, perbandingan yang diperlukan dijalankan sesuai dengan aturan kolase yang diutamakan. Jika Anda tidak dapat menjalankan konversi ini, Mesin Database SQL Server mengembalikan kesalahan.
Saat membandingkan nilai kolom untuk menentukan baris DISTINCT, dua nilai NULL dianggap sama.
EXCEPT dan INTERSECT mengembalikan nama kolom tataan hasil yang sama dengan nama kolom yang dikembalikan kueri di sisi kiri operator.
Nama kolom atau alias dalam klausa ORDER BY harus mereferensikan nama kolom yang dikembalikan oleh kueri sisi kiri.
Nullability kolom apa pun dalam tataan hasil yang dikembalikan oleh EXCEPT atau INTERSECT sama dengan nullability kolom terkait yang dikembalikan oleh kueri di sisi kiri operator.
Jika EXCEPT atau INTERSECT digunakan bersama dengan operator lain dalam ekspresi, maka akan dievaluasi dalam konteks prioritas berikut:
Ekspresi dalam tanda kurung
Operator INTERSECT
EXCEPT dan UNION dievaluasi dari kiri ke kanan berdasarkan posisinya dalam ekspresi
Anda dapat menggunakan EXCEPT atau INTERSECT untuk membandingkan lebih dari dua set kueri. Saat Anda melakukannya, konversi jenis data ditentukan dengan membandingkan dua kueri pada satu waktu, dan mengikuti aturan evaluasi ekspresi yang disebutkan sebelumnya.
EXCEPT dan INTERSECT tidak dapat digunakan dalam definisi tampilan partisi terdistribusi, pemberitahuan kueri.
EXCEPT dan INTERSECT dapat digunakan dalam kueri terdistribusi, tetapi hanya dijalankan di server lokal dan tidak didorong ke server yang ditautkan. Dengan demikian, menggunakan EXCEPT dan INTERSECT dalam kueri terdistribusi dapat memengaruhi performa.
Anda dapat menggunakan kursor cepat maju saja dan statis dalam tataan hasil saat digunakan dengan operasi EXCEPT atau INTERSECT. Anda juga dapat menggunakan kursor berbasis keyset atau dinamis bersama dengan operasi EXCEPT atau INTERSECT. Saat Anda melakukannya, kursor kumpulan hasil operasi dikonversi ke kursor statis.
Saat operasi EXCEPT ditampilkan dengan menggunakan fitur Graphical Showplan di SQL Server Management Studio, operasi muncul sebagai anti gabungan semi kiri, dan operasi INTERSECT muncul sebagai gabungan semi kiri.
Contoh
Contoh berikut menunjukkan penggunaan INTERSECT operator dan EXCEPT . Kueri pertama mengembalikan semua nilai dari Production.Product tabel untuk perbandingan dengan hasil dengan INTERSECT dan EXCEPT.
-- Uses AdventureWorks
SELECT ProductID
FROM Production.Product ;
--Result: 504 Rows
Kueri berikut mengembalikan nilai berbeda yang dikembalikan oleh kueri di sisi INTERSECT kiri dan kanan operator.
-- Uses AdventureWorks
SELECT ProductID
FROM Production.Product
INTERSECT
SELECT ProductID
FROM Production.WorkOrder ;
--Result: 238 Rows (products that have work orders)
Kueri berikut mengembalikan nilai yang berbeda dari kiri kueri EXCEPT operator yang juga tidak ditemukan di kueri kanan.
-- Uses AdventureWorks
SELECT ProductID
FROM Production.Product
EXCEPT
SELECT ProductID
FROM Production.WorkOrder ;
--Result: 266 Rows (products without work orders)
Kueri berikut mengembalikan nilai yang berbeda dari kiri kueri EXCEPT operator yang juga tidak ditemukan di kueri kanan. Tabel dibalik dari contoh sebelumnya.
-- Uses AdventureWorks
SELECT ProductID
FROM Production.WorkOrder
EXCEPT
SELECT ProductID
FROM Production.Product ;
--Result: 0 Rows (work orders without products)
Contoh: Azure Synapse Analytics dan Analytics Platform System (PDW)
Contoh berikut menunjukkan cara menggunakan INTERSECT operator dan EXCEPT . Kueri pertama mengembalikan semua nilai dari FactInternetSales tabel untuk perbandingan dengan hasil dengan INTERSECT dan EXCEPT.
-- Uses AdventureWorks
SELECT CustomerKey
FROM FactInternetSales;
--Result: 60398 Rows
Kueri berikut mengembalikan nilai berbeda yang dikembalikan oleh kueri di sisi INTERSECT kiri dan kanan operator.
-- Uses AdventureWorks
SELECT CustomerKey
FROM FactInternetSales
INTERSECT
SELECT CustomerKey
FROM DimCustomer
WHERE DimCustomer.Gender = 'F'
ORDER BY CustomerKey;
--Result: 9133 Rows (Sales to customers that are female.)
Kueri berikut mengembalikan nilai yang berbeda dari kiri kueri EXCEPT operator yang juga tidak ditemukan di kueri kanan.
-- Uses AdventureWorks
SELECT CustomerKey
FROM FactInternetSales
EXCEPT
SELECT CustomerKey
FROM DimCustomer
WHERE DimCustomer.Gender = 'F'
ORDER BY CustomerKey;
--Result: 9351 Rows (Sales to customers that are not female.)