Cara Menemukan Duplikat di SQL: Panduan Lengkap dengan Contoh Interaktif

Menemukan duplikat di SQL bisa menjadi tugas yang membosankan, tetapi ini adalah keterampilan penting bagi siapa pun yang bekerja dengan database. Duplikat dapat menyebabkan kesalahan, ketidaksesuaian, dan inkonsistensi dalam data Anda, yang mengakibatkan hasil yang salah dan performa yang buruk. Oleh karena itu, sangat penting untuk mengidentifikasi dan menghapus duplikat dari tabel Anda untuk memastikan integritas dan akurasi data.

SQL menyediakan beberapa cara untuk menemukan duplikat dalam data Anda, tergantung pada kebutuhan Anda dan struktur tabel Anda. Anda dapat menggunakan klausa GROUP BY dan HAVING untuk mengelompokkan record berdasarkan kolom tertentu dan memfilter duplikat berdasarkan hitungan atau kondisi. Alternatifnya, Anda dapat menggunakan kata kunci DISTINCT untuk memilih hanya nilai unik dan membandingkannya dengan tabel asli untuk mengidentifikasi duplikat. Ada juga fungsi dan operator khusus, seperti COUNT(), EXISTS, dan JOIN, yang dapat membantu Anda menemukan duplikat dalam skenario yang lebih kompleks.

Sepanjang panduan ini, Anda akan menemukan latihan SQL interaktif yang memungkinkan Anda berlatih menemukan duplikat di browser Anda — tidak perlu setup database. Tulis query, dapatkan feedback instan, dan lihat apakah hasil Anda sesuai dengan output yang diharapkan.

Dalam artikel ini, kami akan mengeksplorasi berbagai teknik untuk menemukan duplikat di SQL, dari query sederhana hingga metode lanjutan, dan memberikan contoh serta praktik terbaik untuk membantu Anda menguasai keterampilan ini. Baik Anda seorang pemula maupun pengembang SQL berpengalaman, panduan ini akan membantu Anda meningkatkan kualitas dan efisiensi data Anda dengan mendeteksi dan menghilangkan duplikat dari tabel Anda. Untuk berlatih query ini, pertimbangkan untuk menggunakan editor SQL modern seperti editor SQL Beekeeper Studio, yang menyediakan syntax highlighting dan fitur autocomplete.

Menemukan Duplikat Menggunakan Klausa GROUP BY dan HAVING

Salah satu cara untuk menemukan nilai duplikat di SQL adalah dengan menggunakan klausa GROUP BY dan HAVING. Klausa ini memungkinkan Anda mengelompokkan baris yang memiliki nilai yang sama di satu atau lebih kolom dan kemudian memfilter grup berdasarkan kriteria tertentu. Begini cara kerjanya:

1. Mulai dengan memilih kolom yang ingin Anda periksa duplikatnya menggunakan pernyataan SELECT.
2. Gunakan klausa GROUP BY untuk mengelompokkan baris berdasarkan kolom yang dipilih.
3. Gunakan fungsi COUNT dalam klausa HAVING untuk memfilter grup yang memiliki lebih dari satu baris. Ini adalah grup yang berisi duplikat.

Misalnya, katakanlah Anda memiliki tabel bernama “customers” dengan kolom untuk “name” dan “email”. Anda ingin menemukan semua pelanggan yang mendaftar dengan alamat email yang sama. Begini tampilan query SQL-nya:

SELECT name, email, COUNT(*)
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Query ini mengelompokkan pelanggan berdasarkan alamat email mereka dan kemudian menghitung jumlah pelanggan di setiap grup. Klausa HAVING memfilter grup yang hanya memiliki satu pelanggan, menyisakan hanya grup dengan alamat email duplikat.

Coba sendiri:

[[ error ]]

[[ testData.solution ]]

Penting untuk dicatat bahwa klausa GROUP BY harus mencakup semua kolom yang Anda pilih kecuali yang menggunakan fungsi agregat seperti COUNT. Jika tidak, query akan mengembalikan kesalahan.

