SQL Multi Table dan Select Bertingkat

Selamat Datang
       Hai pada kesempatan kali ini aku akan menjelaskan tentang Multi table dan select bertingkat. Yang teliti baca nya ya, enjoyy!

1. Select Multitable
          Pada pembuatan suatu aplikasi, terkadang memerlukan tampilan data yang tidak hanya berasal dari 1 (satu) tabel, namun bisa dari beberapa tabel sekaligus. Misalnya ingin menampilkan nama provinsi berikut dengan kota-kota di dalamnya. Dari contoh tersebut, kita harus dapat menggabungkan minimal dua tabel, yaitu provinsi dan kota. Atau menampilkan data karyawan lengkap dengan gajinya, sehingga harus menggabungkan tabel data karyawan
dan data gaji. Data yang tersimpan dalam database dapat bervariasi dalam hal jumlah dan tingkat kompleksitasnya.

Gambar 1.1 Data yang tersimpan dalam database

• Data Selection

           Adapun untuk melihat data (Selection), Query yang digunakan adalah SELECT diikuti beberapa pernyataan khusus yang berhubungan dengan tabel yang diseleksi. Pada dasarnya, SELECT merupakan perintah DML (Data Manipulation Language) untuk menghasilkan recordset dengan mengekstrak data dari database. Perintah SELECT ini sering dipakai dan memiliki bentuk yang kompleks. Bentuk sederhana dari SELECT adalah sebagai berikut.

SELECT select_list [INTO table_new] FROM table_source [WHERE
search_condition] [GROUP BY group_by_expression] [HAVING search_
condition] [ORDER BY order_expression [ASC | DESC] ]

Misalnya memiliki tabel nilai_siswa dengan field (kolom) nama, kelas, mapel, nilai, dan keterangan. Maka perintah SQL untuk menampilkan khusus pada field nama, mapel, dan nilai adalah sebagai berikut.

SELECT Nama, mapel, nilai FROM nilai_siswa

Namun jika ingin menampilkan seluruh field pada tabel Customer1 dapat menggunakan perintah asterik (*) sebagai berikut.

SELECT * FROM nilai_siswa

• Fungsi Select Multiple Table

          Select multiple table berfungsi untuk menampilkan data yang berhubungan dari dua buah tabel, misalnya ingin menampilkan data nm_siswa dan nm_kelas tiap siswa. Pada tabel siswa, nama kelas siswa ditunjukkan oleh id_kelas agar dapat melihat langsung nm_kelas tiap siswa, maka perintah select multiple table dapat digunakan. Pada syntax from nama tabel siswa ditulis lebih dahulu dibandingkan nama tabel tb_kelas.
       Hal tersebut menyebabkan data ascending menurut table tb_kelas. Misalnya data dalam tb_kelas pada urutan teratas adalah data BSD. Jika nama table tb_kelas ditulis lebih dulu maka data yang ditampilkan akan ascending sesuai dengan tabel siswa. Misalnya data tabel siswa pada urutan teratas adalah Yurizka.

Gambar 1.2 Data tabel siswa

Di dalam suatu RDBMS menjadi suatu kewajaran jika dalam satu database dapat
terdiri dari beberapa tabel, di mana masing-masing tabel tersebut berhubungan (relasi) satu sama lain. Relasi antartabel dapat berupa relasi 1-1, 1-M, atau M-N. Misalnya tabel customer1 berhubungan dengan dt_pesan, dt_pesan dengan barang, dan lain-lain. Namun adakalanya memerlukan tampilan data yang tidak hanya berasal dari 1 (satu) tabel, tetapi bisa dari beberapa tabel sekaligus. Hal ini dapat dilihat pada gambar pemodelan data konseptual (class diagram). Misalnya dari class diagram ingin menampilkan nama customer1 beserta transaksi yang pernah dilakukannya. Oleh sebab itu, bisa dilakukan dengan menggabungkan minimal dua tabel, yaitu customer1 dan dt_pesan. Tabel customer1 hanya menampilkan id, nama, dan e-mail sebagai berikut.

