PENGGUNAAN SQL

UNTUK MENGAKSES
MULTITABLE

NANUNG NURJANNAH,[Link]
SMK NEGERI 2 KUNINGAN
 Perintah MySQL Lanjutan
™ Perintah SELECT dari Banyak Tabel

Pada bab ini akan dijelaskan beberapa perintah SQL lanjutan yang lebih
kompleks seperti join antar tabel, grouping, sub select, random search dan
transaksi (commit-and-roolback). Untuk mempermudah penjelasan, maka semua
contoh yang disajikan di bab ini mengacu pada pemodelan data konseptual
Sistem Pemesanan (Penjualan) Barang sbb:

Gambar 6.1. Pemodelan Data Konseptual

Untuk membuat tabel-tabel dari rancangan di atas, kita akan menggunakan

tipe tabel InnoDB karena nantinya kita akan menggunakan transaksi di sistem
tersebut. Dan berikut ini spesifikasi basis data dari pemodelan data konseptual
di atas:

Tabel pelanggan
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id_pelanggan | varchar(5) | NO | PRI | | |
| nm_pelanggan | varchar(40) | NO | | | |
| alamat | text | NO | | | |
| telepon | varchar(20) | NO | | | |
| email | varchar(50) | NO | | | |
+--------------+-------------+------+-----+---------+-------+

Tabel pesan
+--------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+----------------+
| id_pesan | int(5) | NO | PRI | NULL | auto_increment |
| id_pelanggan | varchar(5) | NO | MUL | | |
| tgl_pesan | date | NO | | | |
+--------------+------------+------+-----+---------+----------------+

Tabel produk
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id_produk | varchar(5) | NO | PRI | | |
| nm_produk | varchar(30) | NO | | | |
| satuan | varchar(10) | NO | | | |
| harga | decimal(10,0) | NO | | 0 | |
| stock | int(3) | NO | | 0 | |
+-----------+---------------+------+-----+---------+-------+

Tabel detil_pesan
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id_pesan | int(5) | NO | PRI | | |
| id_produk | varchar(5) | NO | PRI | | |
| jumlah | int(5) | NO | | 0 | |
| harga | decimal(10,0) | NO | | 0 | |
+-----------+---------------+------+-----+---------+-------+

Tabel faktur
+------------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------+------+-----+---------+----------------+
| id_faktur | int(5) | NO | PRI | NULL | auto_increment |
| id_pesan | int(5) | NO | | | |
| tgl_faktur | date | NO | | | |
+------------+--------+------+-----+---------+----------------+

Tabel kuitansi
+--------------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------+------+-----+---------+----------------+
| id_kuitansi | int(5) | NO | PRI | NULL | auto_increment |
| id_faktur | int(5) | NO | | | |
| tgl_kuitansi | date | NO | | | |
+--------------+--------+------+-----+---------+----------------+

Berikut ini disajikan perintah SQL untuk membuat tabel-tabel di atas:

/*Table structure for table detil_pesan */

