Pertanyaan Apa perbedaan antara "INNER JOIN" dan "OUTER JOIN"?


Juga bagaimana caranya LEFT JOIN, RIGHT JOIN dan FULL JOIN cocok?


4014
2017-09-01 22:36


asal


Jawaban:


Dengan asumsi Anda bergabung di kolom tanpa duplikat, yang merupakan kasus yang sangat umum:

  • Gabung batin A dan B memberikan hasil A berpotongan B, yaitu bagian dalam a diagram Venn persimpangan.

  • Gabungan luar A dan B memberikan hasil A union B, yaitu bagian terluar dari kesatuan diagram Venn.

Contoh

Misalkan Anda memiliki dua tabel, masing-masing dengan satu kolom, dan data sebagai berikut:

A    B
-    -
1    3
2    4
3    5
4    6

Perhatikan bahwa (1,2) unik untuk A, (3,4) umum, dan (5,6) unik untuk B.

Batin bergabung

Penggabungan dalam menggunakan salah satu kueri yang setara memberikan perpotongan dari dua tabel, yaitu dua baris yang sama.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Penggabungan luar kiri

Gabungan luar kiri akan memberikan semua baris dalam A, ditambah baris biasa dalam B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Penggabungan luar kanan

Penggabungan luar kanan akan memberikan semua baris dalam B, ditambah baris umum dalam A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Gabung luar penuh

Gabung luar penuh akan memberi Anda persatuan A dan B, yaitu semua baris dalam A dan semua baris di B. Jika sesuatu dalam A tidak memiliki datum yang sesuai dalam B, maka bagian B adalah nol, dan sebaliknya versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

5420
2017-09-01 22:59



Anda juga dapat mempertimbangkan skema berikut untuk tipe gabungan yang berbeda;

visual explanation of joins

Sumber: Visual-Representasi-of-SQL-Bergabung dijelaskan secara detail oleh C.L. Moffatt


2441
2018-05-16 23:03



saya merekomendasi Artikel blog Jeff. Deskripsi terbaik yang pernah saya lihat, plus ada visualisasi, misalnya:

Batin Bergabung:

enter image description here

Gabung Penuh Luar:

enter image description here


591
2017-08-30 11:52



Diagram Venn tidak benar-benar melakukannya untuk saya.

Mereka tidak menunjukkan perbedaan antara gabungan antara dan bergabung dalam, misalnya, atau lebih umum menunjukkan perbedaan antara berbagai jenis predikat bergabung atau memberikan kerangka untuk berpikir tentang bagaimana mereka akan beroperasi.

Tidak ada pengganti untuk memahami pemrosesan logis dan relatif mudah untuk dipahami.

  1. Bayangkan sebuah salib bergabung.
  2. Evaluasilah on klausa terhadap semua baris dari langkah 1 menjaga mereka di mana predikat yang dievaluasi true
  3. (Hanya untuk gabungan luar) tambahkan kembali pada baris luar yang hilang pada langkah 2.

(NB: Dalam prakteknya, pengoptimal kueri mungkin menemukan cara yang lebih efisien dalam mengeksekusi kueri daripada deskripsi logis murni di atas tetapi hasil akhirnya harus sama)

Saya akan mulai dengan versi animasi bergabung luar penuh. Penjelasan lebih lanjut berikut.

enter image description here


Penjelasan

Tabel Sumber

enter link description here

Pertama mulai dengan CROSS JOIN (AKA Cartesian Product). Ini tidak memiliki ON klausa dan hanya mengembalikan setiap permutasi baris dari dua tabel.

PILIH A.Colour, B.Colour DARI CROSS JOIN B

enter link description here

Inner dan Outer bergabung memiliki predikat klausa "ON".

  • Gabung Dalam. Evaluasi kondisi dalam klausa "ON" untuk semua baris dalam hasil gabungan silang. Jika benar kembalikan baris yang tergabung. Jika tidak, buang itu.
  • Left Outer Join. Sama seperti inner join maka untuk setiap baris di tabel kiri yang tidak cocok dengan apa pun output ini dengan nilai NULL untuk kolom tabel kanan.
  • Gabung Luar Kanan. Sama seperti inner join maka untuk setiap baris di tabel kanan yang tidak cocok dengan apa pun output ini dengan nilai NULL untuk kolom tabel kiri.
  • Gabung Penuh Luar. Sama seperti inner join lalu lestarikan baris kiri yang tidak cocok seperti pada outer join kiri dan baris kanan yang tidak cocok seperti pada outer join kanan.

Beberapa contoh

