Aljabar Relasional dan Query #4: Inner Join dan Outer Join

Inner join: theta join, equi-join, natural join, dan semijoin.
Outer join: left outer join, right outer join, dan full outer join.

Daftar Isi

• Pendahuluan
• Jenis Join
• Inner Join
▹ Theta Join
▹ Equi-join
▹ Natural Join: R ⨝ S
▹ Semijoin: R ▷ S
• Outer Join
▹ Left Outer Join: R ⟕ S
▹ Right Outer Join: R ⟖ S
▹ Full Outer Join: R ⟗ S
▹ Latihan
• Optimasi Query
• Penutup
• Referensi

Photo by Roberto Hund

Pendahuluan

Dalam pengelolaan basis data relasional, seringkali kita memiliki data yang tersebar di beberapa tabel yang saling terkait. Untuk mengombinasikan informasi dari tabel-tabel ini, kita membutuhkan operasi yang disebut join. Join adalah salah satu konsep kunci dalam aljabar relasional yang memungkinkan kita untuk mengombinasikan data dari beberapa tabel ke dalam satu hasil yang lebih komprehensif. Dengan kata lain, join memungkinkan kita untuk menjembatani hubungan antara entitas yang berbeda dalam basis data, sehingga kita dapat menjalankan query yang lebih kompleks dan analisis data yang lebih mendalam.

Tujuan dari artikel ini adalah untuk memberikan pemahaman yang komprehensif tentang konsep join dalam aljabar relasional dan penggunaannya dalam query. Artikel ini dirancang untuk membantu pembaca, terutama mereka yang terlibat dalam pengelolaan dan analisis basis data relasional, untuk memahami secara mendalam bagaimana join memungkinkan pengombinasian data dari berbagai tabel. Artikel ini akan membahas jenis-jenis join, kapan dan bagaimana menggunakan masing-masing jenis, serta memberikan contoh konkret dalam query.

Jenis Join

Secara garis besar ada dua jenis join yaitu inner join dan outer join.

Inner Join

Ini adalah join yang paling umum. Ia mengombinasikan baris dari dua tabel berdasarkan kondisi yang sesuai di antara kolom suatu tabel dengan kolom tabel lain. Hasil inner join hanya mencakup baris-baris yang memiliki pasangan yang sesuai di kedua tabel. inner join digunakan ketika Anda ingin mendapatkan data yang ada di kedua tabel yang berhubungan.

Outer Join

Selain menghasilkan pasangan baris yang sesuai, outer join juga memberikan pasangan baris yang tidak sesuai.

Inner Join

Ada beberapa jenis inner join yaitu:
(1) theta join
(2) equi- join
(3) natural join
(4) semijoin.

Theta Join

Identik dengan operasi seleksi terhadap produk Cartesian.

Menghasilkan tabel berisi baris dari R ⨉ S yang memenuhi predikat F. Predikat F dalam bentuk R.aᵢ θ S.bᵢ
dengan:
θ adalah operator perbandingan: >, >=, <, <=, <>, =
R dan S adalah relasi
aᵢ adalah atribut ke-i relasi R
bᵢ adalah atribut ke-i relasi S

Query-nya:

SELECT *
FROM R INNER JOIN
     S ON F

Sebagai contoh, berikut adalah tabel NilaiMk yang berisi daftar nilai mahasiswa. Nilai bertipe integer.

Akan ditentukan nilai hurufnya berdasarkan ketentuan berikut.

Terlebih dahulu dibuat basis data dengan skema sebagai berikut.
NilaiMk (NIM,KodeMk,Nilai)
NilaiHuruf (NilaiMin,NilaiMax,Huruf)

CREATE DATABASE NilaiMhs
GO
USE NilaiMhs
GO
CREATE TABLE NilaiMk (
   NIM int PRIMARY KEY,
   KodeMk char(5),
   Nilai tinyint
)
GO
CREATE TABLE NilaiHuruf (
   NilaiMin tinyint PRIMARY KEY,
   NilaiMax tinyint,
   Huruf char(1)
)
GO
INSERT INTO NilaiHuruf
VALUES (0,44,'E'),
       (45,55,'D'),
       (56,69,'C'),
       (70,84,'B'),
       (85,100,'A')
GO
INSERT INTO NilaiMk
VALUES (2211001,'KK333',71),
       (2211002,'KK335',45),
       (2211003,'KK333',85),
       (2311004,'KU122',67),
       (2311005,'KU134',70)