+-----------+------------------------+-----------------------------------------+
| id_cst  | nm_customer  | email                                      |
+-----------+------------------------+-----------------------------------------+
| CST01 | Nanik Ramini  | nramini@gmail.com           |
| CST02 | Zaskia Sari       | zask.sari@gmail.com          |
| CST03 | Herman Mario| her_mario19@yahoo.com |
| CST04 | Rangga Mardi  | nggamard@msn.com         |
+-----------+------------------------+-----------------------------------------+
Sedangkan tabel dt_pesan hanya menampilkan id_dt_pesan, id_cst, dan tgldt_pesan 
sebagai berikut. 
+-------------------+-----------+-------------------+
| id_dt_pesan| id_cst| tgldt_pesan  |
+-------------------+-----------+-------------------+
| 1                    | CST01 | 2019-04-02 |
| 2                    | CST02 | 2019-04-05 |
| 3                    | CST02 | 2019-04-10 |
| 4                    | CST04 | 2019-01-20 |
| 5                    | CST01 | 2018-12-14 |
+-------------------+-----------+------------------+

• Perintah Join

          Pada SQL terdapat sebuah perintah join yang berfungsi untuk menghubungkan tabel yang satu dengan tabel yang lain yang saling berhubungan atau berelasi. Tujuannya adalah untuk menampilkan beberapa data dari table yang berbeda dengan menggunakan satu perintah.
 Perintah Join dikategorikan menjadi sebagai berikut:
          a. Inner Join
              Data yang akan ditampilkan pada perintah inner join hanya data yang memiliki pasangan saja, sedangkan data pada tabel yang tidak memiliki sebuah kesamaan maka data tersebut tidak akan ditampilkan. Inner Join identik dengan sebuah perintah yang digunakan untuk menampilkan sebuah data atau record dengan menghubungkan dua tabel atau lebih dalam satu perintah. 

Gambar 1.3 Diagram venn untuk inner join

Format penulisan yang digunakan adalah sebagai berikut. 

SELECT * FROM table_1 INNER JOIN table_2 ON table_1.PK = table_2.FK
SELECT * FROM POST INNER JOIN category on post.category_id=category.id_category;

             Tabel akan digabungkan dua arah menggunakan Inner Join, sehingga tidak ada data yang NULL di satu sisi. Misalnya menggabungkan tabel customer1 dan dt_pesan dengan tujuan untuk menampilkan daftar customer1 yang pernah melakukan pemesanan (transaksi). 
Cara penggabungan dengan Inner Join menggunakan bentuk umum sebagai 
berikut:

SELECT table_1.*, table_2.* FROM table_1 INNER JOIN table_2 ON table_1.PK=table_2.FK;

Perintah SQL untuk menggabungkan tabel customer1 dan dt_pesan adalah 
sebagai berikut:

SELECT customer1.id_cst, customer1.nm_customer1, dt_pesan.id_dt_pesan, dt_pesan.tgldt_pesan FROM customer1 INNER JOIN dt_pesan ON customer1.id_cst=dt_pesan.id_cst;


Hasilnya adalah sebagai berikut:
+-----------+-----------------------+--------------------+------------------+
| id_cst  | nm_customer1 |id_dt_pesan|tgldt_pesan |
+-----------+-----------------------+--------------------+------------------+
| CST01 | Nanik Ramini | 1                     | 2019-04-02 |
| CST01 | Nanik Ramini | 5                     | 2018-12-14 |
| CST02 | Zaskia Sari      | 2                     | 2019-04-05 |
| CST02 | Zaskia Sari      | 3                     | 2019-04-10 |
| CST04 | Rangga Mardi | 4                     | 2019-01-20 |
+-----------+-----------------------+--------------------+------------------+



            b. Outer Join
            Tabel yang akan digabungkan dengan Outer Join menjadi satu arah, sehingga 
terdapat data NULL (kosong) di satu sisi. Misalnya menggabungkan tabel 
customer1 dan dt_pesan untuk menampilkan daftar customer1 yang pernah 
melakukan pemesanan (transaksi). Secara umum, Outer Join terbagi menjadi 
sebagai berikut. 

              1). Left Join