SELECT A.Colour, B.Colour DARI INNER JOIN B ON A.Colour = B.Colour

Di atas adalah equi klasik bergabung.

Inner Join

Versi Animasi

enter image description here

PILIH A.Colour, B.Colour DARI INNER JOIN B ON A.Colour NOT IN ('Green', 'Blue')

Kondisi gabung dalam tidak perlu menjadi kondisi kesetaraan dan tidak perlu kolom referensi dari kedua (atau bahkan salah satu) tabel. Mengevaluasi A.Colour NOT IN ('Green','Blue') pada setiap baris salib bergabung kembali.

inner 2

PILIH A.Colour, B.Colour DARI INNER JOIN B ON 1 = 1

Kondisi bergabung mengevaluasi true untuk semua baris di hasil gabungan silang jadi ini sama saja dengan gabungan silang. Saya tidak akan mengulang gambar 16 baris lagi.

PILIH A.Colour, B.Colour DARI LEFT OUTER JOIN B ON A.Colour = B.Colour

Pencocokan luar secara logis dievaluasi dengan cara yang sama seperti gabungan dalam kecuali jika deretan dari tabel kiri (untuk gabungan kiri) tidak bergabung dengan baris apa pun dari tabel sebelah kanan sama sekali diawetkan dalam hasil dengan NULL nilai untuk kolom tangan kanan.

LOJ

SELECT A.Colour, B.Colour DARI A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour IS NULL

Ini hanya membatasi hasil sebelumnya hanya mengembalikan baris di mana B.Colour IS NULL. Dalam kasus khusus ini adalah baris yang diawetkan karena tidak ada kecocokan di tabel sebelah kanan dan kueri mengembalikan baris merah tunggal yang tidak dicocokkan dalam tabel B. Ini dikenal sebagai anti semi bergabung.

Penting untuk memilih kolom untuk IS NULL tes yang tidak dapat dibatalkan atau yang mana syarat bergabungnya memastikan bahwa ada NULL nilai-nilai akan dikecualikan agar pola ini berfungsi dengan benar dan menghindari hanya membawa kembali baris yang kebetulan memiliki NULL nilai untuk kolom itu selain baris yang tidak dicocokkan.

loj is null

PILIH A.Colour, B.Colour FROM A RIGHT OUTER JOIN B ON A.Colour = B.Colour

Gabungan luar kanan bekerja sama dengan gabungan luar kiri kecuali mereka mempertahankan baris yang tidak cocok dari tabel kanan dan null memperpanjang kolom tangan kiri.

ROJ

PILIH A.Colour, B.Colour DARI A FULL OUTER JOIN B ON A.Colour = B.Colour

Gabungan luar yang lengkap menggabungkan perilaku gabungan kiri dan kanan dan mempertahankan baris yang tidak cocok dari tabel kiri dan kanan.

FOJ

PILIH A.Colour, B.Colour DARI A FULL OUTER JOIN B ON 1 = 0

Tidak ada baris di salib yang bergabung dengan 1=0 predikat. Semua baris dari kedua sisi diawetkan menggunakan aturan bergabung eksternal normal dengan NULL di kolom dari tabel di sisi lain.

FOJ 2

PILIH COALESCE (A.Colour, B.Colour) SEBAGAI WARNA DARI A FULL OUTER JOIN B ON 1 = 0

Dengan minor amand ke pertanyaan sebelumnya yang bisa mensimulasikan UNION ALL dari dua tabel.

UNION ALL

PILIH A.Colour, B.Colour DARI A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour = 'Green'

Perhatikan bahwa WHERE klausa (jika ada) secara logis berjalan setelah bergabung. Satu kesalahan umum adalah melakukan penggabungan luar kiri dan kemudian menyertakan klausa WHERE dengan kondisi di tabel kanan yang berakhir tidak termasuk baris yang tidak cocok. Di atas berakhir dengan melakukan penggabungan luar ...

LOJ

... Dan kemudian "Di mana" klausa berjalan. NULL= 'Green' tidak mengevaluasi benar sehingga baris yang diawetkan oleh gabungan luar berakhir dibuang (bersama dengan biru) secara efektif mengubah bergabung kembali ke yang dalam.

LOJtoInner 

Jika tujuannya adalah untuk hanya menyertakan baris dari B di mana Warna Hijau dan semua baris dari A terlepas dari sintaks yang benar

PILIH A.Colour, B.Colour DARI A LEFT OUTER JOIN B ON A.Colour = B.Colour DAN B.Colour = 'Green'

enter image description here

SQL Fiddle

Lihat contoh-contoh ini jalankan langsung di SQLFiddle.com.


