SQL Server: Filtering Rentang Data dengan BETWEEN dan IN

Foto oleh Leeloo The First via Pexels

Filtering Rentang Data di SQL Server: Mengoptimalkan Query dengan BETWEEN dan IN

Halo teman-teman Malam Ngoding! Pernahkah Anda berhadapan dengan dataset besar dan perlu menemukan data yang jatuh dalam rentang waktu atau nilai tertentu? Bayangkan Anda memiliki jutaan baris data transaksi atau inventaris, dan tugas Anda adalah mengekstrak informasi yang sangat spesifik, misalnya semua transaksi antara tanggal X dan Y, atau semua produk dari kategori A, B, dan C.

Menulis kondisi WHERE yang kompleks dengan banyak operator AND atau OR untuk skenario semacam ini bisa menjadi mimpi buruk. Selain sulit dibaca, query seperti itu juga rentan terhadap kesalahan dan seringkali kurang efisien. Untungnya, SQL Server menyediakan dua operator powerful yang dirancang khusus untuk kebutuhan filtering semacam ini: BETWEEN dan IN. Keduanya membantu kita menulis query yang lebih bersih, lebih mudah dipahami, dan yang terpenting, seringkali lebih performant.

Mengapa Perlu Filtering Rentang Data?

Dalam aplikasi dunia nyata, kebutuhan untuk memfilter data berdasarkan rentang nilai adalah hal yang sangat umum. Sistem manajemen inventaris perlu menampilkan produk dengan stok antara 10 hingga 50 unit. Sistem perbankan harus melaporkan transaksi yang terjadi dalam kuartal tertentu. Laporan penjualan mungkin memerlukan data untuk produk dengan harga di atas 100 ribu dan di bawah 500 ribu. Tanpa operator yang efisien, pekerjaan ini akan sangat melelahkan dan rawan kesalahan.

Secara tradisional, Anda mungkin akan menulis kondisi seperti WHERE Harga >= 100000 AND Harga <= 500000. Ini berfungsi, tetapi untuk rentang tanggal atau daftar nilai yang lebih panjang, sintaks ini bisa menjadi bertele-tele. Di sinilah BETWEEN dan IN bersinar, menyediakan cara yang lebih ringkas dan semantik untuk menyatakan maksud Anda.

Operator BETWEEN: Memfilter dalam Sebuah Rentang

Membuat Contoh Tabel Produk dan Transaksi

CREATE TABLE Produk (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    NamaProduk VARCHAR(100) NOT NULL,
    Kategori VARCHAR(50) NOT NULL,
    Stok INT NOT NULL,
    Harga DECIMAL(18, 2) NOT NULL
);

INSERT INTO Produk (NamaProduk, Kategori, Stok, Harga) VALUES
('Laptop Gaming', 'Elektronik', 15, 15000000.00),
('Mouse Wireless', 'Elektronik', 80, 250000.00),
('Kemeja Pria', 'Pakaian', 30, 180000.00),
('Celana Jeans', 'Pakaian', 45, 350000.00),
('Susu UHT 1L', 'Makanan', 120, 18000.00),
('Roti Tawar', 'Makanan', 60, 15000.00),
('Buku Resep', 'Buku', 20, 120000.00),
('Headphone ANC', 'Elektronik', 5, 2000000.00),
('Charger HP', 'Aksesoris', 90, 100000.00);

CREATE TABLE Pelanggan (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    NamaPelanggan VARCHAR(100) NOT NULL,
    Kota VARCHAR(50) NOT NULL
);

INSERT INTO Pelanggan (NamaPelanggan, Kota) VALUES
('Andi Santoso', 'Jakarta'),
('Budi Cahyo', 'Surabaya'),
('Citra Dewi', 'Bandung'),
('Dewi Lestari', 'Jakarta'),
('Eka Putra', 'Semarang');

CREATE TABLE Transaksi (
    TransaksiID INT PRIMARY KEY IDENTITY(1,1),
    CustomerID INT NOT NULL,
    ProductID INT NOT NULL,
    TanggalTransaksi DATETIME NOT NULL,
    Jumlah INT NOT NULL,
    TotalHarga DECIMAL(18, 2) NOT NULL
);

INSERT INTO Transaksi (CustomerID, ProductID, TanggalTransaksi, Jumlah, TotalHarga) VALUES
(1, 1, '2023-01-05 10:30:00', 1, 15000000.00),
(2, 5, '2023-01-15 14:00:00', 3, 54000.00),
(1, 3, '2023-01-20 09:15:00', 2, 360000.00),
(3, 7, '2023-02-01 11:00:00', 1, 120000.00),
(4, 2, '2023-01-25 16:45:00', 1, 250000.00),
(2, 6, '2023-02-10 13:00:00', 5, 75000.00),
(5, 8, '2023-01-31 23:00:00', 1, 2000000.00);

