Records

Filtering Data SQL

Setelah mempelajari dasar pembuatan kueri, kita akan mengimplementasikan pembuatan kueri yang lebih kompleks dengan penggunaan filter. Ada banyak kondisi ketika kita hanya membutuhkan sebagian data saja sehingga penggunaan filter akan sangat bermanfaat.

Penggunaan WHERE

Dalam pembuatan kueri, pernyataan SELECT akan memberikan hasil semua baris dari satu maupun beberapa kolom dari tabel. Jika kamu menginginkan hanya sebagian data, kamu dapat membuat filter dengan menggunakan pernyataan WHERE. Sintaksis penggunaan WHERE adalah sebagai berikut:

SELECT
    daftar_kolom
FROM
    nama_tabel
WHERE
    kondisi
ORDER BY
    ekspresi_pengurutan;

Urutan pengecekan yang akan dilakukan oleh PostgreSQL terhadap penggunaan WHERE adalah tepat setelah evaluasi terhadap FROM namun sebelum SELECT dan ORDER BY.

Penggunaan WHERE juga bisa dilakukan bersamaan dengan UPDATE dan DELETE untuk menentukan kondisi khusus data yang akan diupdate dan dihapus.

Penggunaan pernyataan WHERE membutuhkan kondisi dengan penggunaan operator pembandingan dan logika sebagai berikut

Operator Deskripsi
= Sama dengan
> Lebih besar dari
< Lebih kecil dari
>= Lebih besar atau sama dengan
<= Lebih kecil atau sama dengan
<> atau != Tidak sama dengan
AND Operator logika AND
OR Operator logika OR
IN Bernilai benar jika nilai sesuai dengan nilai yang ada di daftar
BETWEEN Bernilai benar jika nilai berada di antara rentang nilai
LIKE Bernilai benar jika nilai cocok dengan pola
IS NULL Bernilai benar jika nilainya NULL
NOT Menegasikan hasil dari operator lainnya

Sebagai contoh, saya akan menggunakan tabel customer dari database dvdrental dengan kolom sebagai berikut

column_name|
-----------+
active     |
store_id   |
create_date|
last_update|
customer_id|
address_id |
activebool |
first_name |
last_name  |
email      |

1. Penggunaan WHERE dengan operator sama dengan (=)

Berikut kueri yang akan memberikan hasil nama depan dan nama belakang pelanggan yang memiliki nama depan Tracy:

SELECT
	first_name ,
	last_name
FROM
	customer c
WHERE
	first_name = 'Tracy';

Hasil kueri:

first_name|last_name|
----------+---------+
Tracy     |Cole     |
Tracy     |Herrmann |

Ternyata dari tabel customer di database dvdrental terdapat dua pelanggan dengan nama depan Tracy.

2. Penggunaan WHERE dengan operator AND

Operator logika AND dapat digunakan untuk memadukan dua kondisi filter. Berikut contoh kueri untuk mencari pelanggan yang memiliki nama depan Tracy dan nama belakang Herrmann. Ingat kembali bahwa operator logika AND akan memberikan hasil dengan kondisi dua kondisi yang seluruhnya terpenuhi.

SELECT
	first_name ,
	last_name
FROM
	customer c
WHERE
	first_name = 'Tracy'
	AND last_name = 'Herrmann';

Hasil kueri:

first_name|last_name|
----------+---------+
Tracy     |Herrmann |

3. Penggunaan WHERE dengan operator OR

Operator logika OR akan memberikan hasil benar jika salah satu kondisinya terpenuhi. Berikut adalah contoh kueri yang menampilkan pelanggan dengan nama depan Kelly atau nama belakang Jones.

SELECT
	first_name ,
	last_name
FROM
	customer c
WHERE
	first_name = 'Kelly'
	OR last_name = 'Jones';

Hasil kueri

first_name|last_name|
----------+---------+
Barbara   |Jones    |
Kelly     |Torres   |
Kelly     |Knott    |

Terdapat tiga pelanggan yang memenuhi kondisi tersebut. Dua pelanggan memiliki nama depan Kelly dan satu pelanggan memiliki nama belakang Jones.

4. Penggunaan WHERE dengan operator IN