526
2017-12-13 11:58



Berikut ini diambil dari artikel "MySQL - LEFT JOIN dan RIGHT JOIN, INNER JOIN dan OUTER JOIN"oleh Graham Ellis di blognya Horse's Mouth.

Dalam database seperti MySQL, data dibagi menjadi beberapa tabel yang kemudian dihubungkan (Joined) bersama oleh JOIN di SELECT perintah untuk membaca rekaman dari beberapa tabel. Baca contoh ini untuk melihat cara kerjanya.

Pertama, beberapa data sampel:

people
    mysql> select * from people;
    +------------+--------------+------+
    | name       | phone        | pid  |
    +------------+--------------+------+
    | Mr Brown   | 01225 708225 |    1 |
    | Miss Smith | 01225 899360 |    2 |
    | Mr Pullen  | 01380 724040 |    3 |
    +------------+--------------+------+
    3 rows in set (0.00 sec)

property
    mysql> select * from property;
    +------+------+----------------------+
    | pid  | spid | selling              |
    +------+------+----------------------+
    |    1 |    1 | Old House Farm       |
    |    3 |    2 | The Willows          |
    |    3 |    3 | Tall Trees           |
    |    3 |    4 | The Melksham Florist |
    |    4 |    5 | Dun Roamin           |
    +------+------+----------------------+
    5 rows in set (0.00 sec)

REGULER JOIN

Jika kita melakukan GABUNG reguler (dengan tidak satu pun dari kata kunci INNER, OUTER, LEFT atau RIGHT), maka kita mendapatkan semua catatan yang cocok dengan cara yang tepat dalam dua tabel, dan catatan di kedua tabel masuk yang tidak cocok tidak dilaporkan :

mysql> select name, phone, selling 
from people join property 
on people.pid = property.pid;
+-----------+--------------+----------------------+
| name      | phone        | selling              |
+-----------+--------------+----------------------+
| Mr Brown  | 01225 708225 | Old House Farm       |
| Mr Pullen | 01380 724040 | The Willows          |
| Mr Pullen | 01380 724040 | Tall Trees           |
| Mr Pullen | 01380 724040 | The Melksham Florist |
+-----------+--------------+----------------------+
4 rows in set (0.01 sec)

KIRI GABUNG

Jika kami melakukan KIRI KIRI, kami mendapatkan semua rekaman yang cocok dengan cara yang sama dan DI TAMBAHAN kami mendapatkan catatan tambahan untuk setiap data yang tidak cocok di tabel kiri dari gabungan - sehingga memastikan (dalam contoh ini) bahwa setiap PERSON mendapat penyebutan :

   mysql> select name, phone, selling 
    from people left join property 
    on people.pid = property.pid; 
    +------------+--------------+----------------------+
    | name       | phone        | selling              |
    +------------+--------------+----------------------+
    | Mr Brown   | 01225 708225 | Old House Farm       |
    | Miss Smith | 01225 899360 | NULL <<-- unmatch    |
    | Mr Pullen  | 01380 724040 | The Willows          |
    | Mr Pullen  | 01380 724040 | Tall Trees           |
    | Mr Pullen  | 01380 724040 | The Melksham Florist |
    +------------+--------------+----------------------+
    5 rows in set (0.00 sec)

KANAN GABUNG

Jika kita melakukan GABUNGAN KANAN, kita mendapatkan semua catatan yang cocok dan TAMBAHAN catatan tambahan untuk setiap data yang tidak cocok di tabel kanan bergabung - dalam contoh saya, itu berarti bahwa setiap properti mendapat penyebutan bahkan jika kita tidak memiliki rincian penjual:

mysql> select name, phone, selling 
from people right join property 
on people.pid = property.pid;
+-----------+--------------+----------------------+
| name      | phone        | selling              |
+-----------+--------------+----------------------+
| Mr Brown  | 01225 708225 | Old House Farm       |
| Mr Pullen | 01380 724040 | The Willows          |
| Mr Pullen | 01380 724040 | Tall Trees           |
| Mr Pullen | 01380 724040 | The Melksham Florist |
| NULL      | NULL         | Dun Roamin           |
+-----------+--------------+----------------------+
5 rows in set (0.00 sec)

INNER JOIN melakukan penggabungan penuh, seperti contoh pertama, dan kata OUTER dapat ditambahkan setelah kata KIRI atau KANAN dalam dua contoh terakhir - ini disediakan untuk kompatibilitas ODBC dan tidak menambah kemampuan ekstra.


291
2018-02-14 05:53



Gabung Dalam

Ambil hanya baris yang cocok, yaitu, A intersect B.

