id_bin

QUERY ANTAR TABEL
1. Pendahuluan
• Tabel pertama bernama infoprib, dapat
dilihat pada Gambar 1.
id_bin
nama
tgl_lahir
sex
DREWB
Drew Barrymore
1975-02-22
W
STONE
Sharon Stone
1958-03-10
W
SYLVE
Sylvester Stallone
1946-07-06
P
KEVIN
Kevin Costner
1955-01-18
P
HAKIM
Cristine Hakim
1957-12-25
W
JULIA
Julia Roberts
1967-10-28
W
DEMIM
Demi Moore
1962-11-11
W
CINDY
Cindy Crawford
1966-02-20
W
CDIAZ
Cameron Diaz
1972-08-30
W
KEANU
Keanu Reeves
1964-09-02
P
SANDR
Sandra Bullock
1964-09-02
W
RGERE
Richard Gere
1948-08-29
P
RAHAR
Slamet Rahardjo
KELLY
Kelly McGillis
1957-07-09
W
JOLIE
Angelina Jolie
1975-06-04
W
P
Gambar 1. Isi tabel infoprib
• Tabel kedua berupa film, yg dibuat melalui
pernyataan SQL:
CREATE TABLE film (
kode_film CHAR(5) NOT NULL
PRIMARY KEY,
judul CHAR(25),
tahun CHAR(4));
• Isi tabel film ditunjukkan pada Gambar 2.
kode_film
judul
tahun
DABAN
Daun di Atas Bantal
1998
OTONO
Nemuru Otoko
1994
BASIC
Basic Instinct
1992
SPESI
The Spesialist
1995
BATFO
Batman Forever
1995
NOWAY
No Way Out
1987
FAIRG
Fair Game
1995
DISCL
Disclosure
1994
GJANE
G. I. Jane
1997
BODYG
Bodyguard
1992
GLORI
Gloria
1999
CLIFF
Cliffhanger
1993
RAMBO
Rambo: First Blood II
1985
SPEED
Speed
1994
BRIDE
Runaway Bride
1999
Gambar 2. Isi tabel film
• Tabel ketiga berupa main, yg dibuat melalui
pernyataan SQL :
CREATE TABLE main (
id_bin CHAR(5) NOT NULL,
kode_film CHAR(5) NOT NULL ,
peran CHAR(25),
PRIMARY KEY (id_bin));
• Isi tabel main ditunjukkan pada Gambar 3.
id_bin
kode_film
peran
HAKIM
DABAN
Asih
HAKIM
OTOKO
Tia
STONE
BASIC
Catherine Tramell
STONE
SPESI
May Munro
STONE
GLORI
Gloria
DREWB
BATFO
Sugar
SYLVE
SPESI
Ray Quick
CINDY
FAIRG
Kate McQueen
DEMIM
GJANE
Jordan O’Neil
DEMIM
DISCL
Meredith Johson
KEVIN
BODYG
Frank Farmer
SYLVE
CLIFF
Gbe Walker
SYLVE
RAMBO
John Rambo
KEANU
SPEED
Jack Traven
SANDR
SPEED
Annie Porter
JULIA
BRIDE
Maggie Carpenter
RGERE
BRIDE
Ike Graham
Gambar 3. Isi tabel main
2. Menggabungkan Tabel
• SQL
mempunyai
kemampuan
untuk
menggabungkan 2 tabel atau lebih untuk
membentuk informasi. Prosesnya sdisebut join.
• Sebagai gambaran dapat dilihat pada Gambar 4,
berdasarkan 3 buah tabel di atas dapat diperoleh
informasi seperti berikut melalui join.
id_bin
judul
peran
STONE
Basic Intinct
Catherine Tramell
DREWB
Batman Forever
Sugar
KEVIN
Bodyguard
Frank Farmer
JULIA
Runaway Bride
Maggie Carpenter
RGERE
Runaway Bride
Ike Graham
SYLVE
Cliffhanger
Gabe Walker
HAKIM
Daun di Atas Bantal
Asih
DEMIM
Disclosdure
Meredith Johson
CINDY
Fair Game
Kate McQueen
DEMIM
G. I. Jane
Jordan O’Neil
STONE
Gloria
Gloria
HAKIM
Nemuru Otoko
Tia
SYLVE
Rambor First Blood II
John Rambo
KEANU
Speed
Jack Traven
SANDR
Speed
Annie Porter
STONE
The Spesialist
May Munro
SYLVE
The Spesialist
Ray Quick
Gambar 4. Informasi yg dibentuk dari tabel infoprib, main dan film
3. Query dengan Dua Buah Tabel
• Untuk memahami bagaimana menggabungkan
beberapa tabel, cobalah pernyataan SQL berikut:
SELECT main.nama,
film.judul,
main.peran
FROM main, film
WHERE main.kode_film=film.kode_film
• Query di atas digunakan untuk memperoleh identitas
bintang film beserta judul dan peran yg dimainkan.
• Hasil yg diperoleh dapat dilihat pada Gambar 5.
nama
judul
peran
Sharon Stone
Basic Intinct
Catherine Tramell
Drew Barrymore
Batman Forever
Sugar
Kevin Costner
Bodyguard
Frank Farmer
Julia Roberts
Runaway Bride
Maggie Carpenter
Richard Gere
Runaway Bride
Ike Graham
Sylvester Stallone
Cliffhanger
Gabe Walker
Christine Hakim
Daun di Atas Bantal
Asih
Demi Moore
Disclosdure
Meredith Johson
Cindy Crawford
Fair Game
Kate McQueen
Demi Moore
G. I. Jane
Jordan O’Neil
Sharon Stone
Gloria
Gloria
Christine Hakim
Nemuru Otoko
Tia
Sylvester Stallone
Rambor First Blood II
John Rambo
Keanu Reeves
Speed
Jack Traven
Sandra Bullock
Speed
Annie Porter
Sharon Stone
The Spesialist
May Munro
Sylvester Stallone
The Spesialist
Ray Quick
Gambar 5. Hasil query terhadap 2 buah tabel
Beberapa hal yg perlu diperhatikan dalam query antar tabel:
• Setiap kolom disebutkan dengan bentuk:
nama_tabel.nama_kolom
Sebagai contoh, film.kode_film berarti kolom kode_film yg terdapat pada tabel film. Hal ini
merupakan mekanisme untuk membedakan kolom kode_film milik tabel film dengan
kode_film milik tabel main yg ditulis dengan main.kode_film.
• Tabel-tabel yg dilibatkan dalam query perlu disebutkan dalam klausa FROM dengan antar
tabel dipisah oleh tanda koma.
Contoh:
FROM main, film
yg berarti ada 2 tabel yg dilibatkan yaitu main dan film. Urutan tabel tidaklah penting. Jadi:
FROM main, film dan
FROM film, main
sama saja.
• Kondisi dalam klausa WHERE menentukan macam join yg terbentuk.
• Contoh berikut digunakan untuk menggabungkan
data yg terdapat pada tabel main dan infoprib
maka pernyataan SQL sbb:
SELECT infoprib.nama,
main.kode_film
FROM infoprib, main
WHERE
main.id_bin=infoprib.id_bin;
• Hasil query di atas dapat dilihat pada Gambar 6.
nama
kode_film
Cindy Crawford
FAIRG
Demi Moore
DISCL
Demi Moore
GJANE
Drew Barrymore
BATFO
Cristine Hakim
DABAN
Cristine Hakim
OTOKO
Julia Roberts
BRIDE
Keanu Reeves
SPEED
Kevin Costner
BODYG
Richard Gere
BRIDE
Sandra Bullock
SPEED
Sharon Stone
BASIC
Sharon Stone
GLORI
Sharon Stone
SPESI
Sylvester Stallone
CLIFF
Sylvester Stallone
RAMBO
Sylvester Stallone
SPESI
Gambar 6. Hasil query
4. Menggunakan Alias
• Nama tabel yg digunakan dalam query antar tabel bisa diberi nama alias.
Bentuk pernyataan SQL pemberian alias:
SELECT alias1.kolom, alias2.kolom
FROM tabel1 alias, tabel2 alias
WHERE kondisi;
• Dalam hal ini, kondisi bisa menggunakan alias.
• Contoh pernyataan SQL :
SELECT main.id_bin,
film.judul,
main.peran
FROM main, film
WHERE main.kode_film=film.kode_film;
• Dapat ditulis menjadi:
SELECT M.id_bin,
F.judul,
M.peran
FROM main M, film F
WHERE main.kode_film=film.kode_film;
• Pada contoh di atas M dan F adalah nama alias tabel.
Dalam hal ini, M adalah nama alias dari tabel main dan
F adalah nama alias dari tabel film.
5. Perkalian Kartesian
• Bentuk paling sederhana dari penggabungan
2 buah tabel akan membentuk perkalian
kartesian (cartesian Product) atau biasa juga
disebut cross join atau full join.
• Cross join diperoleh kalau klausa WHERE
tdk disebutkan.
• Sebagai contoh, terdapat pada Gambar 7.
Tabel 1
A
1
3
B
2
4
Tabel 2
X
5
7
Y
6
8
Gambar 7. Isi tabel1 dan tabel2
• Pada gambar 8, jika dikenakan operasi seperti berikut:
SELECT *
FROM tabel1, tabel2;
• Maka akan dihasilkan informasi sebagai berikut.
A
B
X
Y
1
2
5
6
3
4
5
6
1
2
7
8
3
4
7
8
Gambar 8. Hasil perkalian kartesian
6. Equijoin dan Non-Equijoin
• Equijoin adalah penggabungan antar tabel yg
menggunakan operator sama dengan (=)
pada kondisi dalam klausa WHERE untuk
membandingkan satu kolom dengan kolom
lainnya.
• Hasil yg diberikan hanya berupa baris-baris
yg muncul pada kedua tabel.
• Bentuk yg lebih kompleks ditunjukkan pada contoh pernyataan SQL
berikut:
SELECT infoprib.nama, film.judul, main.peran
FROM infoprib, main, film
WHERE
main.kode_film
=
film.kode_film
AND
main.id_bin=infoprib.id_bin;
• Contoh ini melibatkan 3 buah tabel. Perhatikan kondisi yg digunakan pada
klausa WHERE. Kondisi
main.kode_film = film.kode_film
• Digunakan untuk menghubungkan tabel main dan film dan kondisi:
main.id_bin = infoprib.id_bin
• Dipakai untuk menghubungkan tabel main dengan infoprib. Kedua kondisi
tersebut dihubungkan melalui operator AND.
• Hasil query pernyataan di atas dapat dilihat pd Gambar 4 di depan.
• Apabila operator yg digunakan untuk menghubungkan satu tabel dengan
tabel lainnya tdk berupa sama dengan (=) melainkan berupa operator
seperti <> atau >= maka join akan disebut sebagai non-equijoin.
7. Self-Join
• Self join merupakan upaya penggabungan dari tabel yg sama.
• Contoh self-join yaitu untuk memperoleh pasangan bintang film
pria dan wanita yg terdapat pada infoprib.
• Cara melakukannya adalah seperti berikut:
SELECT X.nama, Y.nama
FROM infoprib X, infoprib Y
WHERE X.sex = ‘P’ AND Y.sex=‘W’
• Tampak bahwa penanganannya menggunakan nama alias. Alias
pertama berupa X dan alias kedua berupa Y.
• Penggalan hasil query dari pernyataan di atas diperlihatkan pada
gambar 9.
nama
nama
Sylvester Stallone
Drew Barrymore
Kevin Costner
Drew Barrymore
Keanu Reeves
Drew Barrymore
Richard Gere
Drew Barrymore
Slamet Rahardjo
Drew Barrymore
Sylvester Stallone
Sharon Stone
Kevin Costner
Sharon Stone
Keanu Reeves
Sharon Stone
Richard Gere
Sharon Stone
Slamet Rahardjo
Sharon Stone
Sylvester Stallone
Christine Hakim
Kevin Costner
Christine Hakim
Keanu Reeves
Christine Hakim
Richard Gere
Christine Hakim
Slamet Rahardjo
Christine Hakim
Sylvester Stallone
Julia Roberts
Kevin Costner
Julia Roberts
Gambar 9. Hasil query dalam bentuk self-join
8. Operator UNION
• Operator UNION berguna untuk
menggabungkan hasil dari 2 buah
query.
• Sebagai contoh, terdapat 2 buah
tabel bernama ditolak dan diterima,
dapat dilihat pada Gambar 10 dan
Gambar 11.
no_uji
nama
alasan
no_uji
nama
1
Yuyun
-
2
Kartika
3
Farkhan
-
4
Edi Putra
5
Karun
-
7
Johan
6
Sita Devi
-
8
Friska
9
Rukmana
10
Santosa
Gambar 10. Isi tabel ditolak
Gambar 11. Isi tabel diterima
• Tabel ditolak dan diterima dapat digabungkan
dengan menggunakan UNION. Adapun
pernyataan SQL nya adalah :
SELECT no_uji, nama
FROM ditolak
UNION
SELECT no_uji, nama
FROM diterima;
• Gambar 12 memperlihatkan
pernyataan SQL di atas.
hasil
dari
no_uji
1
10
2
3
4
5
6
7
8
9
nama
Yuyun
Santosa
Kartika
Farkhan
Edi Putra
Karun
Sita Devi
Johan
Friska
Rukmana
Gambar 12. Hasil UNION
• Jika hasil UNION ingin diurutkan menurut kolom
tertentu, klausa ORDER BY hanya boleh disertakan
pada SELECT yg kedua. Contoh:
SELECT no_uji, nama
FROM ditolak
UNION
SELECT no_uji, nama
FROM diterima
ORDER BY nama
• Hasil query di atas dapat dilihat pada Gambar 13.
no_uji
4
3
8
7
2
5
9
7
6
1
nama
Edi Putra
Farkhan
Friska
Johan
Kartika
Karun
Rukmana
Santosa
Sita Dewi
Yuyun
Gambar 13. Hasil UNION diurutkan menurut nama
• Secara
bawaan
UNION
akan
menghilangkan baris yg kembar.
• Untuk melihat efek ini, perhatikan 2 tabel
pada Gambar 14 dan 15.
hewan
hewan
Katak
Katak
Ikan mas
Kuda
Ular
Sapi
Ikan nila
Ular
Gambar 14. Isi tabel hewanair
Gambar 15. Isi tabel hewandrt
• Selanjutnya, bisa mencoba perintah berikut:
SELECT *
FROM hewanair
UNION
SELECT *
FROM hewandrt;
• Hasilnya diperlihatkan pada Gambar 16.
hewan
Ikan mas
Ikan nila
Katak
Kuda
Sapi
Ular
Gambar 16. Baris yg kembar dihilangkan
• Seandainya menghendaki agar data yg kembar
tetap disertakan, perlu menggunakan kata ALL di
belakang UNION. Contoh:
SELECT *
FROM hewanair
UNION ALL
SELECT *
FROM hewandrt;
• Hasilnya diperlihatkan pada Gambar 17.
hewan
Katak
Ikan mas
Ular
Ikan nila
Katak
Kuda
Sapi
Ular
Gambar 17. Efek UNION ALL. Baris yg kembar tetap disertakan
9. Inner Join dan Outer Join
• Equijoin seringkali dibedakan menjadi 2 kategori yaitu inner
equijoin (atau disingkat inner join) dan outer equijoin (atau
disingkat outer join).
• Untuk melihat perbedaan kedua macam equijoin ini, perhatikan
query berikut beserta hasilnya.
SELECT infoprib.nama,
main.kode_film,
main.peran
FROM infoprib, main
WHERE infoprib.id_bin=main.id_bin;
• Hasil query di atas dapat dilihat pada Gambar 18.
nama
kode_film
peran
Cindy Crawford
FAIRG
Kate McQueen
Demi Moore
DISCL
Meredith Johson
Demi Moore
GJANE
Jordan O’Neil
Drew Barrymore
BATFO
Sugar
Cristine Hakim
DABAN
Asih
Cristine Hakim
OTOKO
Tia
Julia Roberts
BRIDE
Maggie Carpenter
Keanu Reeves
SPEED
Jack Traven
Kevin Costner
BODYG
Frank Farmer
Richard Gere
BRIDE
Ike Graham
Sandra Bullock
SPEED
Annie Porter
Sharon Stone
BASIC
Catherine Tramell
Sharon Stone
GLORI
Gloria
Sharon Stone
SPESI
May Munro
Sylvester Stallone
CLIFF
Gabe Walker
Sylvester Stallone
RAMBO
John Rambo
Sylvester Stallone
SPESI
Ray Quick
Gambar 18. Contoh hasil inner join
• Kalau perhatikan isi tabel infoprib (Gambar 1) maka
sesungguhnya banyak bintang film yg tdk terlihat pada
hasil (Gambar 18).
• Hasil (Gambar 18) yaitu hanya berupa baris yg
memiliki data pada kedua tabel yg disebut inner join.
• Memang betul bahwa bintang film yg tdk ditampilkan
tersebut tdk memiliki entri pada tabel main (lihat
Gambar 3).
• Namun bagaimana seandainya dikehendaki agar
bintang film yg tdk tercantum pada tabel main ikut
ditampilkan pada hasil query? Misalnya seperti pada
Gambar 19.
nama
kode_film
peran
Cristine Hakim
DABAN
Asih
Cristine Hakim
OTOKO
Tia
Cindy Crawford
FAIRG
Kate McQueen
Demi Moore
DISCL
Meredith Johson
Demi Moore
GJANE
Jordan O’Neil
Drew Barrymore
BATFO
Sugar
Julia Roberts
BRIDE
Maggie Carpenter
Keanu Reeves
SPEED
Jack Traven
Kevin Costner
BODYG
Frank Farmer
Richard Gere
BRIDE
Ike Graham
Sandra Bullock
SPEED
Annie Porter
Sharon Stone
BASIC
Catherine Tramell
Sharon Stone
GLORI
Gloria
Sharon Stone
SPESI
May Munro
Sylvester Stallone
CLIFF
Gabe Walker
Sylvester Stallone
RAMBO
John Rambo
Sylvester Stallone
SPESI
Ray Quick
Angelina Jolie
Cameron Diaz
Kelly Mcgillis
Slamet Rahardjo
Gambar 19. Contoh
hasil outer join
• Perlu diketahui terlebih dulu bahwa hasil seperti Gambar 19 disebut outer join.
• Adapun cara memperolehnya ada beberapa cara.
• Salah satu di antaranya adalah melalui operator UNION, sebagaimana diperlihatkan
berikut ini:
SELECT infoprib.nama,
main.kode_film,
main.peran
FROM infoprib, main
WHERE infoprib.id_bin=main.id_bin
UNION
SELECT nama,
NULL,
NULL
FROM infoprib
WHERE id_bin NOT IN
(SELECT id_bin FROM main);
10. Natural Join dan Unnatural Join
• Penggabungan 2 tabel atau lebih yg menggunakan kolom yg
berkedudukan sebagai kunci tamu dan kunci primer (sekalipun
hanya dalam desain basis data) sebagai penghubung biasa
disebut dengan istilah natural join.
• Sebagai contoh, hubungan antar tabel infoprib dan main
menggunakan kolom id_bin merupakan contoh natural join.
SELECT infoprib.nama, main.kode_film, main.peran
FROM infoprib,main
WHERE infoprib.id_bin=main.id_bin
• Unnatural join mengungkapkan penggabungan dengan kondisi
penghubung antar tabel tdk mencerminkan hubungan yg lazim.
11. Operator INTERSECT
• Operator INTERSECT berguna untuk memperoleh baris-baris yg
terdapat pada kedua tabel.
• Dengan mengacu pada tabel hewanair dan hewandrt (Gambar
14 dan 15), bisa mencoba perintah berikut:
SELECT hewan
FROM hewanair
INTERSECT
SELECT hewan
FROM hewandrt;
• Hasil query di atas dapat dilihat pada Gambar 20.
hewan
Katak
Ular
Gambar 20. Hasil INTERSECT
12. Operator EXCEPT / MINUS
• Jika terdapat tabel A dan B, operasi A EXCEPT B akan menghasilkan
semua yg ada pada A tetapi tidak terdapat pada B. Contoh:
SELECT hewan
FROM hewanair
EXCEPT
SELECT hewan
FROM hewandrt;
• Hasilnya dapat dilihat pada Gambar 21.
• Tampak pada Gambar 21 bahwa hasil yg diberikan berupa semua baris
pada tabel hewanair yg tdk terdapat pada tabel hewandrt.
hewan
Ikan mas
Ikan nila
Gambar 21. Hasil EXCEPT
• Perlu diketahui, A EXCEPT B dan B
EXCEPT A memberikan hasil yg berbeda.
Contoh:
SELECT hewan
FROM hewandrt
EXCEPT
SELECT hewan
FROM hewanair;
• Hasilnya dapat dilihat pada Gambar 22.
hewan
Kuda
Sapi
Gambar 22. Semua baris pada hewandrt yg tdk
terdapat pada hewanair