SQL untuk Pemula: Bagian 3 - Relasi Database

() translation by (you can also view the original English article)

Hari ini, kami melanjutkan perjalanan kami ke dunia sistem database relasional dan SQL. Dalam bagian tiga seri ini, kita akan mempelajari bagaimana bekerja dengan beberapa tabel yang memiliki relasi satu sama lain. Pertama, kita akan pergi ke beberapa konsep inti, dan kemudian akan mulai bekerja dengan query JOIN di SQL.

Anda juga dapat melihat database SQL dalam aksi dengan memeriksa skrip SQL, apps dan add-ons di Envato Market.

Mengejar

Pengenalan

Ketika membuat sebuah database, akal sehat mendikte kita menggunakan tabel-tabel terpisah untuk berbagai jenis entitas. Beberapa contoh adalah: customers, orders, items, messages dll... Tapi kita juga harus memiliki relasi antara tabel-tabel tersebut. Misalnya, customers membuat orders, dan orders berisi items. Relasi ini harus diwakili dalam database. Juga, ketika mengambil data dengan SQL, kita perlu menggunakan beberapa jenis query JOIN untuk mendapatkan apa yang kita butuhkan.

Ada beberapa jenis relasi database. Hari ini kita akan membahas berikut:

Relasi One to One
Relasi One to Many dan Many to One
Relasi Many to Many
Relasi Referensi Mandiri

Ketika memilih data dari beberapa tabel dengan relasi ini, kita akan menggunakan JOIN query. Ada beberapa jenis JOIN, dan kita akan belajar tentang hal berikut:

Cross Joins
Natural Joins
Inner Joins
Left (Outer) Joins
Right (Outer) Joins

Kita juga akan belajar tentang kalimat ON dan klausa USING.

Relasi One to One

Katakanlah Anda memiliki sebuah tabel untuk customers:

Kita dapat menempatkan informasi alamat customer pada tabel yang terpisah:

Sekarang kita memiliki relasi antara tabel Customers dan tabel Addresses. Jika address masing-masing hanya dapat menjadi milik satu customer, relasi ini adalah "One to One". Perlu diingat bahwa relasi semacam ini sangat tidak umum. Tabel awal kami yang disertakan address bersama dengan customer bisa bekerja baik dalam kebanyakan kasus.

Perhatikan bahwa sekarang ada sebuah field yang bernama "address_id" di tabel Customers, yang mengacu pada record yang cocok dalam tabel Address. Ini disebut "Foreign Key" dan digunakan untuk semua jenis relasi database. Kami akan membahas hal ini kemudian di dalam artikel.

Kita dapat memvisualisasikan relasi antara record customer dan address seperti ini:

Perhatikan bahwa adanya suatu relasi dapat opsional, seperti memiliki record customer yang telah ada yang berkaitan dengan record address.

Relasi One to Many dan Many to One

Ini adalah jenis yang paling umum digunakan dari relasi. Pertimbangkan sebuah situs web e-commerce, dengan berikut:

Customers dapat membuat banyak orders.
Orders dapat berisi banyak items.
Items dapat memiliki deskripsi dalam banyak bahasa.

Dalam kasus ini kita akan perlu untuk membuat relasi "One to Many". Berikut adalah contohnya:

Setiap customer dapat memiliki nol, satu atau beberapa orders. Tapi sebuah order dapat hanya menjadi milik satu customer.

Relasi Many to Many

Dalam beberapa kasus, Anda mungkin perlu beberapa contoh relasi pada kedua sisi. Misalnya, setiap order dapat berisi beberapa items. Dan masing-masing item juga bisa dalam beberapa orders.

Untuk relasi ini, kita perlu membuat sebuah tabel tambahan:

Tabel Items_Orders hanya memiliki satu tujuan, dan itu adalah untuk menciptakan sebuah relasi "Many to Many" antara items dan orders.

Berikut adalah bagaimana kita dapat memvisualisasikan relasi semacam ini:

Jika Anda ingin menyertakan record items_orders pada grafik, ini dapat terlihat seperti ini:

Relasi Referensi Mandiri

Ini digunakan ketika sebuah tabel membutuhkan untuk memiliki relasi dengan dirinya sendiri. Sebagai contoh, katakanlah Anda memiliki program referral. Customers dapat merujuk customers lain ke situs belanja Anda. Tabel dapat terlihat seperti ini:

Customers 102 dan 103 dirujuk oleh customer 101.

Ini sebenarnya juga dapat mirip dengan relasi "one to many" karena satu pelanggan dapat merujuk ke beberapa pelanggan. Juga hal ini dapat divisualisasikan seperti struktur pohon:

Satu customer dapat merujuk nol, satu atau beberapa customers. Setiap customer bisa dirujuk juga dengan hanya satu customer, atau tidak sama sekali.

