Operator SET - EXCEPT dan INTERSECT (Transact-SQL)

Artikel
07/05/2024

Berlaku untuk: Titik akhir analitik SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Platform System (PDW) SQL di Microsoft Fabric Warehouse di Microsoft Fabric

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.

Konvensi sintaks transact-SQL

Sintaks

{ <query_specification> | ( <query_expression> ) }   
{ EXCEPT | INTERSECT }  
{ <query_specification> | ( <query_expression> ) }

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. Ketika 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 jenis xml, teks, ntext, gambar, atau tipe yang ditentukan pengguna CLR nonbiner karena tipe data ini tidak sebanding.

KECUALI
Mengembalikan nilai yang berbeda dari kueri kiri operator EXCEPT. Nilai-nilai tersebut mengembalikan selama kueri yang tepat juga tidak mengembalikan nilai tersebut.

PERPOTONGAN
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 klausul 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, itu 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 sekaligus, dan mengikuti aturan evaluasi ekspresi yang disebutkan sebelumnya.

EXCEPT dan INTERSECT tidak dapat digunakan dalam definisi tampilan terdistribusi yang dipartisi, pemberitahuan kueri.

EXCEPT dan INTERSECT dapat digunakan dalam kueri terdistribusi, tetapi hanya dijalankan di server lokal dan tidak didorong ke server tertaut. Dengan demikian, menggunakan EXCEPT dan INTERSECT dalam kueri terdistribusi dapat memengaruhi performa.

Anda dapat menggunakan kursor hanya maju dan statis yang cepat 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 tabel untuk perbandingan Production.Product 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 and Analytics Platform System (PDW)

Contoh berikut menunjukkan cara menggunakan INTERSECT operator dan EXCEPT . Kueri pertama mengembalikan semua nilai dari tabel untuk perbandingan FactInternetSales dengan hasil dengan INTERSECT dan EXCEPT.

-- Uses AdventureWorks  
  
SELECT CustomerKey   
FROM FactInternetSales;  
--Result: 60398 Rows