Enter image description here

SELECT *
FROM dbo.Students S
INNER JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Left Outer Join

Pilih semua rekaman dari tabel pertama, dan semua catatan di tabel kedua meja yang cocok dengan kunci yang tergabung.

Enter image description here

SELECT *
FROM dbo.Students S
LEFT JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Gabung Penuh Luar

Pilih semua rekaman dari tabel kedua, dan catatan apa pun di yang pertama meja yang cocok dengan kunci yang tergabung.

Enter image description here

SELECT *
FROM dbo.Students S
FULL JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Referensi


114
2018-01-27 12:16



Bergabung digunakan untuk menggabungkan data dari dua tabel, dengan hasil yang baru, tabel sementara. Bergabung dilakukan berdasarkan sesuatu yang disebut predikat, yang menentukan kondisi yang digunakan untuk melakukan gabung. Perbedaan antara penggabungan dalam dan penggabungan luar adalah bahwa gabungan dalam akan mengembalikan hanya baris yang benar-benar cocok berdasarkan predikat gabungan. Mari kita pertimbangkan tabel Karyawan dan Lokasi:

enter image description here

Batin Bergabung: - Inner join membuat tabel hasil baru dengan menggabungkan nilai kolom dari dua tabel (Karyawan dan Lokasi) berdasarkan predikat gabungan. Kueri membandingkan setiap baris Karyawan dengan setiap baris Lokasi untuk menemukan semua pasangan baris yang memenuhi predikat gabungan. Ketika gabungan predikat puas dengan mencocokkan nilai non-NULL, nilai kolom untuk setiap pasangan baris yang cocok Karyawan dan Lokasi digabungkan menjadi baris hasil. Inilah yang akan terlihat seperti SQL for inner join:

select  * from employee inner join location on employee.empID = location.empID
OR
select  * from employee, location where employee.empID = location.empID

Sekarang, inilah hasil dari menjalankan SQL yang akan terlihat seperti: enter image description here enter image description here

Bergabung Luar: - Gabungan luar tidak memerlukan setiap catatan dalam dua tabel yang tergabung untuk memiliki rekaman yang cocok. Tabel gabungan mempertahankan setiap rekaman — bahkan jika tidak ada catatan yang cocok lainnya. Outer bergabung membagi lebih lanjut ke kiri bergabung kiri dan gabungan luar kanan, tergantung pada baris tabel yang dipertahankan (kiri atau kanan).

Left Outer Bergabung: - Hasil dari gabungan luar kiri (atau hanya kiri bergabung) untuk tabel Karyawan dan Lokasi selalu berisi semua catatan dari tabel "kiri" (Karyawan), bahkan jika kondisi-bergabung tidak menemukan catatan yang cocok di tabel "benar" (Lokasi). Di sini adalah apa yang SQL untuk bergabung luar kiri akan terlihat seperti, menggunakan tabel di atas:

select  * from employee left outer join location on employee.empID = location.empID;
//Use of outer keyword is optional

Sekarang, inilah hasil dari menjalankan SQL ini akan terlihat seperti: enter image description here enter image description here

Penggabungan Luar Kanan: - Penggabungan luar kanan (atau penggabungan kanan) sangat mirip dengan gabungan luar kiri, kecuali dengan perlakuan terhadap tabel yang dibalik. Setiap baris dari tabel "kanan" (Lokasi) akan muncul di tabel gabungan setidaknya sekali. Jika tidak ada baris yang cocok dari tabel "kiri" (Karyawan) ada, NULL akan muncul di kolom dari Karyawan untuk catatan yang tidak cocok Lokasi. Seperti inilah tampilan SQL:

select * from employee right outer join location  on employee.empID = location.empID;
//Use of outer keyword is optional

Dengan menggunakan tabel di atas, kita dapat menunjukkan apa hasil dari gabungan luar yang tepat akan terlihat seperti:

enter image description hereenter image description here

Full Outer Bergabung: - Full Outer Join atau Full Join adalah untuk mempertahankan informasi yang tidak cocok dengan memasukkan baris yang tidak cocok dalam hasil gabungan, gunakan gabungan luar penuh. Ini mencakup semua baris dari kedua tabel, terlepas dari apakah tabel lain memiliki nilai yang cocok atau tidak. enter image description here

Sumber Gambar

Panduan Referensi 8,0 MySQL - Bergabung dengan Sintaksis


108
2017-12-18 06:54



Dengan kata sederhana:

Sebuah bergabung dalam ambil baris yang cocok saja.