Operator BETWEEN digunakan untuk memfilter nilai yang berada dalam sebuah rentang inklusif. Artinya, nilai batas bawah (lower bound) dan nilai batas atas (upper bound) akan ikut disertakan dalam hasil filter. Ini sangat berguna untuk kolom numerik, tanggal, bahkan string.

Sintaks dasarnya adalah:

SELECT column_list
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Contoh Penggunaan BETWEEN

1. Filtering Rentang Numerik

Misalnya, kita ingin mencari semua produk yang stoknya antara 10 hingga 50 unit dari tabel Produk.

Skenario industri: Menemukan barang di gudang yang perlu direstock, atau barang yang stoknya di ambang batas minimum/maksimum.

Contoh Query: Produk dengan Stok di Rentang Tertentu

2. Filtering Rentang Tanggal

Ini adalah salah satu penggunaan BETWEEN yang paling populer. Misalnya, mencari semua transaksi yang terjadi di bulan Januari 2023.

Skenario industri: Membuat laporan bulanan, menganalisis performa kampanye dalam periode tertentu.

Contoh Query: Transaksi dalam Rentang Tanggal

Penting untuk diperhatikan ketika bekerja dengan tanggal dan waktu: Operator BETWEEN bersifat inklusif. Jika kolom TanggalTransaksi memiliki komponen waktu (misalnya, DATETIME atau DATETIME2), '2023-01-31' akan diinterpretasikan sebagai '2023-01-31 00:00:00.000'. Ini berarti setiap transaksi yang terjadi pada tanggal 31 Januari setelah tengah malam tidak akan termasuk. Untuk mendapatkan seluruh hari, Anda bisa menggunakan BETWEEN '2023-01-01' AND '2023-01-31 23:59:59.997' (untuk DATETIME) atau pendekatan yang lebih robust seperti:

SELECT * FROM Transaksi
WHERE TanggalTransaksi >= '2023-01-01'
  AND TanggalTransaksi < '2023-02-01';

Pendekatan >= start_date AND < end_date_plus_one_day seringkali lebih aman dan mudah dipahami untuk filtering tanggal penuh, terutama saat berurusan dengan tipe data yang mengandung waktu.

3. Filtering Rentang String

Meskipun jarang, BETWEEN juga bisa digunakan untuk string, berdasarkan urutan leksikografis (alfabetis). Misalnya, mencari nama produk yang dimulai dari 'A' hingga 'F'.

Skenario industri: Mengelompokkan data berdasarkan inisial, melakukan pencarian 'fuzzy' yang sederhana.

Contoh Query: Nama Produk dalam Rentang Alfabetis

Ini akan mencakup semua nama produk yang dimulai dengan A, B, C, D, E, dan F. Perlu diingat bahwa ini juga inklusif, jadi 'F' akan mencakup 'Fzzy' tetapi tidak 'Faang'. Sebagaimana dibahas pada artikel sebelumnya mengenai Pencarian Pola dengan LIKE, LIKE biasanya lebih fleksibel untuk filtering string berbasis pola.

Operator IN: Memilih dari Daftar Nilai Spesifik

Operator IN digunakan untuk memfilter baris di mana suatu kolom memiliki nilai yang cocok dengan salah satu nilai dalam daftar yang diberikan. Daftar nilai ini bisa berupa daftar eksplisit yang Anda tulis sendiri, atau hasil dari sebuah subquery.

Sintaks dasarnya adalah:

SELECT column_list
FROM table_name
WHERE column_name IN (value1, value2, value3, ...);

Contoh Penggunaan IN

1. Filtering dengan Daftar Nilai Eksplisit

Misalnya, kita ingin mencari produk dari kategori 'Elektronik', 'Pakaian', atau 'Makanan'.

Skenario industri: Menarik data untuk segmen pasar tertentu, memfilter data untuk promosi yang menargetkan beberapa kategori produk.

Contoh Query: Produk dari Kategori Spesifik

Ini jauh lebih rapi daripada WHERE Kategori = 'Elektronik' OR Kategori = 'Pakaian' OR Kategori = 'Makanan'.

2. Filtering dengan Subquery

Salah satu kekuatan IN adalah kemampuannya untuk bekerja dengan subquery. Ini memungkinkan Anda untuk memfilter data berdasarkan hasil dari query lain.

Skenario industri: Menemukan karyawan yang bekerja di departemen yang memiliki lebih dari 10 orang, atau produk yang dipesan oleh pelanggan tertentu.

Misalnya, kita ingin mencari semua transaksi yang dilakukan oleh pelanggan yang tinggal di kota 'Jakarta' atau 'Surabaya'. Anggap kita memiliki tabel Pelanggan dengan kolom CustomerID dan Kota.

