Oracle SQL & PL/SQL – 5. Menampilkan Data dari Beberapa Tabel

Ketika kita memerlukan data dari beberapa tabel dalam database, dapat menggunakan kondisi join. Data dari satu tabel dapat digabungkan dengan data dari tabel lain berdasarkan nilai-nilai yang terdapat pada kolom-kolom yang berhubungan, yang disebut kolom-kolom primary key dan foreign key.

Tipe-tipe Join

• Equijoin
• Non-equijoin
• Outer join
• Self join

5.1. Cartesian Product

Cartesian Product adalah hasil dari join yang tidak valid, sehingga menyebabkan ditampilkannya seluruh kombinasi data dari tabel-tabel yang di-join-kan. Seluruh baris dari tabel pertama di-join-kan dengan seluruh baris pada tabel kedua.

Contoh:

SELECT name, last_name
FROM department, employee;

…

300 rows selected.

Tips

Selalu gunakan join yang valid dalam klausa WHERE, kecuali anda memang menginginkan hasil kombinasi tersebut.

5.2. Equijoin

Kita dapat menampilkan data-data dari kedua tabel dengan men-join-kan foreign key dari satu tabel dengan primary key dari tabel lainnya.

SELECT table.column, table.column…
FROM table1, table2
WHERE table1.column1 = table2.column2;

Contoh:

Menampilkan data karyawan dan departemen tempat karyawan tersebut bekerja.

SELECT employee.last_name, employee_dept_id, department.name
FROM employee, department
WHERE employee.dept_id = department.id;

Jika terdapat nama kolom yang sama pada tabel-tabel yang di-join-kan, kita harus menentukan dari table mana kolom tersebut berasal dengan menyebutkan nama tabelnya, namun sebaliknya jika tidak terdapat nama kolom yang sama pada tabel-tabel yang di-join-kan, kita tidak harus menentukan dari table mana kolom tersebut berasal.

Tips:

Nama tabel sebaiknya tetap digunakan meskipun diantara tabel-tabel yang di-join-kan tersebut tidak memiliki nama kolom yang sama karena dapat meningkatkan performa query.

5.2.1. Menambahkan Kondisi Pencarian

Kita dapat menambahkan kondisi pencarian dalam join dengan menggunakan operator AND atau OR sesuai kriteria yang diinginkan.

Contoh:

Menampilkan data karyawan yang memiliki nama akhir Velasquez dan departemen tempat karyawan tersebut bekerja.

SELECT employee.last_name, employee.dept_id, department.name
FROM employee, department
WHERE employee.dept_id = department.id
AND INITCAP(employee.last_name) = ‘Velasquez’;

5.3. Penggunaan Alias Tabel

Menuliskan kolom dengan nama tabel dapat memakan waktu, terlebih jika nama tabel cukup panjang. Kita dapat mengatasi hal ini dengan menggunakan alias tabel (memberikan nama lain kepada tabel dengan nama yang lebih pendek).

Contoh:

Menampilkan data karyawan dan departemen tempat karyawan tersebut bekerja.

SELECT e.last_name “Employee Name”, d.id “Dept ID”, d.name “Department Name”
FROM employee e, department d
WHERE e.dept_id = d.id;

Perhatikan query tersebut diatas, yang dimaksud alias tabel adalah e dan d yang masing-masing mewakili tabel karyawan dan departemen.

5.4. Non-Equijoin

Non-equijoin adalah relasi antara dua atau lebih tabel dimana nilai dari kolom-kolom yang dihubungkan tidak saling berhubungan secara langsung (tidak ada hubungan primary key dan foreign key). Dalam non-equijoin digunakan operator selain equal (=).

Contoh:

Menampilkan data karyawan dan tingkatan gajinya.

SELECT e.last_name, e.title, e.salary, s.grade
FROM employee e, salgrade s
WHERE e.salary BETWEEN s.losal AND s.hisal;

5.5. Outer Join

Sebuah query tidak akan menghasilkan data apapun apabila kondisi join tidak terpenuhi. Sebagai contoh, Anda dapat mencoba menampilkan nama-nama pelanggan dan sales representative dengan cara meng-equijoin-kan table Karyawan dan Pelanggan. Pelanggan yang tidak memiliki sales representative tidak akan ditampilkan karena kondisi join tidak terpenuhi.

Kita dapat menangani data yang hilang tersebut dengan menggunakan operator outer join (+), yang diletakkan pada sisi kolom dari tabel yang bertindak sebagai parent (induk).

Operator ini akan menampilkan hasil dari equijoin ditambah dengan hasil query yang tidak memenuhi kondisi equijoin tersebut.

Sintaks:

SELECT table.column, table.column…
FROM table1, table2
WHERE table1.column = table2.column(+);

Contoh:

Menampilkan data pelanggan dan nama sales representative dari pelanggan tersebut baik yang memiliki sales representative maupun tidak.

SELECT e.last_name, e.id, c.name
FROM employee e, customer c
WHERE c.sales_rep_id = e.id (+)
ORDER BY e.id;

5.6. Self Join

Self join adalah hubungan antara suatu tabel dengan tabel itu sendiri. Hal ini dimungkinkan dengan menggunakan alias tabel sehingga seolah-olah terdapat dua tabel.

Contoh:

Menampilkan data karyawan dan manajer dari karyawan tersebut.

SELECT e.last_name|| ‘ works for ‘ || m.last_name
FROM employee e, employee m
WHERE e.manager_id = m.id;

