SQL 1 (pertemuan 4)

Menampilkan Data Dari Beberapa Tabel

Ø  Tipe Tipe pada Join Join yang mengacu pada standar SQL:1999 termasuk sebagai berikut :

      Cross joins

     Natural joins

     Klausa USING

     Full (atau twosided) outer joins

     Kondisi kondisi penggabungan tidak tetap untuk outer join

Ø  Menggabungkan Tabel tabel

Menggunakan Sintak SQL:1999 Gunakan suatu join untuk mengquery data dari beberapa tabel :

SELECT table1.column, table2.column

FROM table1

[NATURAL JOIN table2] |

[JOIN table2 USING (column_name)] |

[JOIN table2  ON {table1.column_name = table2.column_name}]|

[LEFT |RIGHT | FULL OUTER JOIN table2 ON {table1.column_name =

table2.column_name}]|

[CROSS JOIN table2];

Ø  Mendefinisikan Join

v  Dalam sintak:

table1.column menunjukkan tabel dan kolom dari mana data diperoleh NATURAL JOIN menggabungkan dua tabel berdasarkan nama kolom yang sama JOIN table USING column_name melakukan suatu equijoin berdasarkan nama kolom. JOIN table ON table1.column_name melakukan suatu equijoin berdasarkan suatu kondisi pada klausa ON, = table2.column_name LEFT/RIGHT/FULL OUTER digunakan untuk melakukan outer joins. CROSS JOIN mengembalikan suatu Cartesian product dari dua tabel.

Ø  Membuat Natural Joins

      Klausa NATURAL JOIN adalah didasarkan pada semua kolom pada dua tabel yang memiliki nama yang sama.

      NATURAL JOIN memilih baris baris dari dua tabel yang memiliki nilai nilai yang sama dalam semua kolom yang sesuai.

      Jika kolom kolom memiliki nama yang sama memiliki tipe data berbeda, menghasilkan suatu error.

v  Natural Join dengan suatu klausa WHERE

Pembatasan tambahan pada suatu natural join diterapkan dengan menggunakan klausa WHERE.

Contoh berikut membatasi baris baris sebagai outputnya pada suatu department ID sama dengan 20 atau 50 :

SELECT department_id, department_name, location_id, city

FROM departments

NATURAL JOIN locations

WHERE department_id IN(20,50);

Ø  Klausa USING

Natural joins menggunakan semua kolom dengan menyesuaikan nama nama dan tipe data tipe Data untuk menggabungkan table tabel. Klausa USING dapat digunakan untuk menentukan hanya kolom kolom tertentu yang akan digunakan untuk suatu equijoin. Kolom kolom yang direferensikan pada klausa USING tidak akan memiliki suatu perubah/qualifier (nama tabel atau alias) di manapun pada pernyataan SQL.Sebagai contoh,

v  pernyataan berikut adalah tepat :

SELECT l.city, d.department_name

FROM locations l JOIN departments d USING (location_id)

WHERE location_id = 1400;

v  Pernyataan berikut tidak tepat karena LOCATION_ID dirubah (qualified) pada klausa WHERE :

SELECT l.city, d.department_name

FROM locations l JOIN department d USING (location_id)

WHERE d.location_id = 1400;

v  ORA25154:

column part of USING clause cannot have qualifier Pembatasan yang sama juga berlaku untuk NATURAL join. Oleh karena itu, kolom kolom yang memiliki nama yang sama pada kedua tabel harus digunakan tanpa ada qualifier/perubah.

Ø  Klausa USING untuk Equijoins

Untuk menentukan nama departement dari para pekerja, Anda membandingkan nilai pada kolom DEPARTMENT_ID di dalam tabel EMPLOYEES dengan nilai nilai DEPARTMENT_ID di dalam tabel DEPARTMENTS. Hubungan antara table table EMPLOYEES dan DEPARTMENTS adalah suatu equijoin; karena itu, nilai nilai pada kolom DEPARTMENT_ID di kedua tabel harus sama. Seringkali, tipe dari join ini melibatkan kelengkapan kelengkapan primary dan foreign key. Catatan: Equijoins juga disebut simple joins atau inner joins.

