pertemuan 7.1

7Menggunakan Set Operators

Oracle Database 10g: Fundamentals I 72

TujuanDalam bab ini, anda mempelajari bagaimana cara menulis Query dengan menggunakan Set operator.

Tujuan

Setelah menyelesaikan pelajaran ini, Anda akan dapat melakukan berikut ini :

Menjelaskan set operators Menggunakan suatu set operator untuk mengkombinasikan beberapa queryquery ke dalam query tunggal

Mengontrol urutan baris yang dikembalikan


Set OperatorSet Operator menggabungkan hasil dari dua atau lebih komponen Query ke dalam sebuah penyelesaian.Query yang berisi beberapa Set Operator disebut sebagai Compound Query.

Operator HasilUNION Semua baris distinct dipilih oleh query yang samaUNION ALL Semua baris dipilih oleh query yang sama, termasuk semua duplikatINTERSECT Semua baris distinct dipilih oleh kedua queryMINUS Semua baris distinct yang telah dipilih oleh pernyataan SELECT dan tidak

dipilih di pernyataan kedua

Semua Set Operator mempunyai pendahuluan yang sama. Jika sebuah pernyataan SQL berisi beragam Set Operator, Oracle server akan mengevaluasinya dari kiri (atas) ke kanan (bawah) jika tidak ada sisipan secara jelas yang mengarah pada perintah lain. Anda harus menggunakan sisipan untuk menggolongkan perintah dari hasil evaluasi secara jelas dalam query yang menggunakan operator INTERSECT dengan Set operator yang lain.

Set Operators


Tabeltabel yang digunakan dalam pelajaran ini Bab ini menggunakan 2 tabel. Yaitu tabel EMPLOYEES dan tabel JOB_HISTORY.Tabel EMPLOYEES menyimpan detail karyawan. Untuk penrecord transaksi sumber daya manusia tabel ini bisa menyimpan nomor_id yang bersifat unik dan alamat email tiap karyawan. Detail dari no_id pekerjaan karyawan, gaji dan manajer juga akan tersimpan.Beberapa dari karyawan menerima komisi sebagai tambahan gaji mereka; informasi ini juga akan dicatat juga. Perusahaan mengatur karyawan sesuai dengan pekerjaannya. Beberapa karyawan sudah lama bekerja dalam perusahaan ini dan telah beberapa kali bergantiganti pekerjaan. Hal ini dimonitor menggunakan tabel JOB_HISTORY. Ketika seorang karyawan berganti pekerjaan, detail dari tanggal mulai dan tanggal akhir pekerjaan terakhir, no_id pekerjaan, dan departemen disimpan dalam tabel JOB_HISTORY.Struktur dan data dari tabel EMPLOYEES dan table HISTORY ditunjukkan pada halaman berikut ini.

TabelTabel yang Digunakan dalam Pelajaran Ini

Tabeltabel yang digunakan dalam pelajaran ini adalah : EMPLOYEES : Menyediakan rincian berdasarkan pegawaipegawai saat ini

JOB_HISTORY :Rincian catatan dari tanggal mulai dan tanggal berakhir dari suatu job yang terdahulu, dan nomor identifikasi job serta departemen saat seorang pegawai berpindah job.


Tabel – tabel yang digunakan pada bab ini (lanjutan)

Di dalam perusahaan ada beberapa orang yang bekerja dengan posisi yang sama lebih dari sekali sebelum mereka bergabung dengan perusahaan ini. Contohnya : salah satu karyawan yang bernama Taylor yang bergabung sebagai karyawan pada perusahaan ini pada 24Mar98. Taylor menempati jabatan sebagai SA_REP selama periode 24Mar98 sampai dengan 31Dec98 dan sebagai SA_MAN selama periode 01Jan99 sampai dengan 31Dec99. Taylor kembali lagi menjabat sebagai SA_REP, sampai dengan saat ini.Hal ini berbeda dengan Karyawan yang bernama Whalen, yang bergabung dengan perusahaan ini sejak 17Sep1987. Whalen memegang jabatan sebagai AD_ASST selama periode 17Sep87 sampai dengan 17Jun93 dan sebagai AC_ACCOUNT selama periode 01Jul94 sampai dengan 31Dec98. Whalen kembali lagi menjabat sebagai AD_ADDT, sampai dengan saat ini.

Describe employees