Contoh Query: Transaksi dari Pelanggan di Kota Tertentu

Subquery di dalam IN akan dieksekusi terlebih dahulu untuk mendapatkan daftar CustomerID, lalu daftar tersebut digunakan oleh query utama untuk memfilter tabel Transaksi. Ini adalah pola yang sangat umum dan powerful dalam SQL.

BETWEEN vs. IN vs. Rantai AND/OR: Kapan Menggunakan yang Mana?

Contoh Query: Produk dengan Stok di Rentang Tertentu

SELECT ProductID, NamaProduk, Stok
FROM Produk
WHERE Stok BETWEEN 10 AND 50;

Memilih antara BETWEEN, IN, atau rantai AND/OR bergantung pada konteks dan kebutuhan spesifik Anda:

BETWEEN: Pilihan terbaik untuk rentang kontinu (angka, tanggal, alfabet) di mana batas bawah dan atas inklusif. Sangat ringkas dan mudah dibaca.
IN: Ideal untuk daftar nilai diskrit yang spesifik. Lebih baik daripada banyak OR karena lebih rapi dan SQL Server seringkali mengoptimalkannya dengan lebih baik (misalnya, menjadi semacam hash join atau merge join jika daftar cukup besar dan di-indeks). Ini juga wajib ketika daftar nilai berasal dari hasil subquery.
Rantai AND/OR: Digunakan ketika kondisi filtering tidak dapat diwakili secara sederhana oleh BETWEEN atau IN. Misalnya, kombinasi rentang dan nilai spesifik, atau kondisi yang lebih kompleks.

Dalam hal performa, SQL Server Query Optimizer biasanya cerdas dalam menangani ketiga konstruksi ini, terutama jika kolom yang difilter diindeks. Namun, ada beberapa nuansa:

BETWEEN umumnya sangat efisien untuk kolom terindeks karena dapat memanfaatkan range scan atau index seek.
IN dengan daftar eksplisit yang kecil juga sangat cepat. Untuk daftar yang sangat panjang (ratusan atau ribuan nilai), performa bisa menurun karena optimizer harus membandingkan setiap nilai. Dalam kasus ini, pertimbangkan untuk memasukkan daftar nilai ke dalam tabel temporer atau table variable, lalu melakukan INNER JOIN. Ini seringkali lebih efisien.
IN dengan subquery: Performa sangat tergantung pada kompleksitas subquery. Jika subquery menghasilkan daftar yang kecil dan tidak berkorelasi dengan query luar, performanya akan baik. Namun, subquery yang berkorelasi (yang dieksekusi untuk setiap baris dari query luar) bisa menjadi bottleneck yang signifikan.

Tips dan Best Practices

Indeks Adalah Kunci: Selalu pastikan kolom yang Anda filter dengan BETWEEN atau IN (terutama jika daftar IN besar atau menggunakan subquery) memiliki indeks yang tepat. Ini adalah faktor terbesar dalam performa query.
Pahami Inklusivitas BETWEEN: Ingat bahwa BETWEEN selalu inklusif. Untuk data tanggal/waktu, selalu periksa apakah Anda perlu menyesuaikan batas atas untuk menyertakan seluruh hari, atau gunakan pola >= start AND < end_next_day.
Hindari Daftar IN yang Terlalu Panjang: Jika daftar nilai untuk IN Anda mencapai ratusan atau ribuan, performa bisa terganggu. Sebagai gantinya, pertimbangkan untuk menggunakan tabel temporer atau Common Table Expression (CTE) yang berisi daftar nilai tersebut, kemudian gunakan JOIN ke tabel utama Anda.
Penanganan NULL: Nilai NULL tidak akan pernah cocok dengan kondisi BETWEEN atau IN. Jika Anda perlu menyertakan nilai NULL, Anda harus secara eksplisit menambahkannya dengan OR column_name IS NULL.
NOT BETWEEN dan NOT IN: Kedua operator ini juga tersedia dan berguna untuk mengecualikan data dari rentang atau daftar tertentu. Gunakan dengan bijak untuk kejelasan query Anda.

Dampak pada Performa dan Keamanan

Performa

Operator BETWEEN dan IN di SQL Server didesain untuk menjadi efisien. Ketika diterapkan pada kolom yang diindeks, SQL Server Query Optimizer biasanya dapat melakukan index seek atau range scan. Ini jauh lebih cepat daripada full table scan, terutama pada tabel besar.

Index Seek/Scan: Untuk BETWEEN, optimizer dapat langsung melompat ke awal rentang (seek) dan membaca data secara berurutan hingga akhir rentang (scan).
Optimization for IN: Untuk IN, SQL Server dapat mengubah daftar nilai menjadi sebuah tabel konstanta internal atau bahkan melakukan hash match jika daftar tersebut besar, yang juga sangat efisien.