Ø  Merubah Nama Nama Kolom Ambigu

      Gunakan awalan awalan tabel untuk merubah nama nama kolom yang ada pada beberapa tabel.

      Gunakan awalan awalan tabel untuk meningkatkan performa.

      Gunakan kolom kolom alias untuk membedakan kolom kolom yang memiliki nama nama sama tapi berada dalam table table berbeda.

      Jangan gunakan alias alias pada kolom kolom yang disebutkan pada klausa USING dan dimanapun terdaftar pada pernyataan SQL.

Ø  Menggunakan Tabel Tabel Alias

Merubah nama nama kolom dengan nama nama tabel bisa sangat menghabiskan banyak waktu, khususnya jika nama nama tabel yang digunakan panjang. Anda dapat menggunakan table tabel alias daripada nama nama tabel. Seperti suatu kolom alias memberikan nama lain suatu kolom, suatu tabel alias memberikan nama lain suatu tabel. Tabel table alias membantu menjaga kode SQL menjadi lebih sederhana, sehingga menghemat penggunaan memory. Perhatikan bagaimana table table alias diidentifikasi pada klausa FROM dalam contoh. Suatu nama tabel disebutkan secara utuh, diikuti oleh spasi dan kemudian suatu tabel alias. Tabel EMPLOYEES diberikan suatu alias e, dan tabel DEPARTMENTS memiliki suatu alias d.

Ø  Pedoman pedoman

      Tabel table alias dapat mencapai 30 karakter panjangnya, tapi alias alias pendek lebih baik daripada alias alias panjang.

      Jika suatu tabel alias digunakan untuk nama tabel khusus pada suatu klausa FROM, maka table alias itu harus diganti dengan nama tabel sepanjang pernyataan SELECT.

      Tabel table alias sebaiknya  mempunyai arti.

      Tabel alias berlaku hanya pada pernyataan SELECT saat ini.

Ø  Membuat Join Join dengan Klausa ON

      Kondisi join untuk natural join pada dasarnya adalah suatu equijoin pada seluruh kolom kolom dengan nama yang sama.

      Gunakan klausa ON untuk menentukkan kondisi kondisi perubahan atau menentukan kolom kolom untuk digabungkan.

      Kondisi penggabungan adalah dipisahkan dari kondisi kondisi pencarian lain.

      Klausa ON membuat kode mudah untuk dipahami.

Ø  Menggabungkan Suatu Tabel dengan Tabel itu Sendiri

Kadangkala Anda perlu untuk menggabungkan suatu tabel dengan tabel itu sendiri. Untuk mencari nama manager dari masing masing pegawai, Anda perlu untuk menggabungkan tabel EMPLOYEES dengan tabel EMPLOYEES sendiri, atau melakukan suatu selfjoin. Sebagai contoh, untuk mencari nama manager dari Lorentz, Anda perlu untuk :

      Mencari nama Lorentz dalam tabel EMPLOYEES dengan mencarinya di kolom LAST_NAME.

      Mencari nomor manager dari  Lorentz dengan mencarinya di kolom MANAGER_ID. Nomor manager Lorentz adalah 103.

     Mencari nama manager dengan  EMPLOYEE_ID 103 dengan mencarinya di kolom

v  LAST_NAME. Hunold adalah pegawai dengan nomor pegawai 103, jadi Hunold adalah manager dari Lorentz. Pada proses ini, Anda mencarinya dalam tabel dua kali. Yang pertama mencari nama Lorentz di kolom LAST_NAME dan nilai dari 103 di MANAGER _ID. Kedua saat Anda mencari nomor 103 di kolom EMPLOYEE_ID dan mencari nama Hunold di kolom LAST_NAME.

Ø  Menerapkan Kondisi Kondisi Tambahan pada Suatu Join

Anda dapat menerapkan kondisi kondisi tambahan untuk join. Pada contoh ditunjukkan suatu join pada table table EMPLOYEES dan DEPARTMENTS dan, sebagai tambahan, ditampilkan hanya pegawai pegawai yang memiliki nomor manager 149. Untuk menambah penambahan kondisi kondisi pada klausa ON, Anda dapat menambahkan klausa klausa AND. Sebagai alternatif, Anda dapat menggunakan klausa WHERE untuk menerapkan tambahan kondisi kondisi :

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id

FROM employees e JOIN departments d ON (e.department_id = d.department_id)