GO

Untuk memperoleh nilai huruf, lakukan operasi theta join.

SELECT *
FROM NilaiMk INNER JOIN
     NilaiHuruf
ON Nilai >= NilaiMin AND
   Nilai <= NilaiMax

-- Alternatif lain
SELECT *
FROM NilaiMk INNER JOIN
     NilaiHuruf
ON Nilai Between NilaiMin AND NilaiMax

Hasil eksekusinya:

NIM         KodeMk Nilai NilaiMin NilaiMax Huruf
----------- ------ ----- -------- -------- -----
2211001     KK333  71    70       84       B
2211002     KK335  45    45       55       D
2211003     KK333  85    85       100      A
2311004     KU122  67    56       69       C
2311005     KU134  70    70       84       B

(5 rows affected)

Karena tidak diperlukan, ada baiknya kolom NilaiMin dan NilaiMax tidak diikutsertakan. Lakukan proyeksi terhadap theta join.

SELECT NilaiMk.*, Huruf
FROM NilaiMk INNER JOIN
     NilaiHuruf
ON Nilai >= NilaiMin AND
   Nilai <= NilaiMax

-- Alternatif lain
SELECT NilaiMk.*, Huruf
FROM NilaiMk INNER JOIN
     NilaiHuruf
ON Nilai Between NilaiMin AND NilaiMax

Hasil eksekusinya:

NIM         KodeMk Nilai Huruf
----------- ------ ----- -----
2211001     KK333  71    B
2211002     KK335  45    D
2211003     KK333  85    A
2311004     KU122  67    C
2311005     KU134  70    B

(5 rows affected)

Latihan
Bagaimana kalau Nilai bertipe real/float?
⦁ Buat basis datanya.
⦁ Bagaimana data tabel NilaiHuruf?
⦁ Bagaimana kriteria theta join-nya?

Equi-join

Equi-join adalah salah satu jenis join yang paling umum digunakan. Ia identik dengan theta join tetapi predikat F menggunakan operator =. Equi-join mengombinasikan baris dari dua tabel berdasarkan kondisi yang sesuai di antara kolom foreign key suatu tabel dengan kolom primary key tabel lain.

Bagaimana memperoleh daftar mahasiswa dan dosen PA-nya dari kedua tabel berikut?

Buat basis data dan tabelnya terlebih dahulu dengan skema sebagai berikut.
Dosen (NRP,Nama)
Mahasiswa (NIM,Nama,NRP)

CREATE DATABASE MhsPA
GO
USE MhsPA
GO
CREATE TABLE Dosen (
   NRP  Char(2) PRIMARY KEY,
   Nama VarChar(15))  
GO
CREATE TABLE Mahasiswa(
   NIM  Char(3) PRIMARY KEY,
   Nama VarChar(16),
   NRP  Char(2),
   FOREIGN KEY(NRP) REFERENCES Dosen(NRP)
)
GO
INSERT INTO Dosen
VALUES ('01','Budi'),
       ('02','Rizka'),
       ('03','Anti')
GO
INSERT INTO Mahasiswa
VALUES ('345','Mawar','03'),
       ('346','Budi',NULL),
       ('347','Rizki','01'),
       ('348','Anta','03')

Gunakan equi-join.

SELECT *
FROM Mahasiswa INNER JOIN
     Dosen ON Mahasiswa.NRP = Dosen.NRP

Hasilnya:

Equi-join mengombinasikan data dari tabel Mahasiswa dan Dosen berdasarkan kesamaan nilai pada kolom NRP. Hanya baris yang memiliki NRP yang sama di kedua tabel yang akan dimasukkan dalam hasil join.

Natural Join: R ⨝ S

Natural join mengombinasikan data dari dua tabel berdasarkan kolom yang memiliki nama yang sama dan tipe data yang sama. Anda tidak perlu secara eksplisit menentukan kondisi join seperti yang Anda lakukan dalam equi-join. Sistem basis data secara otomatis mengidentifikasi kolom yang sesuai berdasarkan nama dan tipe data.

Natural join dapat membuat query lebih sederhana karena tidak memerlukan penyebutan kolom-kolom yang akan di-join. Hasilnya identik dengan equi-join tetapi atribut yang terhubung dieliminasi (atribut yang sama hanya muncul sebuah).

Berikut adalah ekspresi aljabar relasional dan query-nya untuk memperoleh daftar mahasiswa dan nama dosen pembimbing akademiknya.