Namun, performa akan anjlok jika tidak ada indeks, atau jika indeks yang ada tidak cocok dengan kolom yang difilter. Penggunaan subquery yang kompleks dalam IN juga bisa menjadi performance killer jika subquery tersebut dieksekusi ulang untuk setiap baris dari query luar (correlated subquery).

Keamanan

Meskipun BETWEEN dan IN tidak secara langsung terkait dengan isu keamanan database seperti SQL Injection (itu adalah masalah pada input pengguna), penggunaan filtering yang tepat sangat penting untuk menerapkan prinsip Least Privilege. Dengan memfilter data secara akurat, Anda memastikan bahwa pengguna hanya melihat data yang relevan dan diizinkan untuk mereka akses. Ini mencegah pengungkapan data yang tidak perlu atau sensitif kepada pihak yang tidak berwenang.

Misalnya, dalam sebuah aplikasi HR, seorang manajer hanya boleh melihat data karyawan di departemennya. Filtering yang tepat dengan IN (berdasarkan daftar departemen yang diizinkan untuk manajer tersebut) atau BETWEEN (untuk rentang gaji tertentu yang boleh diakses) akan memastikan kebijakan keamanan ini ditegakkan di level database.

Kesimpulan

Operator BETWEEN dan IN adalah alat esensial dalam kotak perkakas setiap developer SQL. Mereka tidak hanya membuat query Anda lebih ringkas dan mudah dibaca, tetapi juga sangat membantu dalam mengoptimalkan performa filtering data. Dengan memahami karakteristik masing-masing, kapan harus menggunakannya, dan bagaimana menghindari kesalahan umum, Anda akan mampu menulis query SQL Server yang lebih efisien, robust, dan mudah dikelola. Ingatlah selalu untuk mempertimbangkan indeks pada kolom yang difilter dan dampak dari subquery pada performa.

FAQ (Frequently Asked Questions)

Q1: Apa perbedaan utama antara `BETWEEN` dan `IN`?

A1: Perbedaan utamanya terletak pada jenis data yang difilter. BETWEEN digunakan untuk memfilter nilai dalam sebuah rentang kontinu (misalnya, angka 1 sampai 10, tanggal 1 Januari sampai 31 Januari), dan bersifat inklusif (batas bawah dan atas disertakan). Sedangkan IN digunakan untuk memfilter nilai yang cocok dengan daftar nilai diskrit yang spesifik (misalnya, kategori 'A', 'B', 'C' atau ID pelanggan 101, 205, 310).

Q2: Apakah `BETWEEN` dan `IN` dapat menggunakan indeks untuk optimasi performa?

A2: Ya, keduanya dapat memanfaatkan indeks pada kolom yang difilter. Ketika sebuah kolom memiliki indeks, SQL Server Query Optimizer dapat melakukan index seek atau range scan yang jauh lebih cepat daripada memindai seluruh tabel. Ini sangat krusial untuk performa query, terutama pada tabel dengan volume data yang besar.

Q3: Bagaimana jika ada nilai `NULL` dalam daftar `IN` atau kolom yang difilter `BETWEEN`?

A3: Baik BETWEEN maupun IN tidak akan mencocokkan nilai NULL. Jika kolom yang Anda filter berisi NULL, dan Anda ingin menyertakan baris tersebut, Anda harus secara eksplisit menambahkan kondisi OR column_name IS NULL pada klausa WHERE Anda. Misalnya: WHERE KolomTanggal BETWEEN '2023-01-01' AND '2023-01-31' OR KolomTanggal IS NULL;

Q4: Kapan sebaiknya saya menghindari `IN` dengan subquery?

A4: Anda sebaiknya berhati-hati dengan IN yang menggunakan subquery dalam dua skenario:

Subquery Berkorelasi: Jika subquery di dalam IN adalah subquery berkorelasi (yaitu, ia bergantung pada data dari query luar dan dieksekusi untuk setiap baris dari query luar), ini bisa sangat tidak efisien. Seringkali, ini dapat diubah menjadi JOIN yang lebih performant.
Subquery Menghasilkan Banyak Baris: Jika subquery menghasilkan daftar nilai yang sangat besar (puluhan ribu atau lebih), memproses daftar tersebut bisa memakan banyak memori dan waktu. Dalam kasus ini, mungkin lebih baik untuk menyimpan hasil subquery ke dalam tabel temporer atau CTE, lalu melakukan JOIN.

Seri Belajar SQL Server:

← Sebelumnya: Pencarian Pola dengan LIKE

→ Selanjutnya: Logika Kondisional dengan CASE WHEN (segera terbit)

SQL Server: Filtering Rentang Data dengan BETWEEN dan IN