Jika Anda ingin membuat referensi mandiri relasi "many to many", Anda akan membutuhkan sebuah tabel tambahan seperti yang kita bicarakan dalam bagian sebelumnya.

Foreign Key

Sejauh ini kita telah belajar tentang beberapa konsep. Sekarang adalah waktunya menghidupkan mereka dengan menggunakan SQL. Untuk bagian ini, kita perlu memahami apa itu Foreign Key.

Dalam contoh-contoh relasi di atas, kami selalu memiliki field ini "****_id" yang direferensikan sebuah kolom di tabel yang lain. Dalam contoh ini, kolom customer_id dalam tabel Orders adalah kolom Foreign Key:

Dengan database seperti MySQL, ada dua cara untuk membuat kolom foreign key kolom:

Mendefinisikan Foreign Key secara eksplisit

Mari kita membuat tabel customers sederhana:

1	CREATE TABLE customers (
2	customer_id INT AUTO_INCREMENT PRIMARY KEY,
3	customer_name VARCHAR(100)
4	);

Sekarang tabel orders, yang akan berisi Foreign Key:

CREATE TABLE orders (
	order_id INT AUTO_INCREMENT PRIMARY KEY,
	customer_id INT,
	amount DOUBLE,
	FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Kedua kolom (customers.customer_id dan orders.customer_id) harus dengan struktur data yang sama persis. Jika salah satunya adalah INT, yang lain tidak boleh BIGINT misalnya.

Harap dicatat bahwa di MySQL hanya mesin InnoDB yang memiliki dukungan penuh untuk Foreign Key. Tapi mesin penyimpanan lainnya masih akan memungkinkan Anda untuk menentukan mereka tanpa ada kesalahan. Juga kolom Foreign Key adalah yang diindeks secara otomatis, kecuali jika Anda menentukan indeks lain untuknya.

Tanpa Deklarasi Eksplisit

Tabel orders yang sama dapat dibuat tanpa menyatakan kolom customer_id untuk menjadi Foreign Key secara eksplisit:

CREATE TABLE orders (
	order_id INT AUTO_INCREMENT PRIMARY KEY,
	customer_id INT,
	amount DOUBLE,
	INDEX (customer_id)
);

Ketika mengambil data dengan query JOIN, Anda masih bisa memperlakukan kolom ini sebagai Foreign Key meskipun mesin database tidak menyadari relasi ini.

1	SELECT * FROM orders
2	JOIN customers USING(customer_id)

Kita akan belajar tentang query JOIN lebih lanjut dalam artikel ini.

Memvisualisasikan Relasi

Perangkat lunak favorit saya saat ini untuk merancang database dan memvisualisasikan relasi Foreign Key adalah MySQL Workbench.

Setelah Anda merancang database Anda, Anda dapat mengekspor SQL dan menjalankan pada server Anda. Hadir yang sangat berguna untuk desain database yang lebih besar dan lebih kompleks.

Query JOIN

Untuk mengambil data dari database yang memiliki relasi, kita sering perlu untuk menggunakan query JOIN.

Sebelum kita mulai, mari kita membuat tabel dan beberapa sampel data untuk bekerja dengannya.

CREATE TABLE customers (
	customer_id INT AUTO_INCREMENT PRIMARY KEY,
	customer_name VARCHAR(100)
);

CREATE TABLE orders (
	order_id INT AUTO_INCREMENT PRIMARY KEY,
	customer_id INT,
	amount DOUBLE,
	FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO `customers` (`customer_id`, `customer_name`) VALUES
(1, 'Adam'),
(2, 'Andy'),
(3, 'Joe'),
(4, 'Sandy');

INSERT INTO `orders` (`order_id`, `customer_id`, `amount`) VALUES
(1, 1, 19.99),
(2, 1, 35.15),
(3, 3, 17.56),
(4, 4, 12.34);

Kami memiliki 4 customers. Satu customer memiliki dua orders, dua customers memiliki satu order masing-masing, dan salah satu customer tidak memiliki order. Sekarang mari kita lihat berbagai jenis query JOIN yang dapat kita jalankan di tabel ini.

Cross Join

Ini adalah default tipe query JOIN ketika tidak ada kondisi yang ditentukan.

Hasilnya adalah apa yang disebut "produk Cartesian" dari tabel. Itu berarti bahwa setiap baris dari tabel pertama dicocokkan dengan setiap baris dari tabel kedua. Karena setiap tabel memiliki 4 baris, kami akhirnya mendapatkan hasil 16 baris.

Kata kunci JOIN secara opsional dapat diganti dengan koma sebagai gantinya.

Tentu saja hasil seperti ini biasanya tidak berguna. Jadi mari kita lihat jenis penggabungan lainnya.

Natural Join

Dengan query JOIN semacam ini, tabel harus memiliki pencocokan nama kolom. Dalam kasus kami, kedua tabel memiliki kolom customer_id. Jadi, MySQL akan menggabung record hanya ketika nilai dari kolom ini adalah cocok pada dua record.

Seperti yang Anda lihat kolom customer_id ini hanya ditampilkan sekali saat ini, karena mesin database memperlakukan ini sebagai kolom umum. Kita dapat melihat dua orders yang dilakukan oleh Adam, dan dua orders lainnya oleh Joe dan Sandy. Akhirnya kami mendapatkan beberapa informasi yang berguna.

Inner Join

Ketika penggabungan dengan kondisi yang ditentukan, Inner Join dilakukan. Dalam kasus ini, akan menjadi ide yang baik untuk memiliki field customer_id yang cocok pada kedua tabel. Hasilnya harus sama dengan Natural Join.

Hasil yang sama kecuali ada sedikit perbedaan. Kolom customer_id ini diulang dua kali, sekali untuk setiap tabel. Alasannya adalah, kita hanya bertanya database untuk mencocokkan nilai-nilai pada dua kolom. Tetapi itu adalah benar-benar tidak menyadari bahwa mereka mewakili informasi yang sama.

Mari kita tambahkan beberapa kondisi lagi ke query.

Kali ini kami hanya menerima orders lebih dari $15.

Klausa ON

Sebelum pindah ke penggabungan jenis lainnya, kita perlu melihat pada klausa ON. Ini berguna untuk menempatkan kondisi JOIN di klausa terpisah.

Sekarang kita dapat membedakan kondisi JOIN dari klausa kondisi WHERE. Tapi juga ada sedikit perbedaan dalam fungsi. Kita akan melihatnya di contoh-contoh LEFT JOIN.

Klausa USING

Klausa USING mirip dengan klausa ON, tapi lebih pendek. Jika kolom dengan nama yang sama pada kedua tabel, kita dapat menentukannya di sini.

Pada kenyataannya, ini adalah mirip dengan NATURAL JOIN, sehingga dengan kolom yang bergabung (customer_id) tidak diulang dua kali dalam hasilnya.

Left (Outer) Join

LEFT JOIN adalah jenis Outer Join. Dalam query ini, jika tidak ada kecocokan ditemukan dari kedua tabel, record dari tabel pertama masih ditampilkan.

Meskipun Andy tidak memiliki orders, record-nya masih ditampilkan. Nilai-nilai di bawah kolom dari tabel kedua adalah ditetapkan ke NULL.

Ini juga berguna untuk menemukan record yang tidak memiliki relasi. Misalnya, kita bisa mencari customers yang belum melakukan orders.

Semua yang kita lakukan adalah mencari nilai-nilai NULL untuk order_id.

Juga perhatikan bahwa kata kunci OUTER adalah opsional. Anda hanya dapat menggunakan LEFT JOIN, bukan LEFT OUTER JOIN.

Conditional

Sekarang mari kita lihat sebuah query dengan kondisi.

Jadi apa yang terjadi dengan Andy dan Sandy? KIRI BERGABUNG seharusnya mengembalikan customers yang tidak sesuai dengan orders. Masalahnya adalah bahwa di klausa WHERE memblokir hasil tersebut. Untuk mendapatkan mereka, kita dapat mencoba memasukkan kondisi NULL juga.

Kita punya Andy tapi tidak Sandy. Ini masih tidak terlihat benar. Untuk mendapatkan apa yang kita inginkan, kita perlu menggunakan klausa ON.

Sekarang kita punya semua orang, dan semua orders di atas $15. Seperti yang saya katakan sebelumnya, klausa ON kadang-kadang memiliki sedikit fungsi yang berbeda dari klausa WHERE. i Outer Join seperti yang satu ini, baris akan dimasukkan bahkan jika mereka tidak cocok dengan klausa kondisi ON.

Right (Outer) Join

RIGHT OUTER JOIN bekerja persis sama, tetapi urutan tabel terbalik.

Saat ini kami tidak memiliki hasil NULL karena setiap order memiliki kecocokan dengan record customer. Kita dapat mengubah urutan dari tabel dan mendapatkan hasil yang sama seperti yang kita lakukan dari LEFT OUTER JOIN.

Sekarang kita memiliki nilai-nilai NULL karena tabel customers ada pada sisi kanan penggabungan.

Kesimpulan

Terima kasih telah membaca artikel ini. Saya harap Anda menikmatinya! Silakan tinggalkan komentar dan pertanyaan, dan miliki hari yang besar!

Jangan lupa untuk memeriksa skrip SQL, apps dan add-ons kami di Envato Market. Anda akan mendapatkan rasa dari apa yang mungkin dengan database SQL, dan Anda mungkin menemukan solusi yang tepat untuk membantu Anda dengan pengembangan proyek Anda saat ini.

Ikuti kami di Twitter, atau berlangganan ke Nettuts+ RSS Feed untuk tutorial pengembangan web terbaik di web.