LEFT OUTER JOIN dapat ditulis menggunakan dua cara, yaitu dengan klausa LEFT OUTER JOIN atau cukup dengan klausa LEFT JOIN saja. Left Join (left outer join) identik dengan suatu perintah yang digunakan untuk mengembalikan semua nilai dari tabel kiri ditambah dengan nilai dari tabel kanan yang sesuai (atau null jika tidak ada nilai yang sesuai). 

Gambar 1.4 Left (outer) Join

Bentuk umum Left Join adalah sebagai berikut:

SELECT table_1.*, table_2.* FROM table_1 LEFT JOIN table_2 ON table_1.PK=table_2.FK;

Format penulisan query yang digunakan adalah sebagai berikut:

SELECT * FROM table_1 LEFT JOIN table_2 ON table_1.PK = table_2.FK
SELECT * FROM POST LEFT JOIN category on post.category_id=category.id_category;

Perintah SQL untuk menggabungkan tabel customer1 dan dt_pesan 
menggunakan Left Join adalah sebagai berikut:

SELECT customer1.id_cst, customer1.nm_customer1, dt_pesan.id_dt_pesan, dt_pesan.tgldt_pesan FROM customer1 LEFT JOIN dt_pesan ON customer1.id_cst=dt_pesan.id_cst;

Hasilnya adalah sebagai berikut:
+-----------+------------------------+-------------------+------------------+
| id_cst  | nm_customer1 |id_dt_pesan|tgldt_pesan |
+-----------+-------------------------+------------------+------------------+
| CST01 | Nanik Ramini   | 1                   | 2019-04-02 |
| CST01 | Nanik Ramini   | 5                   | 2018-12-14 |
| CST02 | Zaskia Sari        | 2                   | 2019-04-05 |
| CST02 | Zaskia Sari        | 3                   | 2019-04-10 |
| CST03 | Herman Mario | NULL           | NULL          |
| CST04 | Rangga Mardi   | 4                   | 2019-01-20 |
+-----------+-------------------------+------------------+------------------+

Penggunaan Left Join juga akan menampilkan data customer1 dengan id CST03, walau pun customer1 tersebut belum pernah bertransaksi. Pada kolom id_dt_pesan dan tgldt_pesan untuk customer1 CST03 isinya NULL yang berarti pada tabel dt_pesan untuk customer1 tersebut benar￾benar tidak ada. Pada dasarnya, fungsi dari Left Join hampir sama dengan Inner Join dan Right Join. Jika pada Right Join akan membuat sebuah parameter di sebelah kanan, maka pada Left Join justru akan membuat sebuah parameter dari tabel sebelah kiri, dan jika ada data atau record 
yang kosong atau tidak berelasi akan berisi NULL di sebelah kanan.

             2) Right Join
                 Secara mendasar, fungsi dari Right Join hampir sama dengan Inner Join, hanya pada Right Join akan membuat sebuah parameter pada sebelah kanan jika 
data pada table terdapat data atau record yang kosong atau tidak berelasi  maka akan berisi NULL.

Gambar 1.5 Diagram venn untuk Right Join

Bentuk umum Right Join adalah sebagai berikut:

SELECT * FROM table_1 RIGHT JOIN table_2 ON table_1.PK = table_2.FK
SELECT * FROM POST RIGHT JOIN category on post.category_id=category.id_category;

Perintah SQL untuk menggabungkan tabel customer1 dan dt_pesan menggunakan Right Join adalah sebagai berikut:

SELECT customer1.id_cst, customer1.nm_customer1, dt_pesan.id_dt_pesan, dt_pesan.tgldt_pesan FROM customer1 RIGHT JOIN dt_pesan ON customer1.id_cst=dt_pesan.id_cst;

Hasilnya adalah sebagai berikut:
+-----------+------------------------+--------------------+-------------------+
| id_cst  | nm_customer1|id_dt_pesan   |tgldt_pesan |
+-----------+------------------------+--------------------+-------------------+
| CST01 | Nanik Ramini  | 1                     | 2019-04-02  |
| CST02 | Zaskia Sari       | 2                     | 2019-04-05  |
| CST02 | Zaskia Sari       | 3                     | 2019-04-10  |
| CST04 | Rangga Mardi  | 4                     | 2019-01-20  |
| CST01 | Nanik Ramini  | 5                     | 2018-12-14  |
+-----------+------------------------+--------------------+-------------------+