Perhatikan kasus diatas, karena manajer juga merupakan karyawan maka semua data yang diperlukan berasal dari satu tabel yaitu Employee. Dengan menggunakan alias tabel kita menciptakan seolah-olah terdapat dua tabel yaitu e (employee) dan m (manager), yang keduanya berasal dari tabel yang sama yaitu Employee.

5.7. Latihan

Tampilkan nama departemen dan nama belakang dari pegawai dalam satu query.

SELECT name, last_name
FROM employee, department;

Tampilkan nama belakang pegawai, kode dan nama departemen dimana pegawai tersebut bekerja.

SELECT e.last_name, e.dept_id, d.name
FROM employee e, department d
WHERE e.dept_id = d.id;

Tampilkan kode departemen, kode dan nama daerah tempat departemen tersebut berada.

SELECT d.id, r.id, r.name
FROM department d, region r
WHERE d.region_id = r.id;

Tampilkan pegawai dengan nama belakang Menchu, kode dan nama departemen tempat ia bekerja.

SELECT e.last_name, e.dept_id, d.name
FROM employee e, department d
WHERE e.dept_id = d.id AND e.last_name = ‘Menchu’;

Tampilkan nama belakang dan prosentase komisi masing pegawai, serta nama daerah tempat pegawai tersebut bekerja, khusus untuk pegawai yang mendapatkan komisi.

SELECT e.last_name, r.name, e.commission_pct
FROM employee e, department d, region r
WHERE e.dept_id = d.id AND d.region_id = r.id
AND e.commission_pct IS NOT NULL;

Tampilkan nama pelanggan, kode dan nama daerah tempat dimana pelanggan tersebut tinggal.

COLUMN “Customer Name” FORMAT A30

SELECT c.name “Customer Name”, c.region_id “Region ID”, r.name “Region Name”
FROM customer c, region r
WHERE c.region_id = r.id;

Tampilkan nama belakang pegawai dengan judul ENAME, jabatan pegawai dengan judul JOB, gaji bulanan pegawai dengan judul SAL, dan tingkatan gaji pegawai dengan judul GRADE, khusus untuk pegawai yang memiliki gaji diantara kolom losal dan hisal pada tabel S_SALGRADE.

SELECT e.last_name ENAME, e.title JOB, e.salary SAL, s.grade GRADE
FROM employee e, salgrade s
WHERE e.salary BETWEEN s.losal AND s.hisal;

Tampilkan nama belakang sales representative, kode pegawai, dan nama pelanggan termasuk pelanggan yang tidak memiliki sales representative, urut berdasarkan kode pegawai.

SELECT e.last_name, e.id, c.name
FROM employee e, customer c
WHERE e.id(+) = c.sales_rep_id
ORDER BY e.id;

Tampilkan nama belakang pegawai dan nama belakang atasan dari pegawai tersebut.

SELECT e.last_name | | ‘ works for ‘ | | m.last_name EMP
FROM employee e, employee m
WHERE e.manager_id = m.id;

Tampilkan nama belakang pegawai, kode dan nama departemen tempat pegawai tersebut bekerja.

SELECT e.last_name, e.dept_id, d.name
FROM employee e, department d
WHERE e.dept_id = d.id;

Tampilkan nama belakang pegawai, nama departemen tempat pegawai tersebut bekerja dan nama daerah tempat departemen tersebut berada.

SELECT e.last_name, d.name, r.name
FROM employee e, department d, region r
WHERE e.dept_id = d.id AND d.region_id = r.id;

Tampilkan nama belakang pegawai dan nama departemen tempat pegawai tersebut bekerja khusus untuk pegawai dengan nama belakang Smith.

SELECT e.last_name, d.name
FROM employee e, department d
WHERE e.dept_id = d.id AND e.last_name = ‘Smith’;

Tampilkan nama produk, kode produk dan kuantitas order dari produk tersebut, khusus untuk order dengan nomor 101.

SELECT p.name, p.id, i.quantity ORDERED
FROM product p, orders o, items i
WHERE p.id = i.product_id AND i.ord_id = o.id AND o.id = 101;

Tampilkan kode pelanggan dan nama belakang dari sales representative-nya urut berdasarkan nama belakang sales representative.

SELECT c.id, e.last_name
FROM customer c, employee e
WHERE c.sales_rep_id = e.id
ORDER BY e.last_name;

Tampilkan kode pelanggan, nama pelanggan dan nomor order dari pelanggan tersebut, termasuk pelanggan yang tidak memiliki nomor order.

SELECT c.id “Customer ID”, c.name “Customer Name”, o.id “Order ID”
FROM customer c, orders o
WHERE o.customer_id(+) = c.id;

Tampilkan nama belakang pegawai, kode pegawai, nama belakang atasan dari pegawai tersebut, serta kode dari atasan pegawai tersebut.

SELECT e.last_name EMP_NAME, e.id EMP_ID,m.last_name MGR_NAME, m.id MGR_ID
FROM employee e, employee m
WHERE e.manager_id = m.id;

Tampilkan kembali soal diatas termasuk pegawai yang tidak memiliki atasan.

SELECT e.last_name EMP_NAME, e.id EMP_ID, m.last_name MGR_NAME, m.id MGR_ID
FROM employee e, employee m
WHERE e.manager_id = m.id(+);

Tampilkan nama pelanggan, kode produk dan kuantitas order pelanggan terhadap produk tersebut khusus untuk pelanggan yang memiliki total order lebih dari 100000.

SELECT c.name “Customer”, i.product_id, i.quantity
FROM customer c, orders o, items i
WHERE c.id = o.customer_id AND o.id = i.ord_id AND o.total > 100000;