WHERE e.manager_id = 149;

Ø  ThreeWay Joins

Suatu ThreeWay join adalah suatu join pada tiga buah tabel. Dalam acuan sintak SQL:1999, join join dilakukan dari kiri ke kanan. Jadi join pertama dilakukan EMPLOYEES JOIN DEPARTMENTS. Kondisi join pertama dapat merujuk kolom kolom pada tabel EMPLOYEES dan DEPARTMENTS tapi tidak bisa merujuk ke kolom kolom pada LOCATIONS. Kondisi join kedua dapat merujuk kolom kolom pada ketiga tabel.

Ø  Non Equijoins

Suatu nonequijoins adalah suatu kondisi join yang berisi suatu operator kesamaan (equality).Hubungan antara tabel EMPLOYEES dengan tabel JOB_GRADES adalah suatu contoh dari nonequijoins.Hubungan antara kedua kolom adalah bahwa kolom SALARY dalam tabel EMPLOYEES harus berada diantara nilai nilai di kolom kolom LOWEST_SALARY dan HIGEST_SALARY dalam tabel JOB_GRADES. Hubungan diperoleh menggunakan suatu operator lain daripada kesamaan (=).Penting untuk diperhatikan bahwa semua pegawai hanya muncul sekali saat query ini dijalankan. Tidak ada nama pegawai yang diulang dalam daftar. Ada dua alasan untuk itu :

     Tidak satupun baris dalam tabel  job grade yang berisi tingkatan tingkatan yang overlap. Maka, nilai penghasilan untuk seorang pegawai bisa berada hanya di antara salah satu barisbaris penghasilan terendah dan penghasilan tertinggi dalam tabel tingkat gaji.

 

      Semua penghasilan para pegawai  berada dalam batas batas yang disediakan oleh tabel job grade. Maka, tidak akan ada pegawai mendapat penghasilan kurang dari nilai terendah yang termasuk pada kolom LOWEST_SAL atau lebih dari nilai tertinggi yang termasuk pada kolom HIGHEST_SAL.

 

     Catatan: Kondisi kondisi lain (seperti <= dan >=) dapat digunakan, tapi lebih sederhana menggunakan BETWEEN. Ingat untuk menentukan lebih dulu nilai terendah dan kemudian nilai tertinggi saat menggunakan BETWEEN.

Ø  Mengembalikan Record Record yang Tidak Secara Langsung Sesuai dengan Outer Joins

Jika suatu baris tidak memenuhi kondisi join, baris tidak muncul pada hasil query. Sebagai contoh, dalam kondisi equijoin pada table table EMPLOYEES dan DEPARTMENTS, nomor department 190 tidak muncul karena tidak ada pegawai dengan nomor department tersebut dicatat dalam table EMPLOYEES. Malahan tampak 20 orang pegawai pada sekelompok hasil, Anda lihat 19 record. Untuk mengembalikan catatan departemen yang tidak mempunyai beberapa pegawai, Anda bisa gunakan outer join

Ø  INNER Join Versus OUTER Join

Menggabungkan table table dengan klausa klausa NATURAL JOIN, USING atau ON menghasilkan suatu inner join. Beberapa baris yang tidak sesuai tidak ditampilkan pada output. Untuk mengembalikan baris baris yang tidak sesuai, Anda bisa menggunakan suatu outer join. Suatu outer join mengembalikan semua baris baris yang memenuhi kondisi join dan juga mengembalikan beberapa atau semua baris baris tersebut dari satu tabel yang mana tidak ada baris baris dari table lain yang memenuhi kondisi join. Ada tiga tipe dari outer join:

    LEFT OUTER 

    RIGHT OUTER

    FULL OUTER

Ø  Cartesian Products

      Suatu Cartesian product terbentuk ketika :

Suatu kondisi join dihilangkan Suatu kondisi join tidak tepat Seluruh baris dalam tabel pertama digabungkan ke seluruh baris dalam tabel kedua

      Untuk menghindari suatu Cartesian product, selalu sertakan sebuah kondisi join yang tepat. Membuat Cross Join

     Klausa CROSS JOIN menghasilkan crossproduct dari dua tabel.

     CROSS JOIN disebut juga Cartesian product antara dua tabel.