Dapatkah Anda bergabung dalam 3 tabel mysql?

Question

Ringkasan. dalam tutorial ini, Anda akan belajar bagaimana menggunakan klausa MySQL INNER JOIN untuk memilih data dari beberapa tabel berdasarkan kondisi gabungan

Table of Contents Show

Pengantar MySQL ________65______ klausa
MySQL INNER JOIN contoh
MySQL 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; (adsbygoogle = window.adsbygoogle || []).push({}); Code language: SQL (Structured Query Language) (sql)0
MySQL INNER JOIN – gabungkan tiga tabel contoh
MySQL INNER JOIN_ – gabungkan empat tabel contoh
MySQL INNER JOIN menggunakan operator lain
Bagaimana cara menggabungkan 3 tabel dalam kueri SQL?
Bagaimana cara menggabungkan tiga tabel dengan gabungan dalam?
Bisakah Anda bergabung dengan 3 tabel di SQL?

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 klausa

SELECT 
     select_list
FROM 
     t1
INNER JOIN t2 ON join_condition;
Code language: SQL (Structured Query Language) (sql)

4. Berikut adalah sintaks dari klausa INNER JOIN

SELECT
    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 dan

SELECT 
     select_list
FROM 
     t1
INNER JOIN t2 ON join_condition;
Code language: SQL (Structured Query Language) (sql)

9

Pernyataan 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 dan

SELECT 
     select_list
FROM 
     t1
INNER JOIN t2 ON join_condition;
Code language: SQL (Structured Query Language) (sql)

9 menggunakan klausa INNER JOIN

SELECT 
     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 tabel

SELECT 
     select_list
FROM 
     t1
INNER JOIN t2 ON join_condition;
Code language: SQL (Structured Query Language) (sql)

9 berdasarkan kondisi gabungan

Jika 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 baris

Jika 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 tabel

Diagram 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 dan

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 di database sampel

Dalam diagram ini, tabel

SELECT 
    productCode, 
    productName, 
    textDescription
FROM
    products
INNER JOIN productlines USING (productline);
Code language: SQL (Structured Query Language) (sql)

_9 memiliki 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)

2 yang mereferensikan 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 dari tabel

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. 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)

_2 dalam tabel

SELECT 
    productCode, 
    productName, 
    textDescription
FROM
    products
INNER JOIN productlines USING (productline);
Code language: SQL (Structured Query Language) (sql)

9 disebut kolom kunci asing

Biasanya, 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 dan

SELECT 
    productCode, 
    productName, 
    textDescription
FROM
    products
INNER JOIN productlines USING (productline);
Code language: SQL (Structured Query Language) (sql)

9 tabel

Misalkan 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 dan

SELECT 
    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 tabel

SELECT 
    productCode, 
    productName, 
    textDescription
FROM
    products
INNER JOIN productlines USING (productline);
Code language: SQL (Structured Query Language) (sql)

9

SELECT 
    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 tabel

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

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 berikut

SELECT 
    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 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

SELECT 
    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 bersih

MySQL 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; (adsbygoogle = window.adsbygoogle || []).push({}); Code language: SQL (Structured Query Language) (sql)0

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 dan

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 berikut

Kueri 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 dan

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 menggunakan klausa INNER JOIN dengan 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)

6

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)

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)

7

SELECT 
    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 dan

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 berikut

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, dan

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)

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 dan

SELECT 
    productCode, 
    productName, 
    textDescription
FROM
    products
INNER JOIN productlines USING (productline);
Code language: SQL (Structured Query Language) (sql)

9 berikut

Contoh 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 gabungan

Kueri 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 kode

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)

8 yang lebih rendah dari harga eceran yang disarankan produsen (MSRP) untuk produk tersebut

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)

Dalam tutorial ini, Anda telah mempelajari cara menggunakan MySQL INNER JOIN untuk meminta data dari beberapa tabel.

Bagaimana cara menggabungkan 3 tabel dalam kueri SQL?

Cara menggabungkan 3 tabel atau lebih di SQL .

Gabung Sederhana. Pertama, semua tabel digabungkan menggunakan kata kunci JOIN, kemudian digunakan klausa WHERE. DARI Karyawan e JOIN Gaji s JOIN Departemen d. DI MANA e. ID = s. Emp_ID DAN e. .

Gabung Bersarang. Pernyataan GABUNG bersarang digunakan dengan kata kunci ON. PILIH e. tanda pengenal, e. Nama, s. Gaji, d

Bagaimana cara menggabungkan tiga tabel dengan gabungan dalam?

Sintaks untuk multiple join. PILIH nama_kolom1,nama_kolom2,. FROM table_name1 INNER JOIN table_name2 ON condition_1 INNER JOIN table_name3 ON condition_2 INNER JOIN table_name4 ON condition_3. . . Catatan. Saat memilih hanya kolom tertentu gunakan table_name.

Bisakah Anda bergabung dengan 3 tabel di SQL?

Menggunakan JOIN di SQL tidak berarti Anda hanya dapat menggabungkan dua tabel. Anda dapat bergabung dengan 3, 4, atau bahkan lebih . Kemungkinannya tidak terbatas.

kode mysql