Tutorial SQL ( Structured Query Language ) part 3

Menghilangkan Penggandaan Dengan DISTINCT 

Penggunaan keyword DISTINCT hampir mirip dengan GROUP BY yaitu untuk melakukan grouping hasil pencarian. Tetapi DISTINCT hanya berfungsi menghilangkan duplikasi hasil dan tidak dapat digunakan untuk membantu melakukan berbagai perhitungan fungsi agregat.
Penerapan praktisnya misalnya anda ingin mencari negara mana saja yang yang terdapat di tabel customer.
Apabila anda menggunakan perintah SELECT saja maka akan muncul banyak duplikasi karena banyak terdapat customer yang berasal dari negara yang sama. Dengan menggunakan DISTINCT maka duplikasi tersebut dapat dihilangkan.

SELECT DISTINCT Country
FROM Customers

Cobalah bandingkan hasilnya apabila anda menggunakan perintah berikut :

SELECT Country
FROM Customers 

Membatasi Group BY dengan Having

Untuk membatasi hasil yang didapat dari suatu perintah yang menggunakan GROUP BY digunakan klausa HAVING. Fungsi klausa ini mirip dengan WHERE tetapi HAVING hanya dapat digunakan bersamaan dengan klausa GROUP BY dan diletakkan sesudah GROUP BY.
Perintah berikut digunakan untuk mencari produk yang jumlah penjualannya melebihi 1000 buah di tabel Order Details.

SELECT ProductID, SUM(Quantity)as Jumlah, 
FROM [Order Details] 
GROUP BY ProductID
HAVING SUM(Quantity) > 1000

Apabila dijalankan maka hasilnya adalah sebagai berikut :
ProductID   Jumlah      
----------- -------- 
31          1397
62          1083
21          1016
75          1155
60          1577

Terlihat jelas bahwa yang ditampilkan hanya yang memiliki jumlah diatas 1000 sebagaimana kriteria:
HAVING SUM(Quantity) > 1000
15  

Mencari Data dengan BETWEEN

Keyword BETWEEN digunakan untuk mencari data yang memenuhi interval kriteria tertentu dalam suatu kolom. Biasanya digunakan untuk mencari data berdasarkan interval tanggal tertentu.
Penggunaan praktisnya misalnya anda ingin mencari penjualan yang terjadi antara tanggal 1 Januari 2003 sampai dengan 31 Januari 2003. Keyword BETWEEN digunakan bersamaan dengan interval tanggal tersebut.
Tuliskan baris kode berikut :

USE NorthWind
SELECT OrderID, CustomerID, OrderDate 
FROM Orders
WHERE OrderDate BETWEEN '07/01/1996' AND '07/31/1996'

Kode tersebut mencari data di tabel Orders yang memiliki OrderDate antara tanggal 1 Juli 1996 sampai dengan 31 Juli 1996. Setelah di run maka hasilnya adalah :
OrderID     CustomerID OrderDate                                              
----------- ---------- -----------------
10248       VINET      1996-07-04 00:00:00.000
10249       TOMSP      1996-07-05 00:00:00.000
10250       HANAR      1996-07-08 00:00:00.000
10251       VICTE      1996-07-08 00:00:00.000
10252       SUPRD      1996-07-09 00:00:00.000
10253       HANAR      1996-07-10 00:00:00.000

Selain menggunakan kriteria tanggal anda juga dapat menggunakan keyword BETWEEN tersebut untuk tipe data lain misalnya string dan numeric. Misalnya anda ingin menampilkan data customer yang diawali huruf A sampai dengan D, maka digunakan kode program berikut : 

SELECT * FROM dbo.Customers
WHERE CompanyName BETWEEN 'A' AND 'D' 

Menggabungkan Table

Anda telah mempelajari bagaimana menampilkan data dari database Northwind serta memfilternya dengan berbagai kriteria. Pengambilan data tersebut hanya dari satu tabel saja sehingga belum dapat menyajikan informasi lebih detil.
Perintah SQL dapat digunakan untuk menampilkan data dari 2 atau lebih tabel. Antara tabel tersebut harus memiliki penghubung yaitu Primary Key dan Foreign Key sebagaimana telah dijelaskan pada Bab 2 mengenai desain database. Dengan menggunakan kriteria penghubung tersebut anda dapat menampilkan data dari beberapa tabel secara konsisten.