Hal lain yang perlu diingat adalah fungsi COUNT menghitung semua baris di setiap grup, bukan hanya yang unik. Jadi jika Anda memiliki beberapa baris dengan nama dan alamat email yang sama, semuanya akan dihitung sebagai duplikat.

Selain menggunakan klausa GROUP BY dan HAVING, Anda juga dapat menggunakan pernyataan SQL lain seperti ORDER BY, WHERE, dan JOIN untuk lebih menyempurnakan pencarian duplikat Anda. Anda juga dapat menggunakan constraint unik untuk mencegah nilai duplikat dimasukkan ke dalam tabel sejak awal.

Secara keseluruhan, menemukan duplikat di SQL dapat menjadi alat yang ampuh untuk membersihkan data yang belum diproses atau mengidentifikasi potensi kesalahan manusia atau bug aplikasi. Dengan menggunakan kriteria pencarian yang tepat dan menghasilkan output hasil dengan cara yang jelas dan ringkas, Anda dapat dengan cepat mengidentifikasi baris atau nilai duplikat dan mengambil tindakan untuk memperbaikinya.

Menggunakan Fungsi COUNT

Salah satu cara untuk menemukan duplikat di SQL adalah dengan menggunakan fungsi COUNT. Fungsi COUNT adalah fungsi agregat yang menghitung jumlah baris dalam tabel yang memenuhi kondisi tertentu. Dengan menggunakan fungsi COUNT, Anda dapat menghitung jumlah kemunculan nilai tertentu dalam kolom dan mengidentifikasi duplikat.

Untuk menggunakan fungsi COUNT untuk menemukan duplikat, Anda perlu mengelompokkan baris berdasarkan kolom yang ingin Anda periksa duplikatnya. Klausa GROUP BY digunakan untuk mengelompokkan baris berdasarkan nilai dalam kolom tertentu. Misalnya, jika Anda ingin menemukan duplikat di kolom “email” tabel “users”, Anda akan mengelompokkan baris berdasarkan kolom “email”.

Berikut adalah contoh query yang menggunakan fungsi COUNT untuk menemukan duplikat di kolom “email” tabel “users”:

SELECT email, COUNT(email) as count
FROM users
GROUP BY email
HAVING COUNT(email) > 1;

Dalam query ini, klausa GROUP BY mengelompokkan baris berdasarkan kolom “email”, dan fungsi COUNT menghitung jumlah kemunculan setiap alamat email. Klausa HAVING memfilter hasil untuk hanya menampilkan alamat email yang memiliki lebih dari satu kemunculan.

Hasil dari query ini akan berupa tabel yang menunjukkan alamat email yang memiliki duplikat dan jumlah kemunculan setiap alamat email. Anda dapat menggunakan informasi ini untuk mengidentifikasi dan menghapus baris duplikat dari tabel.

Menggunakan fungsi COUNT adalah cara yang sederhana dan efektif untuk menemukan duplikat di SQL. Ini memungkinkan Anda dengan cepat mengidentifikasi nilai duplikat dalam kolom dan mengambil tindakan untuk menghapusnya dari tabel.

Menggunakan Klausa INNER JOIN

Salah satu cara paling umum untuk menemukan duplikat di SQL adalah dengan menggunakan klausa INNER JOIN. Klausa ini memungkinkan Anda menggabungkan dua atau lebih tabel berdasarkan kolom yang sama dan mengembalikan hanya baris yang memiliki nilai yang cocok di kedua tabel.

Untuk menggunakan klausa INNER JOIN untuk menemukan duplikat, Anda perlu mengidentifikasi kolom yang berisi data duplikat. Setelah Anda mengidentifikasi kolom-kolom ini, Anda dapat menggunakan klausa INNER JOIN untuk menggabungkan tabel pada kolom-kolom ini.

Berikut adalah contoh cara menggunakan klausa INNER JOIN untuk menemukan duplikat dalam tabel bernama “orders”:

SELECT o1.order_id, o2.order_id
FROM orders o1
INNER JOIN orders o2
ON o1.customer_id = o2.customer_id
AND o1.order_date = o2.order_date
AND o1.order_id <> o2.order_id;