Name Null ? TypeEMPLOYEE_ID NOT NULL NUMBER(6)FIRST_NAME VARCHAR2(20)LAST_NAME NOT NULL VARCHAR2(25)EMAIL NOT NULL VARCHAR2(25)PHONE_NUMBER VARCHAR2(20)HIRE_DATE NOT NULL DATEJOB_ID NOT NULL VARCHAR2120)SALARY NUMBER(8,2)COMMISION_PCT NUMBER(2,2)MANAGER_ID NUMBER(6)DEPARTMENT_ID NUMBER(4)

SELECT employee_id, last_name, job_id, hire_date, department_idFROM employees;EMPLOYEE_ID LAST_NAME JOB_ID HIRE_DATE DEPARTMENT_

ID100 King AD_PRES 17JUN87 90101 Kochar AD_VP 21SEP89 90102 De Hann AD_VP 13JAN93 90103 Hunold IT_PROG 03JAN90 60104 Ernst IT_PROG 21MAY91 60107 Lorentz IT_PROG 07FEB99 60124 Mourgos ST_MAN 16NOV99 50141 Rajs ST_CLERK 17OCT95 50142 Davies ST_CLERK 29JAN97 50143 Matos ST_CLERK 15MAR98 50144 Vargas IT_PROG 09JUL98 50149 Zlotkey SA_MAN 29JAN00 80174 Abel SA_REP 11MAY96 80

Taylor SA_REP 24MAR98 176EMPLOYEE_ID LAST_NAME JOB_ID HIRE_DATE DEPARTMENT_

ID178 Grant SA_REP 24MAY99200 Whalen AD_ASST 17SEP87 10201 Harstein MK_MAN 17FEB96 20

. . .


Tabel yang digunakan pada Bab ini (lanjutan)DESCRIBE job_history

Name Null ? TypeEMPLOYEE_ID NOT NULL NUMBER(6)START_DATE NOT NULL DATEEND_DATE NOT NULL DATEJOB_ID NOT NULL VARCHAR2(10)DEPARTMENT_ID NUMBER(4)

SELECT * FROM job_history

EMPLOYEE_ID START_DATE HIRE_DATE JOB_ID DEPARTMENT_ID

102 13JAN98 24JUL98 IT_PROG 60

101 21SEP93 27OCT93 AC_ACCOUNT 110

101 28OCT97 15MAR97 AC_MGR 110

201 17FEB96 19DEC99 MK_REP 20

114 24MAR98 31DEC99 ST_CLERK 50

122 01JAN99 31DEC99 ST_CLERK 50

200 17SEP87 17JUN93 AD_ASST 90

176 24MAR98 31DEC98 SA_REP 80

176 01JAN99 31DEC99 SA_MAN 80

200 01JUL94 31DEC98 AC_ACCOUNT 90

10 rows selected


UNION OperatorUNION Operator mengembalikan semua baris yang dipilih oleh query yang sama. Gunakan UNION Operator untuk mengembalikan semua baris dari beberapa tabel kemudian menghapus semua baris duplikat.

PedomanPedoman

Jumlah kolom dan tipe data dari kolom yang telah dipilih harus identik dengan semua pernyataan SELECT yang digunakan dalam Query. Nama kolom tidak harus identik.

UNION mengubah keseluruhan kolom yang telah dipilih Nilai NULL tidak diperbolehkan sebelum dicek. Operator IN mempunyai prioritas lebih dulu daripada operator UNION Secara default, output akan diurutkan secara ascending dari kolom pertama kalimat SELECT.

Operator UNION

Operator UNION mengembalikan hasilhasil dari kedua query

setelah menghilangkan adanya duplikasi.


Menggunakan Operator UNIONOperator UNION menghapus semua record duplikat. Jika record tidak ada dalam kedua tabel EMPLOYEES dan JOB_HISTORY secara identik, maka record tersebut ditampilkan hanya sekali. Amati dari tampilan output pada slide, record untuk karyawan dengan EMPLOYEE_ID200 ditampilkan dua kali karena JOB_ID berbedabeda pada tiap row.

Perhatikan contoh dibawah ini :SELECT employee_id, job_id, department_idFROM employees UNION SELECT employee_id, job_id, department_idFROM job_history;EMPLOYEE_ID JOB_ID DEPARTMENT_ID

…200 AC_ACOUNT 90200 AD_ASST 10200 AD_ASST 90. . .

29 rows slected