Penggunaan operator IN akan mengecek nilai terhadap satu kumpulan nilai. Berikut contoh kueri yang akan memberikan hasil nama depan dan nama belakang untuk pelanggan yang memiliki nama depan Muhammad, Jesus, Moses, Abraham maupun Noah.

SELECT
	first_name ,
	last_name
FROM
	customer c
WHERE
	first_name IN ('Muhammad', 'Jesus', 'Abraham', 'Moses', 'Noah' );

Hasil kueri:

first_name|last_name|
----------+---------+
Jesus     |Mccartney|

Ternyata dari lima kriteria nama, hanya satu pelanggan yang memiliki nama depan sesuai kriteria yang diberikan.

5. Penggunaan WHERE dengan operator LIKE

Operator LIKE akan mencari string yang cocok berdasarkan pola yang diberikan. Berikut contoh kueri yang mencari nama depan yang dimulai dengan kata Jac:

SELECT
	first_name ,
	last_name
FROM
	customer c
WHERE
	first_name LIKE 'Jac%';

Hasil kueri:

first_name|last_name|
----------+---------+
Jacqueline|Long     |
Jackie    |Lynch    |
Jack      |Foust    |
Jacob     |Lance    |

Pada kueri tersebut digunakan karakter persen (%) yang disebut sebagai wildcard yang akan bernilai sesuai untuk string apaapun. Pola yang digunakan pada kueri adalah Jac% yang berarti akan mencari string yang diawali dengan Jac.

6. Penggunaan WHERE dengan operator BETWEEN

Penggunaan operator BETWEEN membutuhkan tiga input, jenis kondisi, batas bawah dan batas atas dari rentang nilai yang akan dicocokkan. Berikut contoh kueri yang akan menampilkan nama depan dan belakang pelanggan dengan kondisi panjang nama belakang pelanggan antara 2 dan 3 karakter.

SELECT
	first_name ,
	last_name
FROM
	customer c
WHERE
	LENGTH(last_name) BETWEEN 2 AND 3
ORDER BY
	LENGTH(last_name);

hasil kueri:

first_name|last_name|
----------+---------+
Roberto   |Vu       |
Kimberly  |Lee      |
Judith    |Cox      |
Audrey    |Ray      |
Holly     |Fox      |
Lillie    |Kim      |
Courtney  |Day      |
...

7. Penggunaan WHERE dengan operator tidak sama dengan (<>)

Penggunaan operator tidak sama dengan relatif sederhana, sifatnya hanya kebalikan dari operator sama dengan yang sebelumnya telah kita bahas. Berikut contoh kueri untuk menampilkan nama depan dan nama belakang pelanggan dengan kondisi nama depan pelanggan diawali dengan Jac namun bukan Jackie

SELECT
	first_name ,
	last_name
FROM
	customer c
WHERE
	first_name LIKE 'Jac%'
	AND first_name  <> 'Jackie';

Hasil kueri:

first_name|last_name|
----------+---------+
Jacqueline|Long     |
Jack      |Foust    |
Jacob     |Lance    |

Perlu diingat bahwa operator tidak sama dengan memiliki beberapa bentuk, kamu dapat menggunakan != atau <>.

Penggunaan operator AND

  • Pada PostgreSQL, tipe data Boolean bisa memiliki satu dari tiga buah nilai berikut: true, false, maupun NULL.
  • PostgreSQL menggunakan kata kunci true, t, 'true', y, yes, 1 untuk merepresentasikan nilai true dan false, f, 'false', n, no dan 0 untuk merepresentasikan nilai false.
  • Operator AND akan melakukan pengecekan pada dua atau lebih ekspresi boolean.

Sebagai pengingat, berikut tabel operasi logika AND:

AND True False NULL
True True False NULL
False False False False
NULL NULL False NULL

Untuk kombinasi penggunaan operator logika AND dan pernyataan WHERE, kamu bisa merujuk pada bagian tulisan sebelumnya di bagian penggunaan WHERE.

Penggunaan operator OR

Operator OR merupakan operator logika yang akan melakukan pengujian terhadap ekspresi boolean. Berikut tabel logika operator OR di PostsgreSQL.

OR True False NULL
True True True True
False True False NULL
NULL True NULL NULL

Untuk kombinasi penggunaan operator logika OR dan pernyataan WHERE, kamu bisa membaca kembali bagian tulisan sebelumnya di bagian penggunaan WHERE.