Dalam contoh ini, klausa INNER JOIN digunakan untuk menggabungkan tabel “orders” dengan dirinya sendiri, menggunakan kolom “customer_id” dan “order_date” sebagai kriteria join. Operator “AND” digunakan untuk menentukan bahwa ketiga kolom harus cocok agar baris dikembalikan. Baris terakhir query, “AND o1.order_id <> o2.order_id”, memastikan bahwa query tidak mengembalikan baris di mana kedua nilai “order_id” sama.

Hasil dari query ini akan berupa daftar semua pesanan yang memiliki ID pelanggan dan tanggal pesanan yang sama, tetapi ID pesanan yang berbeda. Ini adalah pesanan duplikat yang perlu diperiksa lebih lanjut.

Berlatih dengan self-join:

[[ error ]]

[[ testData.solution ]]

Menggunakan klausa INNER JOIN dengan cara ini dapat menjadi alat yang ampuh untuk menemukan duplikat di SQL. Namun, penting untuk berhati-hati saat menggunakan metode ini, karena mudah untuk secara tidak sengaja mengembalikan false positive atau melewatkan duplikat yang tersebar di beberapa tabel. Selalu merupakan ide yang baik untuk memeriksa ulang hasil Anda dan menggunakan metode lain, seperti klausa GROUP BY dan HAVING, untuk mengkonfirmasi temuan Anda.

Coba sendiri:

[[ error ]]

[[ testData.solution ]]

Menemukan Pasangan Duplikat Menggunakan Self-Join

Teknik ampuh lainnya untuk menemukan duplikat adalah menggunakan self-join untuk mengidentifikasi pasangan record yang berbagi karakteristik umum. Pendekatan ini sangat berguna ketika Anda ingin menemukan pasangan duplikat yang tepat daripada hanya mengidentifikasi bahwa duplikat ada.

Self-join melibatkan penggabungan tabel dengan dirinya sendiri menggunakan alias yang berbeda. Dengan menggabungkan pada kolom yang mendefinisikan duplikat dan memastikan bahwa Anda tidak mencocokkan record dengan dirinya sendiri, Anda dapat mengidentifikasi pasangan duplikat secara efektif.

Misalnya, jika Anda memiliki tabel transaksi dan ingin menemukan pasangan transaksi dengan ID pelanggan dan jumlah yang sama tetapi ID transaksi yang berbeda, Anda dapat menggunakan self-join seperti ini:

SELECT t1.transaction_id as transaction_id1, 
       t2.transaction_id as transaction_id2, 
       t1.customer_id, 
       t1.amount
FROM transactions t1
INNER JOIN transactions t2 
ON t1.customer_id = t2.customer_id 
AND t1.amount = t2.amount 
AND t1.transaction_id < t2.transaction_id;

Poin-poin penting dalam query ini adalah:

• t1 dan t2 adalah alias untuk tabel yang sama
• Kondisi join menentukan apa yang membuat record menjadi “duplikat” (customer_id dan amount)
• t1.transaction_id < t2.transaction_id memastikan kita hanya mendapatkan setiap pasangan sekali dan tidak mencocokkan record dengan dirinya sendiri

Coba sendiri:

[[ error ]]

[[ testData.solution ]]

Menemukan Duplikat Menggunakan Common Table Expression (CTE)

Salah satu cara untuk menemukan duplikat di SQL adalah dengan menggunakan Common Table Expression (CTE). CTE adalah kumpulan hasil sementara yang didefinisikan dalam cakupan eksekusi pernyataan SELECT, INSERT, UPDATE, DELETE, atau CREATE VIEW tunggal.

Untuk menemukan duplikat menggunakan CTE, seseorang dapat menggunakan fungsi ROW_NUMBER(), yang memberikan nomor urut unik ke setiap baris dalam partisi kumpulan hasil. Klausa PARTITION BY menentukan kolom yang digunakan untuk mendefinisikan partisi, dan klausa ORDER BY menentukan urutan baris dalam setiap partisi.

Berikut adalah contoh penggunaan CTE untuk menemukan duplikat:

WITH CTE AS (
   SELECT column1, column2, column3, ROW_NUMBER() OVER(PARTITION BY column1, column2, column3 ORDER BY column1, column2, column3) AS RowNumber
   FROM table_name
)
SELECT *
FROM CTE
WHERE RowNumber > 1

Dalam contoh ini, CTE didefinisikan dengan kolom yang akan diperiksa duplikatnya. Fungsi ROW_NUMBER() digunakan untuk menghasilkan nomor urut untuk setiap baris dalam partisi kumpulan hasil, di mana partisi didefinisikan oleh kolom yang ditentukan dalam klausa PARTITION BY. Kumpulan hasil kemudian difilter untuk hanya menampilkan baris di mana RowNumber lebih besar dari 1, menunjukkan bahwa ada duplikat.

Penting untuk dicatat bahwa klausa ORDER BY dalam fungsi ROW_NUMBER() harus sesuai dengan klausa ORDER BY dalam pernyataan SELECT utama, jika tidak hasilnya mungkin tidak akurat.

Menggunakan CTE untuk menemukan duplikat dapat sangat berguna di SQL Server 2017, yang memperkenalkan dukungan untuk pemrosesan graph menggunakan Common Table Expression. Ini memungkinkan query yang lebih kompleks yang melibatkan hubungan antar data.

Secara keseluruhan, menggunakan CTE untuk menemukan duplikat di SQL dapat menjadi alat yang ampuh untuk analisis dan manajemen data.

Coba sendiri:

[[ error ]]

[[ testData.solution ]]

Menemukan Duplikat Menggunakan Fungsi Window

Ketika menemukan duplikat di SQL, salah satu cara paling efisien adalah dengan menggunakan fungsi window. Fungsi window adalah alat yang ampuh yang dapat digunakan untuk melakukan perhitungan pada sekumpulan baris yang terkait dengan baris saat ini.

Untuk menemukan duplikat menggunakan fungsi window, Anda dapat menggunakan fungsi ROW_NUMBER() dikombinasikan dengan klausa PARTITION BY. Fungsi ROW_NUMBER() memberikan nomor unik ke setiap baris dalam partisi, dan klausa PARTITION BY mengelompokkan baris ke dalam partisi berdasarkan kolom tertentu atau kumpulan kolom.

Misalnya, anggap Anda memiliki tabel pengguna yang mencakup kolom untuk user_id, username, dan email. Untuk menemukan semua pengguna yang memiliki email duplikat, Anda dapat menggunakan pernyataan select berikut:

SELECT user_id, username, email
FROM (
  SELECT user_id, username, email,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY user_id) AS row_num
  FROM users
) AS subquery
WHERE row_num > 1;

Dalam contoh ini, klausa PARTITION BY mempartisi baris berdasarkan kolom email, dan fungsi ROW_NUMBER() memberikan nomor unik ke setiap baris dalam partisi berdasarkan kolom user_id. Klausa WHERE memfilter semua baris kecuali yang dengan row_num lebih besar dari 1, yang merupakan baris dengan email duplikat.

Berlatih dengan ROW_NUMBER():

[[ error ]]

[[ testData.solution ]]

Penting untuk dicatat bahwa fungsi window dapat digunakan dikombinasikan dengan fungsi SQL lain, seperti pernyataan CASE dan fungsi agregat, untuk membuat query yang lebih kompleks. Misalnya, Anda dapat menggunakan pernyataan CASE untuk mengelompokkan baris ke dalam kategori tertentu, dan kemudian menggunakan fungsi window untuk menemukan duplikat dalam kategori tersebut.

Kesimpulannya, menggunakan fungsi window untuk menemukan duplikat di SQL dapat menjadi metode yang ampuh dan efisien. Dengan menggunakan fungsi ROW_NUMBER() dikombinasikan dengan klausa PARTITION BY, Anda dapat dengan mudah mengelompokkan baris ke dalam partisi dan memberikan nomor unik ke setiap baris dalam partisi. Ini memungkinkan Anda dengan cepat mengidentifikasi dan menghapus baris duplikat dari hasil Anda.

Coba sendiri:

[[ error ]]

[[ testData.solution ]]

Menggunakan Fungsi ROW_NUMBER() dengan Klausa PARTITION BY