Tabel yang menjadi acuan pada Right Join adalah tabel sebelah kanan  (tabel dt_pesan), sehingga semua isi tabel dt_pesan akan ditampilkan. Meskipun data customer1 tidak ada di tabel customer1, maka isi tabel dt_pesan tetap ditampilkan.

             c. Full Join
                 Penggunaan Full Join akan menghasilkan baris data jika ada data yang sama pada salah satu tabel. Bentuk umum Full Join adalah sebagai berikut:

SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2 column_name

Perhatikan bentuk implementasi sebagai berikut:

SELECT reg.nama,reg.email,order.no_order FROM reg FULL JOIN 
order ON reg.id=order.id ORDER BY reg.nama

Adapun bentuk diagram venn untuk Full join adalah sebagai berikut:

Gambar 1.6 Diagram venn untuk Full Join

2.Select Bertingkat

   Subquery adalah perintah SELECT yang berada di dalam perintah SQL lain. Subquery sangat berguna ketika kita ingin menampilkan data dengan kondisi yang bergantung pada data di dalam table itu sendiri.

• Kegunaan-kegunaan  Subquery dalam memanipulasi data:

§  Meng-copy data dari satu tabel ke tabel lain

§  Menerima data dari inline view

§  Mengambil data dari tabel lain untuk kemudian di update ke tabel yang dituju

§  Menghapus baris dari satu tabel berdasarkan baris dari tabel lain.

• ATURAN SUB QUERY

1. Klausa ORDER BY tidak boleh digunakan .di subquery. ORDER BY dapat digunakan ,di pernyataan SELECT luar.
2. Klausa subquery SELECT harus beisi satu nama kolom tunggal atau ekspresi kecuali untuk subquery-subquery menggunakan kata kunci EXIST.
3. secara default, nama-nama kolom di subquery mengacu ke nama table diklausa FROM di query lebih luar dengan mengkualifikasi nama kolom
4. Saat subquery adalah salah satu dua operan dilibatkan di pembandingan, subquery harus muncul di sisi kanan pembandingan.
5. Subquery tdak boleh digunakan sebagai operan di ekspresi.

Sintaks:
SELECT select_list FROM table WHERE expr operator ( SELECT select_list FROM table );

Contoh:
Menampilkan data karyawan yang bekerja satu departemen dengan Biri.
SELECT last_name, title FROM employee WHERE dept_id = ( SELECT dept_id FROM employee WHERE UPPER(last_name) = ‘BIRI’ );

1. Operator EXIST dan NOT EXIST

  Kata kunci EXIST dan NOT EXIST dirancang hanya untuk digunakan di subquery. Kata kunci-kata kunci ini menghasilkan nilai TRUE atau FALSE EXIST akan mengirim nilai TRUE jika dan hanya jika terdapat sedikitnya satu baris di table hasil yang dikirim oleh subquery. EXIST mengirim nilai FALSE jika subquery mengirm table kosong NOT EXIST kebalian dan EXIST.

Karena EXIST dan NOT EXIST hanya memeriksa keberadaan baris-baris di table hasil subquery.

Contonya Penggunaan EXIST

Daftarkan semua staf yang bekerja dikantor cabang beralamat di jalan “Tamansari 81” :

SELECT IDStaf , namaDepan, namaBelakang, pangkat, gPokok FROM Staf s WHERE EXIST (SELECT * FROM KantorCabang k, StafKCabang sk WHERE s.IDStaf=sk.IDStafAND sk.IDKCabang =k.IDKCabang AND k.jalan=’tamansari 81’);

    

    3. Operator Any(Some)

        Operator Any (some) hampir sama penggunaannya seperti Exists. Tetapi operator relasi yang digunakan biasanya selain = (sama dengan). hal tersebut disebabkan apabila operator relasi = yang digunakan, maka sebetulnya fungsi operator Any (some) sama seperti operator IN, sehingga kondisi seperti itu tidak dianjurkan karena lebih mudah pemahamannya apabila menggunakan operator IN.