Pada output tersebut diatas tadi, karyawan dengan EMPLOYEE_ID = 200 muncul 3 kali. Kenapa ? Perhatikan nilai DEPARTMENT_ID untuk karyawan 200. Satu baris mempunyai DEPARTMENT_ID 90, lainnya 10 dan 90. Karena ini merupakan gabungan unik dari job_IDs dan department_IDs, setiap baris untuk karyawan 200 ini unik dan maka bukan merupakan duplikat. Amati bahwa outputnya berurutan secara ascending dari kolom pertama kalimat SELECT (dalam hal ini, EMPLOYEE_ID).

Menggunakan Operator UNION

Menampilkan detail pekerjaan dari semua karyawan baik yang sekarang atau sebelumnya. Menampilkan karyawan hanya sekali.


Operator UNION ALLGunakan operator UNION ALL untuk mengembalikan semua baris dari beberapa queri

PedomanPedomanPetunjuk untuk UNION dan UNION ALL semua sama, tapi dengan 2 perkecualian untuk UNION ALL yaitu : Tidak seperti UNION, baris duplikat tidak akan terhapus dan output tidak diurutkan secara default.

Keyword DISTINCT tidak bisa digunakan.

Operator UNION ALL

UNION ALL operator mengembalikan hasilhasil dari kedua query, termasuk semua hasil yang duplikasi.


UNION ALL (lanjutan)Seperti yang pada contoh diatas, ada 30 baris yang terpilih. Gabungan antara dua tabel berjumlah 30 baris. Operator UNION ALL tidak menghapus baris duplikat. UNION mengembalikan semua baris distinct yang telah diseleksi oleh query tersebut. Fungsi UNION ALL mengembalikan semua baris yang telah diseleksi oleh query, termasuk semua duplikatnya. Perhatikan quey yang terdapat pada slide, sekarang tuliskan dengan menggunakan kalimat UNION :

SELECT employee_id, job_id, department_idFROM employeesUNIONSELECT employee_id, job_id, department_idFROM job_historyORDER BY employee_id;

Pada hasil query perintah diatas didapatkan hasil 29 baris. Hal ini dikarenakan perintah diatas telah menghapus baris berikut ini (yang merupakan sebuah baris duplikat) :

EMPLOYEE_ID JOB_ID DEPARTMENT_ID

176 SA_REP 80

Menggunakan operator UNION ALL

Menampilkan semua karyawan yang bekerja di departemen baik yang sekarang maupun sebelumnya.

.


Operator INTERSECT Gunakan operator INTERSECT untuk mengembalikan semua baris yang ada (terlibat) dalam beberapa query.

Petunjuk Nomor kolom dan tipe data kolom telah dipilih oleh pernyataan SELECT dalam query harus selalu identik di dalam semua pernyataan SELECT yang digunakan dalam query. Nama dari kolom tidak harus selalu identik.

Membalikkan tabel yang telah diintersect tidak akan mengubah hasil. INTERSECT tidak akan memperbolehkan adanya nilai NULL.

Operator INTERSECT

Operator INTERSECT mengembalikan nilai (baris) yang terletak diantara kedua query.


Operator INTERSECT (lanjutan)Seperti contoh pada slide, query hanya mengembalikan record yang mempunyai kesamaan nilai di dalam kolom yang telah dipilih di kedua tabel tersebut.Apa yang akan dihasilkan jika anda menambahkan kolom DEPARTMENT_ID ke dalam pernyataan SELECT dari table JOB_HISTORY dan menjalankan query ini ? Hasilnya bisa saja berbeda karena pendahuluan dari kolom lainnya dimana nilai bisa diduplikasikan atau tidak diduplikasikan.ContohSELECT employee_id, job_id, department_idFROM employeesINTERSECTSELECT employee_id, job_id, department_idFROM job_history;

EMPLOYEE_ID JOB_ID DEPARTMENT_ID

176 SA_REP 80

Karyawan 200 bukan lagi bagian dari hasil, hal ini dikarena nilai EMPLOYEES.DEPARTMENT_ID berbeda dari nilai JOB_HISTORY.DEPARTMENT_ID

Menggunakan Operator INTERSECT

Menampilkan employee ID dan job ID dari tabel employee dimana karyawan tersebut mempunyai pekerjaan yang sama dengan pekerjaan mereka terdahulu (karyawan tersebut telah berganti pekerjaan tapi sekarang kembali lagi bekerja pada pekerjaan asal)


Operator MINUSGunakan Operator MINUS untuk mengembalikan baris, yang dikembalikan oleh query pertama, yang tidak tampak pada saat query kedua (pernyataan SELECT pertama MINUS pernyataan SELECT kedua).

Petunjuk

