TABLE JOIN SQL SERVER 2005
advertisement
i TABLE JOIN SQL SERVER 2005 Disusun untuk : Membuat Laporan Table Join SQL SERVER 2005 Oleh : CHOIRUL FADHOLANII NIM : 0931140068 POLITEKNIK NEGERI MALANG Tahun 2011 1 DAFTAR ISI 2 DAFTAR GAMBAR 1 1. PENDAHULUAN 1.1. Definisi SQL Server Microsoft SQL Server adalah aplikasi komputer yang digunakan untuk membuat aplikasi desktop, enterprise, dan web aplikasi yang berbasis database. Hal ini digunakan pada tingkat yang berbeda dan dengan berbagai tujuan [http://www.fianga.com/]. Salah satu fasilitas utama salam SQL Server adalah penggunaan query. query adalah pertanyaan atau permintaan informasi tertentu dari sebuah basisdata yang ditulis dalam format tertentu. Fasilitas query merupakan fasilitas yang digunakan untuk menjalankan perintah SQL. SQL singkatan dari Structured Query language. SQL adalah bahasa komputer yang standar untuk mengakses dan memanipulasi database. Dengan menggunakan perintah SQL, kita dapat menampilkan data dari sebuah database sesuai dengan perintah dan kondisi yang ditulis di dalam query SQL. Beberapa perintah SQL yang harus diketahui antara lain : a. CREATE DATABASE, untuk membuat sebuah database. b. DROP DATABASE, untuk menghapus sebuah database. c. CREATE TABLE, untuk membuat sebuah tabel. d. ALTER TABLE, untuk memodifikasi sebuah tabel. e. DROP TABLE, untuk menghapus sebuah tabel. f. SELECT, untuk menampilkan data dari database. g. UPDATE, untuk memodifikasi data dari database. h. INSERT INTO, untuk menambah data di database. i. DELETE, untuk menghapus data dari database Pada makalah ini yang akan dibahas adalah Table Join yang merupakan bagian dari perintah SQL SELECT. 2 2. ISI Pada bagian ini akan dijelaskan mengenai Table join yang merupakan salah satu bagian dari query select dengan fungsi dan manfaat yang berbeda. Spesifikasi Hardware dan software yang digunakan dalam perangkat ini adalah sebagai berikut : a. Notebook dengan spesifikasi sebagai berikut : Gambar 2.1 Spesifikasi hardware yang digunakan. b. SQL Server 2005 Gambar 2.2 Software pengujian yang digunakan. 3 Pada Table join terdapat beberapa sub bab yang akan dijelaskan secara rinci di masing – masing bagian. Berikut ini adalah bagian dari materi Table join : 2.1. SQL Server 2005 join types fall into three categories: inner, outer, and cross. Pada bagian ini akan dijelaskan mengenai kategori tipe Table Join 1 pada SQL Server 2005. SQL adalah database relasional, artinya dalam satu kelompok atau record data dapat dibagi menjadi beberapa tabel. Namun pemisahan tersebut nantinya harus bias digabungkan kembali sehingga pemisahannya harus dilakukan secara logis [ Sudharma S, 2010, hal.64 ] . Karena itu butuh hubungan khusus antar tabel. Sebagai contoh tabel mahasiswa akan berkaitan erat dengan tabel mata kuliah dan tabel KHS (Kartu Hasil Studi). Untuk membuat itu digunakan salah satu fasilitas SQL yaitu dengan menggunakan Join Table. Join table adalah salah satu fasilitas dari SQL yang digunakan untuk memanggil dan menggabungkan data dari beberapa tabel yang telah dibuat. SQL Server 2005 memiliki tiga tipe kategori dalam menggabungkan sebuah table atau yang lebih dikenal dengan Join Table. Tiga kategori tersebut adalah Inner Join, Outer Join dan Cross Join [ Sack Joseph, 2008, hal.29 ]. 2.1.1. Menggunakan Inner Join Kategori pertama dalam menggabungkan tabel adalah dengan menggunakan Inner Join. Inner join bekerja dengan menggunakan keyword INNER JOIN dan beroperasi dengan cara mencocokkan nilai – nilai yang sama antara kedua tabel. 1 Tabel Join. http://www.java2s.com/Tutorial/SQLServer/0080__Table Join/SQLServer2005jointypesfallintothreecategoriesinnerouterandcross.htm 4 Gambar 2.3 Ilustrasi penggunaan Inner Join. Inner Join mengembalikan semua baris dari kedua tabel dimana terdapat kecocokan atau merupakan irisan antara kedua tabel. Jika ada yang tidak cocok dalam tabel – tabel tersebut,maka baris tersebut tidak akan ditampilkan. 2.1.2. Menggunakan Outer Join Kategori kedua dalam menggabungkan tabel adalah dengan menggunakan Outer Join. Dalam menggunakan Outer Join dikenal dengan tiga cara penggabungan tabel yaitu Left Outer Join, Right Outer Join, dan Full Outer Join. 2.1.2.1. Left Outer Join Gambar 2.4 Ilustrasi penggunaan Left Outer Join. 5 Left Outer Join digunakan apabila ingin menampilkan semua baris dari tabel pertama baik yang memiliki kecocokan sesuai seleksi kondisi yang diberikan dengan tabel kedua maupun tidak dan menampilkan irisan antara tabel pertama dan tabel kedua. 2.1.2.2. Right Outer Join Gambar 2.5 Ilustrasi penggunaan Right Outer Join. Right Outer Join digunakan apabila ingin menampilkan semua baris dari tabel kedua baik yang memiliki kecocokan sesuai seleksi kondisi yang diberikan dengan tabel pertama maupun tidak dan menampilkan irisan antara tabel pertama dan tabel kedua. 2.1.2.3. Full Outer Join Gambar 2.6 Ilustrasi penggunaan Full Outer Join. 6 Full Outer Join digunakan apabila ingin menampilkan semua baris dari tabel pertama dan tabel kedua baik yang memiliki kecocokan sesuai seleksi kondisi yang diberikan dengan maupun yang tidak memiliki kecocokan. 2.1.3. Menggunakan Cross Join Cross join atau yang disebut juga sebagai Cartesian Product 2. Bila menggunakan cross join, maka hasil dari cross join akan menciptakan hasil yang didasarkan pada semua kemungkinan kombinasi baris dalam kedua set data. Dengan demikian, jumlah baris yang dikembalikan adalah N ✕ M , dimana N adalah jumlah baris dalam kumpulan data A dan M jumlah baris dalam kumpulan data B. Untuk lebih memahami, perhatikan gambar dibawah ini. Gambar 2.7 Ilustrasi penggunaan Cross Join. 2 Cartesian Product. http:// beginner-sql-tutorial.com/sql-joins.htm 7 2.2. Selecting authors and titles using only joins Pada bagian ini akan dijelaskan bagaimana menampilkan data author dan titles menggunakan SQL join. Tabel yang digunakan adalah tabel author (Gambar 2.13) dan tabel titles (Gambar 2.11). Antara kedua tabel tersebut ada tabel penghubung yang diberi nama tabel titleauthor (Gambar 2.15). Query dari kedua tabel tersebut adalah sebagai berikut : Gambar 2.8Query untuk membuat tabel titles. Gambar 2.9 Query untuk membuat tabel authors. Kemudian dilakukan insert data pada dua tabel berikut seperti pada query dibawah ini : Langkah pertama adalah mengisi tabel titles seperti pada query berikut : Gambar 2.10 Query untuk insert data pada tabel titles. 8 Hasil insert data pada tabel titles adalah sebagai berikut : Gambar 2.11 Hasil insert data pada tabel titles. Langkah kedua adalah mengisi tabel authors seperti pada query berikut : Gambar 2.12 Query untuk insert data pada tabel authors. Hasil insert data pada tabel authors adalah sebagai berikut : Gambar 2.13 Hasil insert data pada tabel authors. Langkah ketiga adalah membuat tabel penghubung yang diberinama titleauthor. Query untuk membuat tabel titleauthor beserta insert data pada tabel tersebut ditunjukkan pada gambar dibawah ini. 9 Gambar 2.14 Query untuk membuat tabel titleauthor. Gambar 2.15 Hasil insert data pada tabel titleauthor. Apabila data yang ingin ditampilkan adalah informasi mengenai nama author dan title maka perintah SQL yang digunakan adalah sebagai berikut: Gambar 2.16 Query untuk menampilkan data authors dan title. Dan hasil eksekusi query diatas adalah sebagai berikut : Gambar 2.17 Hasil eksekusi query menampilkan data authors dan title. 10 Data yang diambil dari perintah diatas adalah au_lname dan title yang berada pada tabel author dengan melakukan penggabungan data dengan acuan kolom au_id dari tabel titleauthor dengan kolom au_id dari tabel titleauthor dan acuan kolom title_id dari tabel titles dengan kolom title_id dari tabeltitleauthor. Query SQL yang dijalankan diatas menjelaskan bagaimana menampilkan data dari beberapa tabel dengan menggunakan syntax join. 2.3. A SELECT statement that joins the Bankers and Billings tables Pada bagian ini akan dijelaskan bagaimana menampilkan data dari dua buah tabel yaitu tabel Bankers (Gambar 2.20) dan tabel Billings (Gambar 2.23). Data yang diambil berasal dari kolom BillingNumber di tabel Billings dan kolom BankerName di tabel Bankers. Langkah pertama adalah menuliskan perintah query SQL untuk membuat tabel Bankersdan tabel Billings adalah sebagai berikut : Gambar 2.18 Query untuk membuat tabel Bankers. Gambar 2.19 Query untuk insert data pada tabel Bankers. 11 Gambar 2.20 Tabel Bankers. Gambar 2.21 Query untuk membuat tabel billings. Gambar 2.22 Query untuk insert data pada tabel billings. Gambar 2.23 Tabel Billings. 12 Sebagai contoh untuk menampilkan data pada kedua tabel diatas dapat menggunakan perintah berikut : Gambar 2.24 Query menampilkan data dari kedua tabel. Data yang diambil merupakan penggabungan dari dua buah tabel yaitu Tabel Bankers dan tabel Bilings dengan melakukan pencocokan data berdasarkan data yang terdapat pada kolom BankerID di tabel Bankers dan data dari kolom BankerID yang terdapat pada tabel Billings. Untuk mengetahui hasil dari penggunaan perintah SQL tersebut dapat dilihat pada gambar dibawah ini : Gambar 2.25 Tabel Billings. 2.4. Joining Tables in the WHERE Clause (not ANSI standard) Apabila ingin menampilkan data dari satu tabel atau beberapa tabel umumnya menggunakan klausa WHERE. Klausa WHERE akan memberikan batasan pada query untuk menampilkan data hanya dari kondisi yang diberikan. Contoh penggunakan klausa WHERE adalah sebagai berikut : 13 Sebagai contoh data yang digunakan sebagai contoh digunakan tabel employee dan tabel title dengan data sebagai berikut ini : Gambar 2.26 Tabel Employee. Gambar 2.27 Tabel Title. Kemudian diberikan perintah query untuk menampilkan data dari kedua tabel tersebut. Data yang ingin ditampilkan adalah kolom first_name dan job_title. Maka dapat menggunakan query sebagai berikut : Gambar 2.28 Query dengan klausa WHERE. 14 Dan hasil eksekusi query tersebut adalah sebagai berikut : Gambar 2.29 Hasil eksekusi query dengan klausa WHERE. 2.5. Using the GROUP BY Clause Pada bagian ini akan dijelaskan mengenai fungsi dari penggunaan perintah GROUP BY. Dengan menggunakan GROUP BY, hasil dari query dapat dikelompokkan berdasarkan suatu kriteria tertentu dimana data dikelompokkan berdasarkan kolom-kolom tertentu. Untuk mengetahui penggunaan GROUP BY akan dilakukan ujicoba pada dua tabel yaitu tabel Classification dan Tabel CD. Kedua tabel yang akan digunakan sebagai data ujicoba tersebut dapat dilihat pada gambar dibawah ini : Gambar 2.30 Tabel Classification. 15 Gambar 2.31 Tabel CD Kemudian ujicoba yang dilakukan adalah dengan menuliskan query untuk menampilkan informasi tentang class_ID, classification dan Total Offering, maka dapat digunakan query sebagai berikut : Gambar 2.32 Query dengan klausa GROUP BY. 16 Dan hasil eksekusi query diatas adalah sebagai berikut : Gambar 2.33 Hasil eksekusi query dengan klausa GROUP BY. Dengan menggunakan klausa GROUP BY kita dapat menampilkan kategori berdasarkan kolom atau kategori tertentu sesuai dengan klausa yang ingin diberikan. 2.6. Joining three tables. Pada bagian ini akan dijelaskan bagaimana cara melakukan Table Join dengan menggunakan tiga tabel. Tabel yang akan digunakan adalah tabel Titles, tabel Author dan tabel titleauthor. Untuk tabel titles dapat dilihat pada gambar gambar 2.11, untuk tabel author dapat dilihat pada gambar 2.13 , dan untuk tabel titleauthor dapat dilihat pada gambar 2.15. Dari ketiga tabel tersebut akan ditampilkan data dari ketiga tabel dengan klausa dimana au_lname = „Jack‟. Maka untuk menyelesaikan masalah tersebut dapat menggunakan perintah SQL berikut ini : Gambar 2.34 Query join dari tiga tabel. Hasil eksekusi query tersebut adalah sebagai berikut ini : Gambar 2.35 Hasil eksekusi query join tiga tabel. 17 2.7. Joins and Subqueries Pada bagian ini akan dijelaskan mengenai subquery. Subquery adalah query SELECT yang mengembalikan nilai tunggal dan berada di dalam SELECT, INSERT, UPDATE, atau DELETE, atau di dalam subquery lain. Sebagai contoh data yang akan ditampilkan adalah title dan price sesuai relasi dengan tabel sales (Gambar 2.36) dan qty dari tabel sales lebih besar dari 30. Pada ujicoba yang dilakukan dengan menggunakan tabel Titles dan Tabel Sales. Untuk informasi mengenai tabel titles dapat dilihat pada gambar 2.11 dan untuk tabel sales dapat dilihat pada gambar berikut ini : Gambar 2.36 Tabel Sales. Kemudian digunakan query untuk menampilkan qty dari tabel sales yang nilainya lebih dari 30. Maka dapat menggunakan query dibawah ini : Gambar 2.37 Query dengan menggunakan subqueries. Hasil dari eksekusi query tersebut adalah sebagai berikut : Gambar 2.38 Hasil eksekusi query dengan menggunakan subquery. 18 2.8. Left and Right Outer Joins Seperti pada penjelasan pada bab pertama, Left Outer Join digunakan apabila ingin menampilkan semua baris dari tabel pertama baik yang memiliki kecocokan sesuai seleksi kondisi yang diberikan dengan tabel kedua maupun tidak dan menampilkan irisan antara tabel pertama dan tabel kedua dan Right Outer Join digunakan apabila ingin menampilkan semua baris dari tabel kedua baik yang memiliki kecocokan sesuai seleksi kondisi yang diberikan dengan tabel pertama maupun tidak dan menampilkan irisan antara tabel pertama dan tabel kedua. Untuk menjelaskan kegunaan dari kedua jenis outer jois tersebut digunakan tabel titles (gambar 2.11) dan tabel sales (gambar 2.36). Percobaan query Left Outer Join : Gambar 2.39 Percobaan query Left Outer Join. Hasil eksekusi query Left Outer Join : Gambar 2.40 Hasil eksekusi query Left Outer Join. 19 Percobaan query Right Outer Join : Gambar 2.39 Percobaan query Right Outer Join. Hasil eksekusi query Right Outer Join : Gambar 2.40 Hasil eksekusi query Right Outer Join. Dari percobaan tersebut didapatkan hasil tampilan data dari kedua tabel sesuai dengan definisi yang telah dijelaskan pada masing – masing jenis outer joins. 2.9. Joining Tables in the FROM Clause (ANSI standard) Pada bagian ini dijelaskan bagaimana fungsi dari klausa FROM yang digunakan dalam perintah SQL khususnya dalam pencarian data. Klausa FROM umumnya adalah merujuk pada data atau tabel yang akan dipanggil. Klausa dapat digunakan dengan mengkombinasikan dengan join. Untuk mengetahui bagaimana penggunaan FROM dalam join tabel akan dilakukan ujicoba pada tabel berikut ini : 20 Ujicoba dilakukan dengan menggunakan tabel Employee (Gambar 2.26) dan tabel Title (Gambar 2.27). Data yang ingin ditampilkan adalah first_name dan job_title. Maka dapat digunakan perintah SQL seperti berikut ini : Gambar 2.41 Query dengan menggunakan klausa FROM. Hasil dari eksekusi query diatas adalah sebagai berikut : Gambar 2.42 Hasil eksekusi query dengan menggunakan klausa FROM. 2.10. Table Aliasing Pada bagian ini akan dijelaskan mengenai fungsi dari aliasing yaitu mengganti nama kolom atau tabel dengan nama lain yang lebih mudah dipahami sehingga sangat membentu ketika akan dilakukan perintah SQL dengan melibatkan tabel atau kolom yang sangat banyak. Untuk mengetahui bagaimana penggunaan Aliasing akan dilakukan ujicoba pada tabel berikut ini : 21 Ujicoba dilakukan dengan menggunakan tabel Employee (Gambar 2.26). Penggunaan aliasing dapat dilakukan seperti perintah SQL dibawah ini : Gambar 2.43 Query dengan menggunakan aliasing. Gambar 2.44 Hasil eksekusi query dengan menggunakan aliasing. Dari hasil yang ditampilkan, maka seseorang akan lebih mudah mengetahui setiap kolom yang ditampilkan. Umumnya digunakan nama alias yang lebih singkat untuk mempercepat proses penulisan query ketika akan berhubungan dengan tabel atau kolom yang sangat banyak. 2.11. Join tables with two columns Pada bagian ini akan dijelaskan bagaimana cara menampilkan data dari tabel yang bersifat temporary, dalam arti tabel tersebut hanya ada ketika query dijalankan. Untuk mengetahui lebih lanjut bagaimana penggunaannya adakn dilakukan percobaan dengan tabel Order seperti pada gambar berikut : Gambar 2.45 Tabel Order Kemudian dilakukan percobaan untuk membuat temporary tabel dengan perintah sebagai berikut : 22 Gambar 2.46 Query untuk membuat temporary folder. Dari perintah SQL menjelaskan bahwa data yang berasal dari tabel Orders ditampilkan data mengenai CustomerID berdasarkan tanggal pemesanan terkecil. Perintah yang digunakan untuk membuat temporary tabel adalah pada baris kedua yaitu, INTO #MinOrderDates yang berarti data yang ditampilkan pada query select ditampung pada temporary yang bernama #MinOrderDates. Kemudian dilakukan pemanggilan pada temporary tabel tersebut dengan perintah seperti dibawah ini : SELECT * FROM #MinOrderDates Hasilnya akan ditampilkan kolom dari temporary tabel tersebut : Gambar 2.47 Hasil eksekusi query temporary tabel. 2.12. The result of the previous join is then joined to another table Pada bagian ini akan dijelaskan bagaimana cara menggunakan hasil dari SQL join yang dilakukan sebelumnya dan hasilnya digunakan kembali dengan SQL join ke tabel lain. Untuk mengetahui bagaimana penggunaan teknik tersebut akan dilakukan ujicoba pada tabel berikut ini: Ujicoba dilakukan dengan menggunakan tabel Author (Gambar 2.13), tabel titles (Gambar 2.11) dan tabel titleauthor (Gambar 2.14). 23 Perintah SQL yang digunakan dalam menggunakan dua join tersebut adalah sebagai berikut : SELECT 'Author'=RTRIM(au_lname) + ', ' + au_fname, 'Title'=title FROM ( (authors AS A FULL OUTER JOIN titleauthor AS TA ON A.au_id=TA.au_id ) FULL OUTER JOIN titles AS T ON TA.title_id=T.title_id ) WHERE state <> 'CA' OR state IS NULL ORDER BY 1 Hasil eksekusi perintah tersebut adalah sebagai berikut : Gambar 2.48 Hasil eksekusi perintah dua join. Dari hasil tersebut dapat dijelaskan bahwa bahwa pertama kali data yang ditampilkan adalah gabungan dari nama belakang dan nama depan yang diberi nama Author sebagai identitas kolom. Dan data berikutnya yang juga ditampilkan adalah data title pada kolom dengan nama Title. Selanjutnya data yang ditampilkan akan digabung dengan menggunakan perintah outer join dengan tabel titleauthor. Dari hasil penggabungan dengan menggunakan perintah SQL outer join, maka data diseleksi lagi dengan menggunakan perintah outer join dari tabel titles. Kondisi yang digunakan 24 untuk menampilkan data adalah state bukan berisi CA atau state bernilai null. 2.13. SQL-92 Three-Way Inner Joins Pada bagian ini dijelaskan mengenai penggunaan SQL-92 Three Way Inner join. Penggunaan perintah SQL dengan beberapa join digunakan apabila data yang diambil berasal dari beberapa tabel yang mempunyai primary key yang saling berhubungan. Untuk mengetahui penggunaan Three-Way Inner Joins maka dilakukan percobaan join dengan menggunakan tiga tabel sebagai berikut : a. Membuat tabel Departement dan mengisikan data sampel. Gambar 2.49 Tabel Departement. b. Membuat tabel Jobs dan mengisikan data sampel. Gambar 2.50 Tabel Jobs. c. Membuat tabel Employee dan mengisikan data sampel. Gambar 2.51 Tabel Employee sampel. 25 Kemudian diberikan perintah SQL seperti dibawah ini : SELECT empid,empname,salary, E.deptno,deptname, E.jobid,jobdesc FROM Employees AS E JOIN Departments AS D ON E.deptno = D.deptno JOIN Jobs AS J ON E.jobid = J.jobid Dan hasil dari eksekusi query tersebut adalah sebagai berikut : Gambar 2.52 Hasil query dari join 3 tabel. 2.14. Forcing the Order of Join Processing Pada bagian ini dijelaskan mengenai fungsi dari force order dalam proses join. Force order digunakan untuk memaksakan urutan eksekusi berdasarkan urutan dalam query [http://netindonesia.net]. Pada umumnya SQL Server sudah melakukan pemrosesan query dan memberi urutan dengan baik, namun dalam kasus lain dibutuhkan rencana eksekusi yang sesuai dengan keinginan user secara tepat, sehingga digunakan fungsi force order. Untuk mengetahui bagaimana cara kerja force order, dilakukan percobaan pada tiga tabel yaitu : Tabel Departement (Gambar 2.49), Tabel Jobs (Gambar 2.50) dan tabel Employee (Gambar 2.51). 26 Kemudian diberikan perintah SQL seperti berikut ini : SELECT empid,empname,salary, E.deptno,deptname, E.jobid,jobdesc FROM Employees AS E JOIN Departments AS D ON E.deptno = D.deptno JOIN Jobs AS J ON E.jobid = J.jobid OPTION(FORCE ORDER) Hasil dari eksekusi query tersebut adalah sebagai berikut ini : Gambar 2.53 Hasil eksekusi query dengan menggunakan force order. Dari data tersebut dipeoleh informasi bahwa SQL bias menghasilkan urutan eksekusi berdasarkan urutan query sehingga menghasilkan data eksekusi yang sesuai dengan yang diinginkan. 27 3. PENUTUP Demikian laporan penggunaan tabel join di SQL Server 2005 yang dapat saya paparkan berdasarkan referensi dan rujukan yang saya dapatkan. Makalah ini masih memiliki banyak kekurangan dikarenakan terbatasnya pengetahuan penulis dan kekurangan bahan rujukan yang digunakan dalam pembuatan makalah ini. Saran dan kritik yang membangun merupakan harapan penulis untuk memperbaiki kekurangan yang ada pada makalah ini. Semoga makalah ini berguna bagi penulis pada khususnya dan pembaca pada umumnya. 28 DAFTAR PUSTAKA Joseph Sack, , 2008, “SQL 2008 Transact-SQL Recipes”, New York. S Sudharma, 2010. “Panduan Belajar MySQL Database Server”,Jakarta, Mediakita. Website : http:// beginner-sql-tutorial.com/sql-joins.htm diakses tanggal 7 November pukul 13.00 WIB http://www.fianga.com/ diakses tanggal 2 Maret 2011 pukul 13.00WIB http://www.java2s.com/Tutorial/SQLServer/0080__TableJoin/SQLServer200 5jointypesfallintothreecategoriesinnerouterandcross.htm diakses tanggal 8 November 2011 pukul 01.00WIB http://netindonesia.net/blogs/kasim.wirama/archive/2007/12/31/sql-server-2005query-hints.aspx diakses tanggal 9 November 2011 pukul 03.30WIB 1