Saat mencari duplikat dalam database SQL, alat yang berguna adalah fungsi ROW_NUMBER() dengan klausa PARTITION BY. Fungsi ini memberikan nomor unik ke setiap baris dalam kumpulan hasil, berdasarkan kriteria partisi yang ditentukan.

Klausa PARTITION BY memungkinkan pengguna untuk mengelompokkan baris ke dalam partisi berdasarkan satu atau lebih kolom. Ini berarti bahwa fungsi ROW_NUMBER() akan memberikan nomor unik ke setiap baris dalam setiap partisi, bukan di seluruh kumpulan hasil.

Misalnya, pertimbangkan tabel pesanan pelanggan dengan kolom untuk ID pesanan, ID pelanggan, dan tanggal pesanan. Untuk menemukan pesanan duplikat untuk setiap pelanggan, seseorang dapat menggunakan query berikut:

SELECT order_id, customer_id, order_date,
       ROW_NUMBER() OVER (PARTITION BY customer_id, order_date
                          ORDER BY order_id) AS row_num
FROM orders

Dalam query ini, fungsi ROW_NUMBER() digunakan dengan klausa PARTITION BY untuk mengelompokkan pesanan berdasarkan pelanggan dan tanggal pesanan. Fungsi kemudian memberikan nomor unik ke setiap baris dalam setiap partisi, berdasarkan ID pesanan.

Tabel yang dihasilkan akan memiliki kolom tambahan bernama “row_num” yang berisi nomor baris yang ditetapkan. Duplikat kemudian dapat diidentifikasi dengan memilih baris dengan row_num lebih besar dari 1.

Menggunakan fungsi ROW_NUMBER() dengan klausa PARTITION BY dapat menjadi alat yang ampuh untuk menemukan duplikat dalam database SQL. Dengan mengelompokkan baris ke dalam partisi berdasarkan kriteria tertentu, fungsi dapat memberikan nomor unik ke setiap baris dalam setiap partisi, membuatnya lebih mudah untuk mengidentifikasi duplikat.

Menggunakan Pernyataan CASE dengan Fungsi ROW_NUMBER()

Di SQL, fungsi ROW_NUMBER() digunakan untuk memberikan nomor urut unik ke setiap baris dalam kumpulan hasil. Fungsi ini dapat digunakan bersama dengan pernyataan CASE untuk mengidentifikasi record duplikat dalam tabel.

Pernyataan CASE memungkinkan logika kondisional diterapkan ke setiap baris dalam kumpulan hasil. Dengan memanfaatkan fungsi ROW_NUMBER() dalam pernyataan CASE, jumlah kemunculan setiap record dapat ditentukan.

Misalnya, query SQL berikut dapat digunakan untuk menemukan record duplikat dalam tabel “users” berdasarkan kolom “first_name” dan “last_name”:

SELECT *,
CASE
    WHEN ROW_NUMBER() OVER (PARTITION BY first_name, last_name ORDER BY id) > 1
    THEN 'Duplikat'
    ELSE 'Unik'
END AS duplicate_status
FROM users;

Query di atas mempartisi kumpulan hasil berdasarkan kolom “first_name” dan “last_name” dan mengurutkan baris berdasarkan kolom “id”. Fungsi ROW_NUMBER() memberikan nomor urut unik ke setiap baris dalam setiap partisi.

Pernyataan CASE kemudian memeriksa apakah nilai ROW_NUMBER() lebih besar dari 1. Jika ya, maka baris diidentifikasi sebagai duplikat. Jika tidak, baris diidentifikasi sebagai unik.

Kumpulan hasil akan mencakup kolom tambahan bernama “duplicate_status” yang menampilkan “Duplikat” atau “Unik” untuk setiap baris.

Dalam contoh di atas, baris 1, 2, dan 4 diidentifikasi sebagai unik, sementara baris 3 dan 5 diidentifikasi sebagai duplikat berdasarkan kolom “first_name” dan “last_name”.

Dengan memanfaatkan fungsi ROW_NUMBER() dalam pernyataan CASE, record duplikat dapat dengan mudah diidentifikasi dan dikelola dalam tabel SQL.