Jumlah kolom dan tipe data dari kolom telah dipilih oleh pernyataan SELECT di dalam query seharusnya identik di dalam semua pernyataan SELECT yang digunakan dalam query. Nama kolom tidak harus identik.

Semua kolom dalam kalimat WHERE seharusnya berada dalam kalimat SELECT supaya operator MINUS berkerja.

Operator MINUS

Operator MINUS mengembalikan baris pada query pertama dan tidak tampak pada query kedua.


Operator MINUS (lanjutan)Seperti contoh pada slide, employee_ID dan job_ID dalam tabel JOB_HISTORY dikurangi dari tabel EMPLOYEES. Rnomorian hasil menampilkan sisa karyawan setelah pengurangan; data tersebut diperlihatkan oleh baris yang terdapat pada tabel EMPLOYEES tapi tidak terdapat dalam tabel JOB_HISTORY. Ini merupakan record dari karyawan yang tidak pernah berganti pekerjaan meskipun sekali.

Operator MINUS

Menampilkan employee id dari karyawankaryawan yang tidak pernah ganti pekerjaan sekalipun.


Petunjuk Tentang Set Operator

Penulisan di dalam rnomori SELECT dari query harus sesuai dengan nomor dan tipe data. Query yang menggunakan operatoroperator seperti UNION, UNION ALL, INTERSECT dan MINUS didalam kalimat WHEREnya harus mempunyai persamaan nomor dan tipe data kolom dalam rangkaian SELECT.Seperti pada contoh :

SELECT employee_id, department_idFROM employeesWHERE (employee_id, department_id)

IN (SELECT employee_id, department_id)FROM employeesUNIONSELECT employee_id, department_idFROM employees);

Kalimat ORDER_BY : Bisa muncul pada akhir sebuah pernyataan Akan menerima nama kolom, sebuah alias atau sesuai dengan letak penulisan

Nama kolom atau alias, jika digunakan sebuah kalimat ORDER_BY, seharusnya berasal dari daftar SELECT yang pertama.

Set operator bisa digunakan pada subqueri.

Petunjuk Set Operator

Ekspresi yang terdapat pada SELECTlist harus sama dengan nomor dan tipe datanya.

Parentheses dapat digunakan untuk merubah sequence. ORDER BY clause :

Dapat digunakan hanya diakhir dari suatu statement.

Dapat menggunakan nama kolom, alias dari SELECT statement yang pertama, atau sesuai dengan letak penulisan.


Server Oracle dan Set operatorKetika sebuah query menggunakan set operator, Server Oracle akan menghapus baris duplikat secara otomatis kecuali di dalam kondisi dengan operator UNION ALL. Nama kolom outputnya ditentukan oleh daftar kolom pada pernyataan SELECT yang pertama. Secara default output akan diurutkan secara ascending dari kolom pertama kalimat SELECT.Keterkaitan penulisan di dalam daftar SELECT yang merupakan komponen query dari campuran query harus sesuai dengan nomor dan tipe data. Jika komponen query memilih data karakter, tipe data dari nilai balik dengan ketentuan sebagai berikut :

Jika kedua query memilih nilai dari tipe data CHAR, nilai balik harus berupa tipe data CHAR.

Jika salah satu atau kedua query tersebut memilih nilai tipe data VARCHAR2, maka nilai balik berupa tipe data VARCHAR2.

Server Oracle dan Set Operator

Baris yang duplikasi akan otomatis hilang kecuali pada UNION ALL.

Nama Kolom dari query yang pertama akan muncul pada hasil.

Output akan diurutkan secara ascending oleh order by default kecuali pada UNION ALL.


Membandingkan dengan Pernyataan SELECTKarena penulisan dari daftar pilih dari query harus sesuai dengan nomor, anda bisa menggunakan kolom dummy dan Para pengguna konversi tipe data sudah menyetujui hal ini. Di dalam slide, nama location itu diberikan sebagai nama heading kolom dummy. Fungsi dari TO_NUMBER ialah digunakan pada query pertama untuk membandingkan tipe data NUMBER dari kolom LOCATION_ID yang akan diterima kembali oleh query yang kedua. Begitupun, fungsi dari TO_DATE pada query yang kedua, digunakan untuk membandingkan tipe data kolom HIRE_DATE yang diterima kembali oleh first query.

Membandingkan Dengan Pernyataan SELECT

Menggunakan operator UNION, untuk menampilkan Department_ID, Location, dan Hire Date untuk semua karyawan.