SELECT *
FROM Mahasiswa NATURAL JOIN
     Dosen

Sayangnya query di atas kalau dieksekusi di SQL Server memberikan pesan kesalahan. SQL Server tidak mengenal NATURAL JOIN. Hasil yang sama dapat diperoleh dengan melakukan proyeksi terhadap equi-join.

SELECT Mahasiswa.*,
       Dosen.Nama As NamaPA
FROM Mahasiswa INNER JOIN
     Dosen ON Mahasiswa.NRP = Dosen.NRP

Hasilnya:

Semijoin: R ▷ S

Semijoin identik dengan equi-join tetapi hanya memberikan kolom tabel kiri. Misalnya, berikut adalah ekspresi aljabar relasional dan query-nya untuk memperoleh daftar dosen pembimbing akademik yaitu dosen yang membimbing mahasiswa.

Dosen ▷ Mahasiswa

SELECT Dosen.*
FROM Dosen INNER JOIN
     Mahasiswa
     ON Dosen.NRP = Mahasiswa.NRP

Eksekusinya menghasilkan:

Dosen pembimbing akademik bernama ‘Anti’ muncul dua kali karena ia membimbing dua orang mahasiswa. Supaya muncul hanya sekali, tambahkan klausa DISTINCT.

SELECT DISTINCT Dosen.*
FROM Dosen INNER JOIN
     Mahasiswa
     ON Dosen.NRP = Mahasiswa.NRP

Hasilnya:

Outer Join

Ada tiga jenis outer join, yaitu:
(1) left outer join
(2) right outer join
(3) full outer join.

Left Outer Join: R ⟕ S

Left outer join mengombinasikan data dari dua tabel, tetapi memberikan semua baris dari tabel di sebelah kiri (tabel R) dan hanya baris yang cocok dari tabel di sebelah kanan (tabel S). Jika tidak ada pasangan yang cocok di tabel kanan, kolom untuk tabel kanan akan diisi dengan NULL.

Berikut adalah ekspresi aljabar relasional dan query untuk memperoleh daftar semua mahasiswa (termasuk yang belum mempunyai pembimbing akademik) dan nama dosen pembimbingnya kalau ada.

Mahasiswa ⟕ Dosen

SELECT Mahasiswa.*,
       Dosen.nama AS NamaPA
FROM Mahasiswa LEFT OUTER JOIN
     Dosen ON Mahasiswa.NRP=Dosen.NRP

Hasilnya:

NIM  Nama             NRP  NamaPA
---- ---------------- ---- ---------------
345  Mawar            03   Anti
346  Budi             NULL NULL
347  Rizki            01   Budi
348  Anta             03   Anti

(4 rows affected)

Right Outer Join: R ⟖ S

Right outer join adalah jenis operasi yang mirip dengan left outer join, tetapi memberikan semua baris dari tabel di sebelah kanan (tabel S) dan hanya baris yang cocok dari tabel di sebelah kiri (tabel R). Jika tidak ada pasangan yang cocok di tabel kiri, kolom untuk tabel kiri akan diisi dengan NULL.

Contoh di atas pada left outer join dapat pula dibuat dengan right outer join: Dosen ⟖ Mahasiswa

SELECT Mahasiswa.*,
       Dosen.nama As NamaPA
FROM Dosen RIGHT  OUTER JOIN
     Mahasiswa
ON Mahasiswa.NRP=Dosen.NRP

dan memberikan hasil yang sama.

Full Outer Join: R ⟗ S

Full outer join adalah jenis operasi join yang mengombinasikan data dari dua tabel dengan memberikan semua baris dari kedua tabel, bahkan jika tidak ada pasangan yang cocok. Jika tidak ada pasangan yang cocok, kolom-kolom yang berasal dari tabel yang tidak memiliki pasangan akan diisi dengan NULL.

Daftar semua mahasiswa (termasuk yang belum mempunyai dosen pembimbing) dan semua dosen (termasuk yang tidak membimbing), keduanya dijoinkan.

Dosen ⟗ Mahasiswa ≡ Mahasiswa ⟗ Dosen

SELECT NIM, Mahasiswa.Nama,
       Dosen.NRP,
       Dosen.nama As NamaPA
FROM Dosen FULL OUTER JOIN
     Mahasiswa
ON Mahasiswa.NRP=Dosen.NRP

Hasilnya:

NIM  Nama             NRP  NamaPA
---- ---------------- ---- ---------------
347  Rizki            01   Budi
NULL NULL             02   Rizka
345  Mawar            03   Anti
348  Anta             03   Anti
346  Budi             NULL NULL

(5 rows affected)

Ada DBMS yang tidak mendukung full outer join, misalnya MySQL. Sebagai gantinya, Anda dapat melakukan union terhadap left ouer join dan right outer join.

SELECT Mahasiswa.*,
       Dosen.nama AS NamaPA
FROM Mahasiswa LEFT OUTER JOIN
     Dosen ON Mahasiswa.NRP=Dosen.NRP
UNION
SELECT Mahasiswa.*,
       Dosen.nama As NamaPA
FROM Mahasiswa RIGHT OUTER JOIN
     Dosen
ON Mahasiswa.NRP=Dosen.NRP

Hasilnya sama dengan full outer join.

Latihan

Bagaimana ekspresi aljabar relasional dan query-nya untuk memperoleh:

1. Daftar semua dosen (termasuk yang tidak membimbing) serta NIM dan nama mahasiswa bimbingannya (kalau ada).
2. Daftar dosen bukan PA (dosen yang tidak membimbing mahasiswa).
3. Daftar mahasiswa yang belum memiliki dosen PA dan dosen yang belum memiliki bimbingan (keduanya dijoinkan).

Optimasi Query

Berikut adalah beberapa langkah untuk mengoptimalkan query dengan join.

1. Indeks yang efisien. Pastikan bahwa semua kolom yang digunakan dalam kondisi join dan pencarian memiliki indeks yang sesuai. Indeks mempercepat proses pencarian dan pengombinasian data. Jika memungkinkan, pertimbangkan penggunaan indeks komposit pada beberapa kolom yang sering digunakan bersama dalam kondisi join.
2. Operasi proyeksi. Alihkan perhatian hanya pada kolom-kolom yang diperlukan dalam hasil query. Mengambil semua kolom yang tersedia bisa mengurangi performa query.
3. Gunakan alias untuk membuat query lebih ringkas dan lebih mudah dibaca. Alias juga membantu dalam pengombinasian tabel jika Anda mengombinasikan tabel yang sama berkali-kali dalam query.
4. Operasi seleksi. Gunakan klausa WHERE untuk membatasi jumlah data yang diambil dalam hasil query. Ini mengurangi jumlah data yang perlu dikombinasikan. Filter data sebelum melakukan join jika memungkinkan, untuk mengurangi jumlah data yang harus dikombinasikan.
5. Gunakan jenis join yang sesuai. Pilih jenis join yang sesuai dengan kebutuhan analisis Anda. Inner join cenderung lebih cepat daripada left outer join, right outer join, atau full outer join karena hasilnya lebih terfokus.
6. Memantau kinerja. Gunakan alat pemantauan kinerja untuk memantau eksekusi query Anda. Ini akan membantu Anda mengidentifikasi query yang lambat dan mengoptimalkan mereka.
7. Denormalisasi data berdasarkan kebutuhan. Jika Anda memiliki tabel yang besar dan kompleks, pertimbangkan denormalisasi data. Ini dapat mengurangi jumlah operasi join yang diperlukan.
8. Upgrade perangkat keras atau sumber daya basis data. Jika performa tetap menjadi masalah, pertimbangkan untuk meningkatkan perangkat keras atau sumber daya basis data, seperti memperluas RAM atau meningkatkan kapasitas CPU.
9. Query tuning dan optimizer hints. Anda dapat menggunakan fitur “query tuning” atau “optimizer hints” yang disediakan oleh sistem manajemen basis data Anda. Ini memungkinkan Anda untuk memberikan petunjuk kepada optimizer query untuk menjalankan query dengan metode yang lebih efisien.

Optimasi query dengan join adalah pekerjaan yang cermat, dan hasil yang optimal akan bervariasi tergantung pada kasus tertentu.

Penutup

Basis data sering kali terdiri atas beberapa tabel yang terkait satu sama lain. Melalui operasi join, Anda dapat mengintegrasikan data dari tabel-tabel ini dan mengakses informasi yang lebih komprehensif.

Penting untuk memantau kinerja, melakukan uji coba, dan mengikuti praktik terbaik dalam penggunaan join untuk mencapai performa yang optimal.

Referensi

Joins (SQL Server) - SQL Server

Database Systems: A Practical Approach to Design, Implementation, and Management, Global Edition