Inner Join 

Penggabungan tabel dalam perintah SQL menggunakan keyword JOIN. Jenis penggabungan tabel yang pertama adalah INNER JOIN. INNER JOIN hanya menampilkan data yang benar-benar terdapat di dalam tabel yang saling dihubungkan. 
Misalnya anda menggabungkan tabel Customers dengan Orders, maka field penghubung yang digunakan adalah CustomerID. Perhatikan diagram berikut :
 
 

Pada gambar tersebut terlihat bahwa CustomerID merupakan Primary Key di tabel Customers dan menjadi Foreign Key di tabel Orders. Hubungan kedua tabel tersebut adalah One to Many, dimana satu customer dapat memiliki banyak order yang berulang.
Dalam teknik INNER JOIN maka hasil yang ditampilkan hanya record yang memiliki CustomerID sama di kedua tabel tersebut. Apabila terdapat customr yang CustomerID nya tidak ditemukan di tabel Orders maka data tersebut tidak ditampilkan.
Coba ketikkan perintah berikut di Query Analyser :

SELECT CompanyName, OrderID, OrderDate
FROM Customers 
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID 



Dan perhatikan hasilnya sebagai berikut :
CompanyName                OrderID     OrderDate                                             
---------------------------------------- ----------- --------- 
Vins et alcools Chevalier  10248       1996-07-04 00:00:00.000
Toms Spezialitäten         10249       1996-07-05 00:00:00.000
Hanari Carnes              10250       1996-07-08 00:00:00.000
Victuailles en stock       10251       1996-07-08 00:00:00.000
Suprêmes délices           10252       1996-07-09 00:00:00.000
Hanari Carnes              10253       1996-07-10 00:00:00.000
…………………………………………………………
……………………………………………………….
(830 row(s) affected)

Perintah SQL tersebut mengambil field CompanyName dari tabel Customers, sedangkan field OrderID dan OrderDate diambil dari tabel Orders. Perhatikan baik baik diagram pada gambar 3.2. 
Kunci utama penggabungan dua tabel tersebut adalah keyword INNER JOIN dengan kriteria CustomerID.
Perhatikan bahwa nama tabel juga harus dituliskan sebelum nama field CustomerID tersebut.
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

Antara nama tabel dengan nama field dibatasi dengan tanda titik (.) misalnya Orders.CustomerID. Tujuan pencantuman nama tabel tersebut adalah menghindari ambiguitas yang mengakibatkan error apabila perintah tersebut dieksekusi. Karena CustomerID tersebut terdapat di kedua tabel maka nama tabel harus dicantumkan agar dapat diidentifikasi secara unik field mana yang dimaksud.
Untuk memberikan gambaran lebih kompleks mengenai penerapan penggabungan tabel ini anda dapat memodifikasi contoh perintah yang terdapat penggunaan GROUP BY. Pada perintah tersebut anda menghitung Jumlah dan rataan produk yang terjual, tetapi hanya ditampilkan ProductID saja sedangkan nama produknya tidak terlihat karena terdapat di tabel lain.
Anda dapat menggabungan tabel Order Details tersebut dengan tabel Products yang menyimpan nama produk. Dengan demikian dapat ditampilkan baik ProductID maupun nama produknya. Tuliskan baris perintah berikut :

SELECT Products.ProductID, Products.ProductName, 
 SUM([Order Details].Quantity) AS Jumlah, 
 AVG([Order Details].Quantity) AS Rataan, 
SUM(Products.UnitPrice * [Order Details].Quantity) 
AS Nilai, 
 AVG(Products.UnitPrice * [Order Details].Quantity) 
AS AVGNilai
FROM Products 
INNER JOIN [Order Details] 
ON Products.ProductID = [Order Details].ProductID
GROUP BY Products.ProductID, Products.ProductName

Setelah dijalankan maka akan tampak hasilnya :

ProductID ProductName Jumlah   Rataan  Nilai    AVGNilai              
----------- ---------------------------------------- ---------
1         Chai        828      21    14904.0000 392.2105
2         Chang       1057     24    20083.0000 456.4318
3         Aniseed     328      27    3280.0000  273.3333
4         Chef Anton  453      22    9966.0000  498.3000

 Pada perintah diatas anda dapat melihat bahwa semua nama field diawali dengan nama tabelnya. Hal ini menyebabkan penulisan kode program menjadi panjang, tetapi sisi positifnya adalah kita dapat langsung mengetahui posisi tabel dari tiap-tiap field yang ditampilkan.
