Ringkasan. dalam tutorial ini, Anda akan belajar bagaimana menggunakan klausa MySQL INNER JOIN untuk memilih data dari beberapa tabel berdasarkan kondisi gabungan
Pengantar MySQL ________65______ klausa
INNER JOIN_ cocok dengan setiap baris dalam satu tabel dengan setiap baris dalam tabel lain dan memungkinkan Anda untuk membuat kueri baris yang berisi kolom dari kedua tabel
INNER JOIN adalah klausa opsional dari pernyataan
SELECT select_list FROM t1 INNER JOIN t2 ON join_condition;
Code language: SQL (Structured Query Language) (sql)3. Itu muncul segera setelah klausaSELECT select_list FROM t1 INNER JOIN t2 ON join_condition;
Code language: SQL (Structured Query Language) (sql)4. Berikut adalah sintaks dari klausa INNER JOINSELECT select_list FROM t1 INNER JOIN t2 ON join_condition1 INNER JOIN t3 ON join_condition2 ...;
Code language: SQL (Structured Query Language) (sql)Dalam sintaks ini
- Pertama, tentukan tabel utama yang muncul di klausa
SELECT select_list FROM t1 INNER JOIN t2 ON join_condition;
Code language: SQL (Structured Query Language) (sql)4 (SELECT select_list FROM t1 INNER JOIN t2 ON join_condition;
Code language: SQL (Structured Query Language) (sql)7) - Kedua, tentukan tabel yang akan digabungkan dengan tabel utama, yang muncul di klausa INNER JOIN (
SELECT select_list FROM t1 INNER JOIN t2 ON join_condition;
Code language: SQL (Structured Query Language) (sql)9,SELECT productCode, productName, textDescription FROM products t1 INNER JOIN productlines t2 ON t1.productline = t2.productline;
Code language: SQL (Structured Query Language) (sql)0,…) - Ketiga, tentukan kondisi bergabung setelah kata kunci
SELECT productCode, productName, textDescription FROM products t1 INNER JOIN productlines t2 ON t1.productline = t2.productline;
Code language: SQL (Structured Query Language) (sql)1 dari klausa INNER JOIN. Kondisi gabungan menentukan aturan untuk mencocokkan baris antara tabel utama dan tabel yang muncul di klausa INNER JOIN
Dengan asumsi Anda ingin menggabungkan dua tabel
SELECT select_list FROM t1 INNER JOIN t2 ON join_condition;
Code language: SQL (Structured Query Language) (sql)7 danSELECT select_list FROM t1 INNER JOIN t2 ON join_condition;
Code language: SQL (Structured Query Language) (sql)9Pernyataan berikut mengilustrasikan cara menggabungkan dua tabel
SELECT select_list FROM t1 INNER JOIN t2 ON join_condition;
Code language: SQL (Structured Query Language) (sql)7 danSELECT select_list FROM t1 INNER JOIN t2 ON join_condition;
Code language: SQL (Structured Query Language) (sql)9 menggunakan klausa INNER JOINSELECT select_list FROM t1 INNER JOIN t2 ON join_condition;
Code language: SQL (Structured Query Language) (sql)Klausa INNER JOIN membandingkan setiap baris dalam tabel
SELECT select_list FROM t1 INNER JOIN t2 ON join_condition;
Code language: SQL (Structured Query Language) (sql)7 dengan setiap baris dalam tabelSELECT select_list FROM t1 INNER JOIN t2 ON join_condition;
Code language: SQL (Structured Query Language) (sql)9 berdasarkan kondisi gabunganJika baris dari kedua tabel menyebabkan kondisi gabungan dievaluasi menjadi
SELECT productCode, productName, textDescription FROM products INNER JOIN productlines USING (productline);
Code language: SQL (Structured Query Language) (sql)2, INNER JOIN membuat baris baru yang kolomnya berisi semua kolom baris dari tabel dan menyertakan baris baru ini dalam rangkaian hasil. Jika tidak, INNER JOIN hanya mengabaikan barisJika tidak ada baris di antara tabel yang menyebabkan kondisi gabungan dievaluasi menjadi
SELECT productCode, productName, textDescription FROM products INNER JOIN productlines USING (productline);
Code language: SQL (Structured Query Language) (sql)2, INNER JOIN mengembalikan kumpulan hasil kosong. Logika ini juga diterapkan saat Anda menggabungkan lebih dari 2 tabelDiagram Venn berikut mengilustrasikan cara kerja klausa INNER JOIN
MySQL INNER JOIN contoh
Mari kita lihat tabel
SELECT productCode, productName, textDescription FROM products INNER JOIN productlines USING (productline);
Code language: SQL (Structured Query Language) (sql)_9 danSELECT t1.orderNumber, t1.status, SUM(quantityOrdered * priceEach) total FROM orders t1 INNER JOIN orderdetails t2 ON t1.orderNumber = t2.orderNumber GROUP BY orderNumber;
Code language: SQL (Structured Query Language) (sql)0 di database sampelDalam diagram ini, tabel
SELECT productCode, productName, textDescription FROM products INNER JOIN productlines USING (productline);
Code language: SQL (Structured Query Language) (sql)_9 memiliki kolomSELECT t1.orderNumber, t1.status, SUM(quantityOrdered * priceEach) total FROM orders t1 INNER JOIN orderdetails t2 ON t1.orderNumber = t2.orderNumber GROUP BY orderNumber;
Code language: SQL (Structured Query Language) (sql)2 yang mereferensikan kolomSELECT t1.orderNumber, t1.status, SUM(quantityOrdered * priceEach) total FROM orders t1 INNER JOIN orderdetails t2 ON t1.orderNumber = t2.orderNumber GROUP BY orderNumber;
Code language: SQL (Structured Query Language) (sql)3 dari tabelSELECT t1.orderNumber, t1.status, SUM(quantityOrdered * priceEach) total FROM orders t1 INNER JOIN orderdetails t2 ON t1.orderNumber = t2.orderNumber GROUP BY orderNumber;
Code language: SQL (Structured Query Language) (sql)0. KolomSELECT t1.orderNumber, t1.status, SUM(quantityOrdered * priceEach) total FROM orders t1 INNER JOIN orderdetails t2 ON t1.orderNumber = t2.orderNumber GROUP BY orderNumber;
Code language: SQL (Structured Query Language) (sql)_2 dalam tabelSELECT productCode, productName, textDescription FROM products INNER JOIN productlines USING (productline);
Code language: SQL (Structured Query Language) (sql)9 disebut kolom kunci asingBiasanya, Anda menggabungkan tabel yang memiliki hubungan kunci asing seperti
SELECT t1.orderNumber, t1.status, SUM(quantityOrdered * priceEach) total FROM orders t1 INNER JOIN orderdetails t2 ON t1.orderNumber = t2.orderNumber GROUP BY orderNumber;
Code language: SQL (Structured Query Language) (sql)0 danSELECT productCode, productName, textDescription FROM products INNER JOIN productlines USING (productline);
Code language: SQL (Structured Query Language) (sql)9 tabelMisalkan Anda ingin mendapatkan
SELECT t1.orderNumber, t1.status, SUM(quantityOrdered * priceEach) total FROM orders t1 INNER JOIN orderdetails t2 ON t1.orderNumber = t2.orderNumber GROUP BY orderNumber;
Code language: SQL (Structured Query Language) (sql)_9 danSELECT orderNumber, status, SUM(quantityOrdered * priceEach) total FROM orders INNER JOIN orderdetails USING (orderNumber) GROUP BY orderNumber;
Code language: SQL (Structured Query Language) (sql)0 dari tabelSELECT productCode, productName, textDescription FROM products INNER JOIN productlines USING (productline);
Code language: SQL (Structured Query Language) (sql)9SELECT orderNumber, status, SUM(quantityOrdered * priceEach) total FROM orders INNER JOIN orderdetails USING (orderNumber) GROUP BY orderNumber;
Code language: SQL (Structured Query Language) (sql)2 lini produk dari tabelSELECT t1.orderNumber, t1.status, SUM(quantityOrdered * priceEach) total FROM orders t1 INNER JOIN orderdetails t2 ON t1.orderNumber = t2.orderNumber GROUP BY orderNumber;
Code language: SQL (Structured Query Language) (sql)0
Untuk melakukannya, Anda perlu memilih data dari kedua tabel dengan mencocokkan baris berdasarkan nilai di kolom
SELECT t1.orderNumber, t1.status, SUM(quantityOrdered * priceEach) total FROM orders t1 INNER JOIN orderdetails t2 ON t1.orderNumber = t2.orderNumber GROUP BY orderNumber;
Code language: SQL (Structured Query Language) (sql)3 menggunakan klausa INNER JOIN sebagai berikutSELECT productCode, productName, textDescription FROM products t1 INNER JOIN productlines t2 ON t1.productline = t2.productline;
Code language: SQL (Structured Query Language) (sql)_Karena kolom gabungan dari kedua tabel memiliki nama yang sama
SELECT t1.orderNumber, t1.status, SUM(quantityOrdered * priceEach) total FROM orders t1 INNER JOIN orderdetails t2 ON t1.orderNumber = t2.orderNumber GROUP BY orderNumber;
Code language: SQL (Structured Query Language) (sql)3, Anda dapat menggunakan sintaksSELECT orderNumber, status, SUM(quantityOrdered * priceEach) total FROM orders INNER JOIN orderdetails USING (orderNumber) GROUP BY orderNumber;
Code language: SQL (Structured Query Language) (sql)7SELECT productCode, productName, textDescription FROM products INNER JOIN productlines USING (productline);
Code language: SQL (Structured Query Language) (sql)Kueri mengembalikan set hasil yang sama. Namun, sintaks
SELECT orderNumber, status, SUM(quantityOrdered * priceEach) total FROM orders INNER JOIN orderdetails USING (orderNumber) GROUP BY orderNumber;
Code language: SQL (Structured Query Language) (sql)_7 jauh lebih pendek dan bersihMySQL INNER JOIN dengan contoh klausa
SELECT orderNumber, orderDate, orderLineNumber, productName, quantityOrdered, priceEach FROM orders INNER JOIN orderdetails USING (orderNumber) INNER JOIN products USING (productCode) ORDER BY orderNumber, orderLineNumber;
Code language: SQL (Structured Query Language) (sql)0Lihat tabel
SELECT orderNumber, orderDate, orderLineNumber, productName, quantityOrdered, priceEach FROM orders INNER JOIN orderdetails USING (orderNumber) INNER JOIN products USING (productCode) ORDER BY orderNumber, orderLineNumber;
Code language: SQL (Structured Query Language) (sql)1 danSELECT orderNumber, orderDate, orderLineNumber, productName, quantityOrdered, priceEach FROM orders INNER JOIN orderdetails USING (orderNumber) INNER JOIN products USING (productCode) ORDER BY orderNumber, orderLineNumber;
Code language: SQL (Structured Query Language) (sql)2 berikutKueri ini mengembalikan nomor pesanan, status pesanan, dan total penjualan dari tabel
SELECT orderNumber, orderDate, orderLineNumber, productName, quantityOrdered, priceEach FROM orders INNER JOIN orderdetails USING (orderNumber) INNER JOIN products USING (productCode) ORDER BY orderNumber, orderLineNumber;
Code language: SQL (Structured Query Language) (sql)1 danSELECT orderNumber, orderDate, orderLineNumber, productName, quantityOrdered, priceEach FROM orders INNER JOIN orderdetails USING (orderNumber) INNER JOIN products USING (productCode) ORDER BY orderNumber, orderLineNumber;
Code language: SQL (Structured Query Language) (sql)2 menggunakan klausa INNER JOIN dengan klausaSELECT orderNumber, orderDate, orderLineNumber, productName, quantityOrdered, priceEach FROM orders INNER JOIN orderdetails USING (orderNumber) INNER JOIN products USING (productCode) ORDER BY orderNumber, orderLineNumber;
Code language: SQL (Structured Query Language) (sql)6SELECT t1.orderNumber, t1.status, SUM(quantityOrdered * priceEach) total FROM orders t1 INNER JOIN orderdetails t2 ON t1.orderNumber = t2.orderNumber GROUP BY orderNumber;
Code language: SQL (Structured Query Language) (sql)Demikian pula, kueri berikut menggunakan INNER JOIN dengan sintaks
SELECT orderNumber, status, SUM(quantityOrdered * priceEach) total FROM orders INNER JOIN orderdetails USING (orderNumber) GROUP BY orderNumber;
Code language: SQL (Structured Query Language) (sql)7SELECT orderNumber, status, SUM(quantityOrdered * priceEach) total FROM orders INNER JOIN orderdetails USING (orderNumber) GROUP BY orderNumber;
Code language: SQL (Structured Query Language) (sql)MySQL INNER JOIN – gabungkan tiga tabel contoh
Lihat tabel
SELECT productCode, productName, textDescription FROM products INNER JOIN productlines USING (productline);
Code language: SQL (Structured Query Language) (sql)_9,SELECT orderNumber, orderDate, orderLineNumber, productName, quantityOrdered, priceEach FROM orders INNER JOIN orderdetails USING (orderNumber) INNER JOIN products USING (productCode) ORDER BY orderNumber, orderLineNumber;
Code language: SQL (Structured Query Language) (sql)1 danSELECT orderNumber, orderDate, orderLineNumber, productName, quantityOrdered, priceEach FROM orders INNER JOIN orderdetails USING (orderNumber) INNER JOIN products USING (productCode) ORDER BY orderNumber, orderLineNumber;
Kueri ini menggunakan dua klausa INNER JOIN untuk menggabungkan tiga tabel.
SELECT orderNumber, orderDate, orderLineNumber, productName, quantityOrdered, priceEach FROM orders INNER JOIN orderdetails USING (orderNumber) INNER JOIN products USING (productCode) ORDER BY orderNumber, orderLineNumber;
Code language: SQL (Structured Query Language) (sql)1,SELECT orderNumber, orderDate, orderLineNumber, productName, quantityOrdered, priceEach FROM orders INNER JOIN orderdetails USING (orderNumber) INNER JOIN products USING (productCode) ORDER BY orderNumber, orderLineNumber;
Code language: SQL (Structured Query Language) (sql)2, danSELECT productCode, productName, textDescription FROM products INNER JOIN productlines USING (productline);
Code language: SQL (Structured Query Language) (sql)9SELECT orderNumber, orderDate, orderLineNumber, productName, quantityOrdered, priceEach FROM orders INNER JOIN orderdetails USING (orderNumber) INNER JOIN products USING (productCode) ORDER BY orderNumber, orderLineNumber;
Code language: SQL (Structured Query Language) (sql)Gambar ini menunjukkan output parsial
MySQL INNER JOIN_ – gabungkan empat tabel contoh
Lihat tabel
SELECT orderNumber, orderDate, orderLineNumber, productName, quantityOrdered, priceEach FROM orders INNER JOIN orderdetails USING (orderNumber) INNER JOIN products USING (productCode) ORDER BY orderNumber, orderLineNumber;
Code language: SQL (Structured Query Language) (sql)_1,SELECT orderNumber, orderDate, orderLineNumber, productName, quantityOrdered, priceEach FROM orders INNER JOIN orderdetails USING (orderNumber) INNER JOIN products USING (productCode) ORDER BY orderNumber, orderLineNumber;
Code language: SQL (Structured Query Language) (sql)2,SELECT orderNumber, productName, msrp, priceEach FROM products p INNER JOIN orderdetails o ON p.productcode = o.productcode AND p.msrp > o.priceEach WHERE p.productcode = 'S10_1678';
Code language: SQL (Structured Query Language) (sql)0 danSELECT productCode, productName, textDescription FROM products INNER JOIN productlines USING (productline);
Code language: SQL (Structured Query Language) (sql)9 berikutContoh ini menggunakan tiga klausa INNER JOIN_ untuk meminta data dari empat tabel di atas
SELECT orderNumber, orderDate, customerName, orderLineNumber, productName, quantityOrdered, priceEach FROM orders INNER JOIN orderdetails USING (orderNumber) INNER JOIN products USING (productCode) INNER JOIN customers USING (customerNumber) ORDER BY orderNumber, orderLineNumber;
Code language: SQL (Structured Query Language) (sql)MySQL INNER JOIN menggunakan operator lain
Sejauh ini, Anda telah melihat bahwa kondisi gabungan menggunakan operator yang sama (=) untuk mencocokkan baris
Selain operator yang sama (=), Anda dapat menggunakan operator lain seperti operator lebih besar dari (
SELECT orderNumber, productName, msrp, priceEach FROM products p INNER JOIN orderdetails o ON p.productcode = o.productcode AND p.msrp > o.priceEach WHERE p.productcode = 'S10_1678';
Code language: SQL (Structured Query Language) (sql)4), kurang dari (SELECT orderNumber, productName, msrp, priceEach FROM products p INNER JOIN orderdetails o ON p.productcode = o.productcode AND p.msrp > o.priceEach WHERE p.productcode = 'S10_1678';
Code language: SQL (Structured Query Language) (sql)5), dan tidak sama (SELECT orderNumber, productName, msrp, priceEach FROM products p INNER JOIN orderdetails o ON p.productcode = o.productcode AND p.msrp > o.priceEach WHERE p.productcode = 'S10_1678';
Code language: SQL (Structured Query Language) (sql)6) untuk membentuk kondisi gabunganKueri berikut menggunakan gabungan kurang dari (
SELECT orderNumber, productName, msrp, priceEach FROM products p INNER JOIN orderdetails o ON p.productcode = o.productcode AND p.msrp > o.priceEach WHERE p.productcode = 'S10_1678';
Code language: SQL (Structured Query Language) (sql)5) untuk menemukan harga jual produk dengan kodeSELECT orderNumber, productName, msrp, priceEach FROM products p INNER JOIN orderdetails o ON p.productcode = o.productcode AND p.msrp > o.priceEach WHERE p.productcode = 'S10_1678';
Code language: SQL (Structured Query Language) (sql)8 yang lebih rendah dari harga eceran yang disarankan produsen (MSRP) untuk produk tersebutSELECT orderNumber, productName, msrp, priceEach FROM products p INNER JOIN orderdetails o ON p.productcode = o.productcode AND p.msrp > o.priceEach WHERE p.productcode = 'S10_1678';
Code language: SQL (Structured Query Language) (sql)Dalam tutorial ini, Anda telah mempelajari cara menggunakan MySQL INNER JOIN untuk meminta data dari beberapa tabel.