Membandingkan dengan pernyataan SELECT : ContohTable EMPLOYEES dan JOB_HISTORY mempunyai beberapa kolom di dalamnya (seperti contoh, EMPLOYEE_ID, JOB_ID dan DEPARTMENT_ID). Tapi apa yang dihasilkan, jika anda ingin melakukan query untuk menampilkan employee ID, ob ID dan gaji menggunakan operator UNION, diketahui bahwa gaji hanya terdapat di table EMPLOYEES ?Contoh kode seperti pada slide, membandingkan antara kolom EMPLOYEE_ID dan JOB_ID di dalam tabel EMPLOYEES dan JOB_HISTORY. Biasanya nilai 0 dihasilkan pernyataan JOB_HISTORY SELECT untuk membandingkan angka kolom SALARY pada pernyataan EMPLOYEE SELECT.Pada hasil sebelumnya, setiap baris di dalam output yang berhubungan dengan record dari tabel JOB_HISTORY yang berisi 0 pada kolom SALARY.

Membandingkan Dengan Pernyataan SELECT : Contoh

Menggunakan operator UNION, untuk menampilkan employee_ID, job_id, dan salary untuk semua karyawan.


Mengontrol permintaan akan barisSecara default, outputnya terurut berdasarkan ascending pada kolom pertama. Anda bisa menggunakan perintah ORDER BY untuk menggantinya.Perintah ORDER BY bisa digunakan hanya sekali dalam campuran query. Jika digunakan, perintah ORDER BY harus diletakkan di bagian akhir dari penulisan query. Perintah ORDER BY dapat digunakan pada nama kolom atau menggunakan alias. Kode contoh pada slide tersebut diatas, ketika tanpa menggunakan perintah ORDER BY maka akan menghasilkan output secara alphabet pada kolom pertamanya :

Catatan : Pikirkan sebuah query campuran dimana set operator UNION digunakan lebih dari sekali. Dalam kasus ini, perintah ORDER BY bisa digunakan hanya ketika keberadaannya jarang daripada penulisan secara explisit Perintah iSQL*Plus COLUMN Anda bisa menggunakan perintah iSQL*Plus COLUMN untuk menyesuaikan heading kolom.Syntax :COL[UMN] [{column|alias}[option]]

Dimana OPTION itu :CLE[AR] : Menghapus format kolomHEA[DING] text : Mengatur heading kolomFOR[MAT] format : Merubah tampilan kolom menggunakan format modelNOPRINT | PRINT : Menindas atau menampilkan heading kolom dan data NULLPernyataan berikut ini menindas data kolom dan heading judul untuk kolom dinamakan A_DUMMY. Perhatikan SELECT clause yang pertama dalam slide pendahuluan membuat sebuah kolom bernama A_DUMMY.

COLUMN a_dummy NOPRINT

My dream

I’d like to teach

sing

the world to

Mengontrol Urutan BarisBaris

Menghasilkan sebuah kalimat dalam bahasa Inggris menggunakan dua operator UNION.


Ringkasan Operator UNION menghasilkan semua baris yang pilih query tersebut. Menggunakan operator

UNION untuk menghasilkan semua baris dari berbagai tabel dan menghapus semua baris duplikat.

Menggunakan operator UNION ALL untuk menghasilkan semua baris dari beragam query. Tidak seperti dalam kondisi dengan menggunakan operator UNION, baris duplikat tidak dihapus dan output tidak diurutkan secara default.

Menggunakan operator INTERSECT untuk mengembalikan semua baris yang ada ke multiple query.

Menggunakan operator MINUS untuk mengembalikan baris yang dikembalikan oleh query yang pertama, dimana hal ini tidak tampak pada query yang kedua.

Ingatlah untuk menggunakan ORDER BY clause hanya pada bagian terakhir pernyataan query beragam.

Pastikan keterkaitan penulisan dalam kesesuaian daftar SELECT yaitu nomor dan tipe datanya.

RINGKASAN

Pada bab ini, anda harus mempelajari bagaimana cara untuk :

Menggunakan UNION untuk menghasilkan semua baris distinct

Menggunakan UNION ALL untuk menghasilkan semua baris, termasuk duplikatnya.

Menggunakan INTERSECT untuk menghasilkan semua baris dimana digunakan untuk saling berbagi antar kedua query tersebut.

Menggunakan MINUS untuk menghasilkan semua baris distinct yang dipilih oleh query yang pertama tapi tidak dipilih di query yang kedua.

Menggunakan ORDER BY hanya pada bagian paling akhir dari pernyataan query.

pertemuan 7.1

Documents