Selain itu antara tabel Customers terdapat beberapa field yang namanya sama sehingga apabila tidak
dituliskan nama tabelnya berakibat error pada perintah tersebut.

LEFT JOIN dan RIGHT JOIN 

Berbeda dengan INNER JOIN yang hanya menampilkan irisan data dari tabel yang digabungkan maka LEFT JOIN akan menampilkan data dari tabel yang disebutkan terlebih dahulu.

LEFT JOIN

 Misalkan pada diagram gambar 3.2 diatas digunakan perintah LEFT JOIN maka apabila tabel Customers disebutkan terlebih dahulu SQL Server akan menampilkan semua nama customer yang terdapat di tabel tersebut. Semua data customer ditampilkan walaupun CustomerID nya tidak terdapat di tabel Orders.
Penggunaan praktis perintah ini misalnya anda ingin mengetahui frekuensi order semua customer baik yang telah memiliki order maupun yang belum. Untuk kebutuhan tersebut maka ditampilkan semua customer berikut data frekuensi ordernya. Perhatikan contoh perintah berikut :

SELECT Customers.CustomerID, Customers.CompanyName, 
COUNT(Orders.OrderID) AS Frekuensi
FROM Customers 
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName
ORDER BY COUNT(Orders.OrderID)
 
Karena menggunakan perintah LEFT JOIN maka semua data di tabel Customers ditampilkan seluruhnya walaupun frekuensi ordernya 0. Hasil perintah tersebut adalah :
CustomerID CompanyName                              Frekuensi   
---------- ---------------------------------------- ---------- 
PARIS      Paris spécialités                        0
FISSA      FISSA Fabrica Inter. Salchichas S.A.     0
CENTC      Centro comercial Moctezuma               1
LAZYK      Lazy K Kountry Store                     2
GROSR      GROSELLA-Restaurante                     2
LAUGB      Laughing Bacchus Wine Cellars            3
……………
……………
(91 row(s) affected)
Anda dapat melihat terdapat 2 customer yang frekuensi 0 tetapi tetap ditampilkan. Di akhir tampilan hasil tersebut terlihat ada 91 baris yang ditampikan. Untuk melakukan pengecekan jalankan perintah berikut :

SELECT  COUNT(*) from dbo.Customers 

maka hasilnya adalah :             
----------- 
91
(1 row(s) affected)

Artinya terdapat 91customer di tabel Customers, yaitu sama dengan jumlah baris di perintah sebelumnya.
Dengan demikian memang benar bahwa LEFT JOIN telah menampilkan seluruh Customer yang berjumlah 91.

RIGHT JOIN

Perintah RIGHT JOIN merupakan kebalikan dari LEFT JOIN, yaitu menampilkan semua isi tabel yang disebutkan kedua dalam perintah join. Dalam contoh di atas apabila LEFT JOIN diganti RIGHT JOIN maka semua isi tabel Orders akan ditampilkan semuanya.  Cobalah memodifikasi perintah diatas menjadi sebagai berikut :

SELECT Customers.CustomerID, Customers.CompanyName,  
COUNT(Orders.OrderID) AS Frekuensi
FROM Customers 
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName
ORDER BY COUNT(Orders.OrderID) 

Setelah anda jalankan perintah tersebut lihatlah perbedaaanya dengan perintah LEFT JOIN sebelumnya. Maka terlihat hanya dihasilkan 89 baris, berarti terdapat 2 customer yang CustomerID nya tidak terdapat di tabel Orders atau frekuensi ordernya = 0.

FULL JOIN  

Jenis join terakhir adalah FULL JOIN yang menampilkan semua data dari dua tabel yang dihubungkan meskipun terdapat data yang tidak memiliki pasangan di tabel lainnya.
Misalnya kita mengambil data dari tabel Country dengan tabel City menggunakan FULL JOIN. Data dari kedua tabel akan ditampilkan semuanya baik untuk nama kota yang tidak memiliki data negara maupun sebaliknya.