Penggunaan LIMIT

Pada PostgreSQL, pernyataan LIMIT bersifat pilihan yang bisa disandingkan dengan pernyataan SELECT untuk membatasi jumlah hasil kueri.

1. Penggunaan LIMIT untuk membatasi hasil

Kamu dapat membatasi hasil kueri yang ingin ditampilkan dengan menggunakan LIMIT. Berikut contoh kueri pada tabel film di database dvdrental yang hanya menampilkan 4 baris hasil.

SELECT
	film_id ,
	title,
	release_year
FROM
	film f
LIMIT 4;

Hasil Kueri:

film_id|title            |release_year|
-------+-----------------+------------+
    133|Chamber Italian  |        2006|
    384|Grosse Wonderful |        2006|
      8|Airport Pollock  |        2006|
     98|Bright Encounters|        2006|

2. Penggunaan LIMIT dengan pernyataan OFFSET

Kamu bisa memanfaatkan penggunaan OFFSET jika ingin memulai dari baris tertentu. Berikut contoh kueri yang mirip dengan kueri sebelumnya namun akan melewati 2 baris pertama.

SELECT
	film_id ,
	title,
	release_year
FROM
	film f
LIMIT 4 OFFSET 2;

Hasil Kueri:

film_id|title            |release_year|
-------+-----------------+------------+
      8|Airport Pollock  |        2006|
     98|Bright Encounters|        2006|
      1|Academy Dinosaur |        2006|
      2|Ace Goldfinger   |        2006|

3. Penggunaan LIMIT OFFSET untuk mendapatkan top/bottom N baris

Salah satu penggunaan LIMIT OFFSET adalah untuk mendapatkan data sejumlah N-baris paling awal atau terakhir. Sebagai contoh, berikut adalah kueri untuk mendapatkan 10 judul film dengan durasi film terendah.

SELECT
	film_id ,
	title,
	release_year,
	f.length 
FROM
	film f
ORDER BY f.length
LIMIT 10;

hasil kueri:

film_id|title              |release_year|length|
-------+-------------------+------------+------+
     15|Alien Center       |        2006|    46|
    504|Kwai Homeward      |        2006|    46|
    730|Ridgemont Submarine|        2006|    46|
    469|Iron Moon          |        2006|    46|
    505|Labyrinth League   |        2006|    46|
    247|Downhill Enough    |        2006|    47|
    407|Hawk Chill         |        2006|    47|
    393|Halloween Nuts     |        2006|    47|
    237|Divorce Shining    |        2006|    47|
    398|Hanover Galaxy     |        2006|    47|

Pada kueri di atas, dilakukan pengurutan terhadap durasi film dengan nama kolom length kemudian pembatasan sejumlah kueri dengan LIMIT 10.

Penggunaan FETCH

Pada bagian sebelumnya, kita menggunakan LIMIT untuk membatasi jumlah baris pada hasil kueri. Penggunaan LIMIT memiliki keterbatasan pada beberapa jenis database karena bukan merupakan standar SQL. Sebagai alternatif, kamu bisa menggunakan FETCH yang berfungsi sama seperti LIMIT. Berikut cuplikan sintaksis penggunaan FETCH pada kueri:

    OFFSET row_to_skip { ROW | ROWS }
    FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY

Pada sintaksis tersebut, tentukan jumlah baris yang akan dilewati (row_to_skip). Kemudian tentukan jumlah baris yang akan ditampilkan pada row_count. Berikut contoh kueri yang menghasilkan data seperti pada penggunaan LIMIT sebelumnya:

SELECT
	film_id ,
	title,
	release_year,
	f.length 
FROM
	film f
ORDER BY f.length
FETCH FIRST 10 ROWS ONLY ;

hasil kueri:

film_id|title              |release_year|length|
-------+-------------------+------------+------+
     15|Alien Center       |        2006|    46|
    504|Kwai Homeward      |        2006|    46|
    730|Ridgemont Submarine|        2006|    46|
    469|Iron Moon          |        2006|    46|
    505|Labyrinth League   |        2006|    46|
    247|Downhill Enough    |        2006|    47|
    407|Hawk Chill         |        2006|    47|
    393|Halloween Nuts     |        2006|    47|
    237|Divorce Shining    |        2006|    47|
    398|Hanover Galaxy     |        2006|    47|