Contoh :

Akan menampilkan daftar nama pegawai dan gaji yang gajinya tidak paling sedikit :

select nama, gaji from pegawai where gaji > any (select gaji from pegawai); 

   3. Operator All

          Operator all digunakan untuk melakukan pembandingan dengan sub query. Kondisi dengan all menghasilkan nilai benar jika pembandingan menghasilkan benar untuk setiap nilai dalam sub query.
Contoh :

Akan menampilkan nama dan gaji pegawai yang gajinya lebih rendah daripada semua pegawai yang pekerjaannya 'SALESMAN' :

select nama where gaji < all (select gaji from pegawai where pekerjaan = 'SALESMAN'); 

Sub query (subselect) hanya dapat digunakan untuk MySQL versi 3.24, Sehingga parintah-perintah diatas tidak dapat dijalankan di MySQL ini(versi 3.23). Tetapi, dengan menggunakan option LEFT JOIN, masalah-masalah diatas dapat diselesaikan.

Contoh :

select * from score where event_id in (select even_id from even where type = 'T'); 

Apabila dituliskan dengan cara lain menjadi :

select score.* from score, event where score.even_id = even.even_id and type = 'T';

  4. Multiple Rows Subquery

                 Multiple Row Subquery adalah subquery yang menghasilkan lebih dari satu baris data. Untuk multiple row subquery ini yang digunakan adalah operator pembanding IN, ANY atau ALL

Contoh:
Menampilkan data karyawan yang bekerja pada departemen Finance atau pada region 2.

SELECT last_name, first_name, title FROM employee WHERE dept_id IN ( SELECT id FROM department WHERE name = ‘Finance’ OR region_id = 2 );

Contoh:
Menampilkan nama, gaji, dan nomer department dari pegawai yang gajinya sama dengan gaji minimum pada suatu department.

SELECT ename, sal, deptno FROMENP WHERE sal IN (SELECT MIN (sal) FROM ENP GROUP BY deptno)

• Single Row Subquery

          Single row subquery memberikan hasil hanya satu baris pada bagian subquery. Untuk single row subquery ini yang digunakan adalah operator pembanding: , >, >=, <, <= atau <>

Contoh:
Menampilkan data karyawan yang memiliki jabatan sama dengan Smith.
SELECT last_name, title FROM employee WHERE title = ( SELECT title FROM employee WHERE last_name = ‘Smith’ );

Kita dapat menampilkan data dari query utama dengan menggunakan fungsi grup (group function) untuk menghasilkan satu baris data.

Contoh:
Menampilkan data karyawan yang memiliki gaji dibawah rata-rata.
SELECT last_name, title, salary FROM employee WHERE salary < ( SELECT AVG(salary) FROM employee );

• Kesalahan dalam Subquery

         Kesalahan umum dalam subquery adalah lebih dari satu baris data dihasilkan untuk single row subquery.
Contoh:
Subquery ini menghasilkan lebih dari satu baris data dan menggunakan single row operator. Kita dapat membetulkan kesalahan ini dengan mengubah = menjadi IN.

SELECT last_name, first_name, title FROM employee WHERE dept_id = ( SELECT id FROM department WHERE name = ‘Finance’ OR region_id = 2 );

• Klausa HAVING

Kita dapat menggunakan subquery tidak hanya dalam klausa WHERE, namun juga klausa HAVING.
Contoh:
Menampilkan data departemen yang memiliki rata-rata gaji diatas rata-rata gaji departemen 32.

SELECT dept_id, AVG (salary) FROM employee GROUP BY dept_id HAVING AVG(salary) > ( SELECT AVG(salary) FROM employee WHERE dept_id = 32 );

Menampilkan jabatan dengan rata-rata gaji terkecil.

SELECT title, AVG (salary) FROM employee GROUP BY title HAVING AVG (salary) = ((SELECT MIN(AVG(salary))FROM employeeGROUP BY title );

Sekian dari saya, semoga bermanfaat. Terimakasih telah berkunjung, jangan lupa share!