DROP TABLE IF EXISTS detil_pesan;
CREATE TABLE detil_pesan (
id_pesan int(5) NOT NULL,
id_produk varchar(5) NOT NULL,
jumlah int(5) NOT NULL default '0',
harga decimal(10,0) NOT NULL default '0',
PRIMARY KEY (id_pesan,id_produk),
KEY FK_detil_pesan (id_produk),
 KEY id_pesan (id_pesan),
CONSTRAINT FK_detil_pesan FOREIGN KEY (id_produk)
REFERENCES produk (id_produk),
CONSTRAINT FK_detil_pesan2 FOREIGN KEY (id_pesan)
REFERENCES pesan (id_pesan)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Table structure for table faktur */
DROP TABLE IF EXISTS faktur;
CREATE TABLE faktur (
id_faktur int(5) NOT NULL auto_increment,
id_pesan int(5) NOT NULL,
tgl_faktur date NOT NULL,
PRIMARY KEY (id_faktur),
KEY id_pesan (id_pesan),
CONSTRAINT faktur_ibfk_1 FOREIGN KEY (id_pesan)
REFERENCES pesan (id_pesan)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table kuitansi */

DROP TABLE IF EXISTS kuitansi;
CREATE TABLE kuitansi (
id_kuitansi int(5) NOT NULL auto_increment,
id_faktur int(5) NOT NULL,
tgl_kuitansi date NOT NULL,
PRIMARY KEY (id_kuitansi),
KEY FK_kuitansi (id_faktur),
CONSTRAINT FK_kuitansi FOREIGN KEY (id_faktur)
REFERENCES faktur (id_faktur)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table pelanggan */

DROP TABLE IF EXISTS pelanggan;
CREATE TABLE pelanggan (
id_pelanggan varchar(5) NOT NULL,
nm_pelanggan varchar(40) NOT NULL,
alamat text NOT NULL,
telepon varchar(20) NOT NULL,
email varchar(50) NOT NULL,
PRIMARY KEY (id_pelanggan)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1
DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

/*Table structure for table pesan */

DROP TABLE IF EXISTS pesan;
CREATE TABLE pesan (
id_pesan int(5) NOT NULL auto_increment,
id_pelanggan varchar(5) NOT NULL,
tgl_pesan date NOT NULL,
PRIMARY KEY (id_pesan),
KEY id_pelanggan (id_pelanggan),
CONSTRAINT pesan_ibfk_1 FOREIGN KEY (id_pelanggan)
REFERENCES pelanggan (id_pelanggan)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

/*Table structure for table produk */

DROP TABLE IF EXISTS produk;
CREATE TABLE produk (
id_produk varchar(5) NOT NULL,
nm_produk varchar(30) NOT NULL,
satuan varchar(10) NOT NULL,
 harga decimal(10,0) NOT NULL default '0',
stock int(3) NOT NULL default '0',
PRIMARY KEY (id_produk)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Selanjutnya, untuk memudahkan dalam pemberian contoh, isilah tabel-tabel

diatas dengan record (data) secukupnya.

Perintah SELECT dari Banyak Tabel dengan JOIN

Di dalam suatu RDBMS tentunya sudah menjadi suatu kewajaran jika dalam
satu database dapat terdiri dari beberapa tabel. Masing-masing tabel tersebut
berhubungan satu sama lain atau dengan kata lain memiliki relasi. Relasi antar-
tabel dapat berupa relasi 1-1, 1-M, atau M-N. Sebagai contoh terlihat pada
gambar pemodelan data konseptual (class diagram) di atas. Tabel pelanggan
berhubungan dengan pesan, pesan dengan barang, dsb.
Pada praktisnya, terkadang kita juga memerlukan tampilan data yang tidak
hanya berasal dari 1 (satu) tabel, namun bisa dari beberapa tabel sekaligus.
Contohnya, dari class diagram diatas, kita ingin menampilkan nama pelanggan
berikut transaksi yang pernah dilakukannya. Dari contoh tersebut, kita harus bisa
menggabungkan minimal dua tabel, yaitu pelanggan dan pesan.
Untuk menggabungkan 2 (dua) atau lebih tabel, kita dapat menggunakan
bentuk perintah JOIN.

Inner Join

Dengan inner join, tabel akan digabungkan dua arah, sehingga tidak ada
data yang NULL di satu sisi. Sebagai contoh, kita akan menggabungkan tabel
pelanggan dan pesan dimana kita akan menampilkan daftar pelanggan yang
pernah melakukan pemesanan (transaksi). Isi tabel pelanggan dan pesan adalah
sebagai berikut :

Tabel pelanggan (hanya ditampilkan id, nama dan email).

+--------------+-----------------+--------------------+
| id_pelanggan | nm_pelanggan | email |
+--------------+-----------------+--------------------+
| P0001 | Achmad Solichin | achmatim@[Link] |
| P0002 | Budianto | budi@[Link] |
| P0003 | Hasan | hasan02@[Link] |
 | P0004 | Amin Riyadi | aminudin@[Link] |
+--------------+-----------------+--------------------+

Tabel pesan :

+----------+--------------+------------+
| id_pesan | id_pelanggan | tgl_pesan |
+----------+--------------+------------+
| 1 | P0001 | 2008-02-02 |
| 2 | P0002 | 2008-02-05 |
| 3 | P0002 | 2008-02-10 |
| 4 | P0004 | 2008-01-20 |
| 5 | P0001 | 2007-12-14 |
+----------+--------------+------------+

Cara 1 : Penggabungan dengan WHERE

Bentuk umum

SELECT tabel1.*, tabel2.* FROM tabel1, tabel2

WHERE [Link]=[Link];

Berikut ini perintah SQL untuk menggabungkan tabel pelanggan dan pesan:

SELECT pelanggan.id_pelanggan, pelanggan.nm_pelanggan,

pesan.id_pesan, pesan.tgl_pesan
FROM pelanggan, pesan
WHERE pelanggan.id_pelanggan=pesan.id_pelanggan;

Hasilnya :

+--------------+-----------------+----------+------------+
| id_pelanggan | nm_pelanggan | id_pesan | tgl_pesan |
+--------------+-----------------+----------+------------+
| P0001 | Achmad Solichin | 1 | 2008-02-02 |
| P0001 | Achmad Solichin | 5 | 2007-12-14 |
| P0002 | Budianto | 2 | 2008-02-05 |
| P0002 | Budianto | 3 | 2008-02-10 |
| P0004 | Amin Riyadi | 4 | 2008-01-20 |
+--------------+-----------------+----------+------------+

Pada hasil perintah query di atas terlihat bahwa terdapat 5 (lima) transaksi
yang dilakukan oleh 3 (tiga) orang pelanggan. Jika kita lihat kembali isi tabel
pelanggan di atas, maka terdapat satu pelanggan yang tidak ditampilkan yaitu
yang memiliki id pelanggan P0003. Pelanggan tersebut tidak ditampilkan karena
belum pernah melakukan transaksi.
Cara 2 : Penggabungan dengan INNER JOIN

Bentuk umum

SELECT tabel1.*, tabel2.*

FROM tabel1 INNER JOIN tabel2
ON [Link]=[Link];

Berikut ini perintah SQL untuk menggabungkan tabel pelanggan dan pesan:

SELECT pelanggan.id_pelanggan, pelanggan.nm_pelanggan,

pesan.id_pesan, pesan.tgl_pesan
FROM pelanggan INNER JOIN pesan
ON pelanggan.id_pelanggan=pesan.id_pelanggan;

Hasilnya :

+--------------+-----------------+----------+------------+
| id_pelanggan | nm_pelanggan | id_pesan | tgl_pesan |
+--------------+-----------------+----------+------------+
| P0001 | Achmad Solichin | 1 | 2008-02-02 |
| P0001 | Achmad Solichin | 5 | 2007-12-14 |
| P0002 | Budianto | 2 | 2008-02-05 |
| P0002 | Budianto | 3 | 2008-02-10 |
| P0004 | Amin Riyadi | 4 | 2008-01-20 |
+--------------+-----------------+----------+------------+

Outer Join

Dengan outer join, tabel akan digabungkan satu arah, sehingga

memungkinkan ada data yang NULL (kosong) di satu sisi. Sebagai contoh, kita
akan menggabungkan tabel pelanggan dan pesan dimana kita akan
menampilkan daftar pelanggan yang pernah melakukan pemesanan (transaksi).
Outer Join terbagi menjadi 2 (dua) yaitu LEFT JOIN dan RIGHT. Berikut ini
bentuk umum dan contohnya:

LEFT JOIN
Bentuk umum

SELECT tabel1.*, tabel2.*

FROM tabel1 LEFT JOIN tabel2
ON [Link]=[Link];

Berikut ini perintah SQL untuk menggabungkan tabel pelanggan dan pesan:
 SELECT pelanggan.id_pelanggan, pelanggan.nm_pelanggan,
pesan.id_pesan, pesan.tgl_pesan
FROM pelanggan LEFT JOIN pesan
ON pelanggan.id_pelanggan=pesan.id_pelanggan;

Hasilnya :

+--------------+-----------------+----------+------------+
| id_pelanggan | nm_pelanggan | id_pesan | tgl_pesan |
+--------------+-----------------+----------+------------+
| P0001 | Achmad Solichin | 1 | 2008-02-02 |
| P0001 | Achmad Solichin | 5 | 2007-12-14 |
| P0002 | Budianto | 2 | 2008-02-05 |
| P0002 | Budianto | 3 | 2008-02-10 |
| P0003 | Hasan | NULL | NULL |
| P0004 | Amin Riyadi | 4 | 2008-01-20 |
+--------------+-----------------+----------+------------+

Berbeda dengan hasil sebelumnya (inner join), penggunaan left join akan
menampilkan juga data pelanggan dengan id P0003, walaupun pelanggan
tersebut belum pernah bertransaksi. Dan pada kolom id_pesan dan tgl_pesan
untuk pelanggan P0003 isinya NULL, artinya di tabel kanan (pesan) pelanggan
tersebut tidak ada.

RIGHT JOIN
Bentuk umum

SELECT tabel1.*, tabel2.*

FROM tabel1 RIGHT JOIN tabel2
ON [Link]=[Link];

Berikut ini perintah SQL untuk menggabungkan tabel pelanggan dan pesan:

SELECT pelanggan.id_pelanggan, pelanggan.nm_pelanggan,

pesan.id_pesan, pesan.tgl_pesan
FROM pelanggan RIGHT JOIN pesan
ON pelanggan.id_pelanggan=pesan.id_pelanggan;

Hasilnya :

+--------------+-----------------+----------+------------+
| id_pelanggan | nm_pelanggan | id_pesan | tgl_pesan |
+--------------+-----------------+----------+------------+
| P0001 | Achmad Solichin | 1 | 2008-02-02 |
| P0002 | Budianto | 2 | 2008-02-05 |
| P0002 | Budianto | 3 | 2008-02-10 |
| P0004 | Amin Riyadi | 4 | 2008-01-20 |
| P0001 | Achmad Solichin | 5 | 2007-12-14 |
 +--------------+-----------------+----------+------------+

Dengan right join, tabel yang menjadi acuan adalah tabel sebelah kanan (tabel
pesan), jadi semua isi tabel pesan akan ditampilkan. Jika data pelanggan tidak ada di
tabel pelanggan, maka isi tabel pesan tetap ditampilkan.

Menggabungkan Tiga Tabel

Untuk menggabungkan tiga tabel atau lebih, pada dasarnya sama dengan
penggabungan 2 (dua) tabel. Sebagai contoh misalnya kita akan menampilkan
barang-barang yang dipesan beserta nama barang dan harganya untuk
pemesanan dengan nomor 1. Berikut ini perintah SQL-nya:

SELECT pesan.id_pesan, produk.id_produk, produk.nm_produk,

detil_pesan.harga, detil_pesan.jumlah
FROM pesan, detil_pesan, produk
WHERE pesan.id_pesan=detil_pesan.id_pesan AND
detil_pesan.id_produk=produk.id_produk
AND pesan.id_pesan='1'

Hasilnya :

+----------+-----------+------------+-------+--------+
| id_pesan | id_produk | nm_produk | harga | jumlah |
+----------+-----------+------------+-------+--------+
| 1 | B0001 | Buku Tulis | 2700 | 2 |
| 1 | B0003 | Penggaris | 3000 | 3 |
| 1 | B0004 | Pensil | 2000 | 1 |
+----------+-----------+------------+-------+--------+