Sedangkan sebuah bergabung luar mengambil baris yang cocok dari satu tabel dan semua baris di tabel lain .... hasilnya tergantung pada yang mana yang Anda gunakan:

  • Kiri: Baris yang cocok di tabel kanan dan semua baris di tabel kiri

  • Kanan: Baris yang cocok di tabel kiri dan semua baris di tabel kanan atau

  • Penuh: Semua baris di semua tabel. Tidak masalah jika ada kecocokan atau tidak


101
2018-01-12 11:07



Gabung dalam hanya menampilkan baris jika ada catatan yang cocok di sisi lain (kanan) dari gabungan.

Sambungan luar (kiri) menampilkan baris untuk setiap rekaman di sisi kiri, bahkan jika tidak ada baris yang cocok di sisi lain (kanan) dari gabungan. Jika tidak ada baris yang cocok, kolom untuk sisi (kanan) lainnya akan menunjukkan NULL.


92
2017-09-01 22:38



Inner join mensyaratkan bahwa catatan dengan ID terkait ada di tabel gabungan.

Gabungan luar akan mengembalikan catatan untuk sisi kiri bahkan jika tidak ada untuk sisi kanan.

Misalnya, Anda memiliki pesanan dan tabel OrderDetails. Mereka terkait dengan "OrderID".

Pesanan

  • Id pemesanan
  • Nama Pelanggan

Detail pesanan

  • OrderDetailID
  • Id pemesanan
  • Nama Produk
  • Qty
  • Harga

Permintaan

SELECT Orders.OrderID, Orders.CustomerName FROM Orders 
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

hanya akan mengembalikan Pesanan yang juga memiliki sesuatu di tabel OrdeDetail.

Jika Anda mengubahnya ke OUTER LEFT JOIN

SELECT Orders.OrderID, Orders.CustomerName FROM Orders 
LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

maka akan mengembalikan catatan dari tabel Pesanan bahkan jika mereka tidak memiliki catatan OrderDetails.

Anda dapat menggunakan ini untuk menemukan Pesanan yang tidak memiliki OrderDetails yang mengindikasikan kemungkinan pesanan yatim piatu dengan menambahkan klausa tempat seperti WHERE OrderDetails.OrderID IS NULL.


67
2017-09-01 22:47



Dengan kata sederhana:

Batin bergabung -> Ambil catatan umum HANYA dari tabel induk dan anak DI MANA kunci utama dari tabel Orang Tua cocok dengan kunci Asing di tabel Anak.

Kiri gabung ->

kode semu

1.Take All records from left Table
2.for(each record in right table,) {
    if(Records from left & right table matching on primary & foreign key){
       use their values as it is as result of join at the right side for 2nd table.
    } else {
       put value NULL values in that particular record as result of join at the right side for 2nd table.
    }
  }

Bergabunglah dengan benar : Persis kebalikan dari kiri bergabung. Masukkan nama tabel di LEFT JOIN di sisi kanan di kanan bergabung, Anda mendapatkan output yang sama dengan LEFT JOIN.

Bergabung di luar : Tampilkan semua rekaman di Kedua tabel No matter what. Jika catatan di tabel Kiri tidak cocok dengan tabel kanan berdasarkan Primer, tombol Forieign, gunakan nilai NULL sebagai hasil dari bergabung.

Contoh:

Example

Mari kita asumsikan sekarang untuk 2 tabel

1.employees , 2.phone_numbers_employees

employees : id , name 

phone_numbers_employees : id , phone_num , emp_id   

Di sini, tabel karyawan adalah tabel Master, phone_numbers_employees adalah tabel anak (berisi emp_id sebagai kunci asing yang menghubungkan employee.id jadi meja anaknya.)

Inner bergabung 

Ambil catatan 2 tabel HANYA JIKA Kunci utama tabel karyawan (idnya) cocok dengan kunci Asing dari meja Anak phone_numbers_employees (emp_id).

Jadi kueri akan menjadi:

SELECT e.id , e.name , p.phone_num FROM employees AS e INNER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Di sini hanya mengambil baris yang cocok pada kunci primer = foreign key seperti yang dijelaskan di atas. Berikut baris yang tidak cocok pada primary key = foreign key dilewatkan sebagai hasil dari join.

Kiri bergabung :

Left join mempertahankan semua baris dari tabel kiri, terlepas dari apakah ada baris yang cocok di meja sebelah kanan.

SELECT e.id , e.name , p.phone_num FROM employees AS e LEFT JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Bergabung luar :

SELECT e.id , e.name , p.phone_num FROM employees AS e OUTER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Secara diagram sepertinya:

Diagram


58
2018-02-19 04:50