Penggunaan IN

Pada PostgreSQL, operator IN digunakan untuk melakukan pengujian kecocokan nilai terhadap kumpulan nilai. Secara konsep, operator IN berfungsi layaknya kombinasi dari operator OR. Performa kueri menggunakan operator IN lebih cepat dibandingkan menggunakan beberapa operator OR. Operator IN dapat digunakan untuk tipe data angka, string, dan tanggal. Kamu bisa juga mengkombinasikan operator IN dengan NOT untuk memberikan hasil yang sesuai dengan yang kamu butuhkan.

Berikut contoh kueri untuk menghasilkan daftar aktor dan aktris yang memiliki nama depan Adam atau Mena:

SELECT
	a.first_name, 
	a.last_name
FROM
	actor a
WHERE
	first_name IN ('Adam', 'Mena')
ORDER BY
	first_name ;

Hasil kueri:

first_name|last_name|
----------+---------+
Adam      |Grant    |
Adam      |Hopper   |
Mena      |Temple   |
Mena      |Hopper   |

Penggunaan BETWEEN

Seperti bahasan sebelumnya, penggunaan BETWEEN setidaknya memerlukan tiga komponen seperti cuplikan sintaksis berikut:

value BETWEEN low AND High

Penggunaan pernyataan BETWEEN ini fungsinya sama dengan penggunaan operator lebih besar sama dengan dari (>=) yang dikombinasikan dengan operator lebih kecil sama dengan dari (<=) menggunakan operator AND.

value >= low AND value <= high

Penggunaan BETWEEN dapat diterapkan pada tipe data angka dan tanggal.

Penggunaan LIKE

Seperti yang sudah pernah sedikit dibahas di atas, penggunaan LIKE membutuhkan pola yang akan dicocokkan. Penggunaan pernyataan LIKE sangat erat kaitannya dengan pembuatan pola dan penggunaan wildcard. Sebagai contoh, berikut kueri yang akan mengambil judul film dengan nama judul memenuhi pola _li%:

SELECT
	title
FROM
	film f
WHERE
	title LIKE '_li%';

Hasil kueri:

title                |
---------------------+
Ali Forever          |
Alice Fantasia       |
Alien Center         |
Blindness Gun        |
Elizabeth Shane      |
Flight Lies          |
Flintstones Happiness|
Sling Luke           |
Slipper Fidelity     |

Pada kueri tersebut terdapat satu underscore (_) yang menandakan perlu satu karakter apapun sebelum string li. Selanjutnya penggunaan simbol persen (%) menandakan pola apapun akan sesuai.

Perlu diperhatikan bahwa penggunaan LIKE bersifat case-sensitive. Jika ingin membuat kueri untuk string yang bersifat case-insensitive maka kamu bisa menggunakan ILIKE. Berikut tabel yang menggambarkan penggunaan pernyataan LIKE dan ILIKE yang juga dipadukan dengan NOT.

Operator Equivalent
~~ LIKE
~~* ILIKE
!~~ NOT LIKE
!~~* NOT ILIKE

Penggunaan IS NULL

Seperti namanya, operator IS NULL akan melakukan pengecekan apakah nilai suatu kolom bernilai Null atau bukan. Perlu diperhatikan bahwa penggunaan IS NULL tidak bisa digantikan dengan menggunakan operator sama dengan (=) atau tidak sama dengan (<>) untuk melakukan pengecekan nilai Null.

Berikut contoh kueri untuk mendapatkan alamat dengan nilai address2 bernilai Null.

SELECT
	address ,
	address2 ,
	district
FROM
	address a
WHERE
	address2 IS NULL

hasil kueri:

address             |address2|district|
--------------------+--------+--------+
47 MySakila Drive   |[NULL]  |Alberta |
28 MySQL Boulevard  |[NULL]  |QLD     |
23 Workhaven Lane   |[NULL]  |Alberta |
1411 Lillydale Drive|[NULL]  |QLD     |

Setelah kita mempelajari cara melakukan filtering data pada SQL, selanjutnya kita akan membahas cara melakukan penggabungan data dari dua atau lebih tabel dengan perintah JOIN.