SQL BERGABUNG dan berbeda jenis Bergabung

Equi-Join: Menggabungkan umum data dari dua tabel yang didasarkan pada kesetaraan kondisi. Secara teknis, Bergabung dibuat dengan menggunakan persamaan-operator (=) untuk membandingkan nilai-nilai dari Primary Key dari suatu tabel dan Foreign Key nilai-nilai dari tabel lain, maka hasil yang ditetapkan mencakup umum(cocok) catatan dari kedua tabel. Untuk implementasi melihat INNER JOIN.
Alami-Bergabung: Ini adalah versi yang disempurnakan dari Equi-Join, di mana PILIH operasi menghilangkan kolom duplikat. Untuk implementasi melihat DALAM BERGABUNG
Non-Equi-Join: Ini adalah kebalikan dari Equi-join di mana kondisi penggabungan adalah kegunaan lain dari operator yang sama(=) e.g, !=, <=, >=, >, < atau ANTARA dll. Untuk implementasi melihat INNER JOIN.
Self-Join:: disesuaikan perilaku bergabung mana meja dikombinasikan dengan dirinya sendiri; hal Ini biasanya diperlukan untuk query self-referensi tabel (atau Unary relationship entity). Untuk implementasi melihat DALAM Bergabung.
Produk Cartesian: Itu cross menggabungkan semua record dari kedua tabel tanpa kondisi apapun. Secara teknis, ia mengembalikan set hasil query tanpa WHERE Clause.

Sebagai per SQL perhatian dan kemajuan, ada 3 jenis bergabung dan semua RDBMS bergabung dapat dicapai dengan menggunakan jenis bergabung.

INNER JOIN: menyatu(atau menggabungkan) sesuai baris dari dua tabel. Pencocokan ini dilakukan berdasarkan kolom tabel dan membandingkan operasi. Jika kesetaraan berdasarkan kondisi lalu: EQUI-JOIN dilakukan, jika Non-EQUI-Join.
OUTER-JOIN: menyatu(atau menggabungkan) sesuai baris dari dua tabel dan tak tertandingi baris dengan nilai-nilai NULL. Namun, dapat disesuaikan pilihan un-cocok baris e.g, memilih tak tertandingi baris dari tabel pertama atau kedua tabel dengan sub-tipe: LEFT OUTER JOIN dan RIGHT OUTER JOIN.

2.1. LEFT Outer JOIN (a.k.a, KIRI-JOIN): Kembali sesuai baris dari dua tabel dan tak tertandingi dari tabel KIRI(saya.e, tabel pertama) saja.

2.2. RIGHT Outer JOIN (a.k.a, KAN-JOIN): Kembali sesuai baris dari dua tabel dan tak tertandingi dari tabel KANAN saja.

2.3. FULL OUTER JOIN (a.k.sebuah OUTER JOIN): Mengembalikan cocok dan tak tertandingi dari kedua tabel.

CROSS-BERGABUNG: Ini bergabung tidak menggabungkan/menggabungkan bukannya melakukan produk Cartesian.

![enter image description here][1] Catatan: Self-JOIN dapat dicapai dengan baik INNER JOIN, OUTER JOIN dan CROSS JOIN berdasarkan kebutuhan tapi meja harus bergabung dengan dirinya sendiri.

[Untuk informasi lebih lanjut:][2]

Contoh:

1.1: INNER JOIN: Equi-join pelaksanaan

SELECT  *
FROM Table1 A 
 INNER JOIN Table2 B ON A. =B.;

1.2: INNER JOIN: Alam-BERGABUNG pelaksanaan

Select A.*, B.Col1, B.Col2          --But no B.ForeignKeyColumn in Select
 FROM Table1 A
 INNER JOIN Table2 B On A.Pk = B.Fk;

1.3: BATIN-BERGABUNG dengan NON-Equi-join pelaksanaan


Select *
 FROM Table1 A INNER JOIN Table2 B On A.Pk

dev ツ

Jawaban edit 9 Agustus 2019 в 11:55

66

0

Komentar (2)

Lukas Eder

Sumber Tidak AMP-versi Sunting

Menariknya sebagian besar jawaban yang lain menderita dua masalah ini:

Mereka fokus pada bentuk dasar dari bergabung hanya
Mereka (ab)menggunakan diagram Venn, yang tidak akurat alat untuk memvisualisasikan bergabung (mereka're jauh lebih baik untuk buruh). I've baru-baru ini menulis sebuah artikel pada topik: A Mungkin tidak Lengkap, Panduan Komprehensif dengan Banyak Cara yang Berbeda untuk BERGABUNG Tabel di SQL, yang saya'll meringkas di sini.
Pertama dan terpenting: Bergabung adalah produk cartesian

Ini adalah mengapa diagram Venn menjelaskan mereka jadi tidak akurat, karena BERGABUNG dengan menciptakan produk cartesian antara dua bergabung tabel. Wikipedia menggambarkan hal ini dengan baik: SQL sintaks untuk produk cartesian adalah CROSS JOIN. Misalnya:

SELECT *

-- This just generates all the days in January 2017
FROM generate_series(
  '2017-01-01'::TIMESTAMP,
  '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
  INTERVAL '1 day'
) AS days(day)

-- Here, we're combining all days with all departments
CROSS JOIN departments

Yang menggabungkan semua baris dari satu tabel dengan semua baris dari tabel lain: Sumber:

+--------+   +------------+
| day    |   | department |
+--------+   +------------+
| Jan 01 |   | Dept 1     |
| Jan 02 |   | Dept 2     |
| ...    |   | Dept 3     |
| Jan 30 |   +------------+
| Jan 31 |
+--------+

Hasilnya:

+--------+------------+
| day    | department |
+--------+------------+
| Jan 01 | Dept 1     |
| Jan 01 | Dept 2     |
| Jan 01 | Dept 3     |
| Jan 02 | Dept 1     |
| Jan 02 | Dept 2     |
| Jan 02 | Dept 3     |
| ...    | ...        |
| Jan 31 | Dept 1     |
| Jan 31 | Dept 2     |
| Jan 31 | Dept 3     |
+--------+------------+

Jika kita hanya menulis daftar dipisahkan koma dari tabel, kita'll mendapatkan hasil yang sama:

-- CROSS JOINing two tables:
SELECT * FROM table1, table2

INNER JOIN (Theta-BERGABUNG)

INNER JOIN hanya disaring CROSS JOIN di mana filter predikat disebut Theta dalam aljabar relasional. Misalnya:

SELECT *

-- Same as before
FROM generate_series(
  '2017-01-01'::TIMESTAMP,
  '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
  INTERVAL '1 day'
) AS days(day)

-- Now, exclude all days/departments combinations for
-- days before the department was created
JOIN departments AS d ON day >= d.created_at

Perhatikan bahwa kata kunci INNER adalah opsional (kecuali di MS Access). (lihat artikel untuk contoh hasil)

EQUI JOIN

Jenis khusus dari Theta-BERGABUNG adalah equi JOIN, yang kita gunakan paling. Predikat bergabung dengan primary key dari tabel satu dengan tabel foreign key dari tabel yang lain. Jika kita menggunakan Sakila database sebagai ilustrasi, kita dapat menulis:

SELECT *
FROM actor AS a
JOIN film_actor AS fa ON a.actor_id = fa.actor_id
JOIN film AS f ON f.film_id = fa.film_id

Ini menggabungkan semua aktor dengan film-film mereka. Atau juga, pada beberapa database:

SELECT *
FROM actor
JOIN film_actor USING (actor_id)
JOIN film USING (film_id)

MENGGUNAKAN() sintaks yang memungkinkan untuk menentukan kolom yang harus ada pada kedua sisi JOIN operation's tabel dan menciptakan kesetaraan predikat pada dua kolom.

NATURAL JOIN

Jawaban yang lain telah terdaftar ini "BERGABUNG type" secara terpisah, tapi itu doesn't make sense. It's hanya sintaks gula formulir untuk equi JOIN, yang merupakan kasus khusus dari Theta-JOIN atau INNER JOIN. NATURAL JOIN hanya mengumpulkan semua kolom yang umum untuk kedua tabel yang sedang bergabung dan bergabung dengan MENGGUNAKAN() kolom tersebut. Yang hampir tidak pernah berguna, karena kebetulan cocok (seperti LAST_UPDATE kolom di Sakila database). Berikut ini's sintaks:

SELECT *
FROM actor
NATURAL JOIN film_actor
NATURAL JOIN film

OUTER JOIN

Sekarang, OUTER JOIN sedikit berbeda dari INNER JOIN karena menciptakan UNION dari beberapa produk cartesian. Kita dapat menulis:

-- Convenient syntax:
SELECT *
FROM a LEFT JOIN b ON 

-- Cumbersome, equivalent syntax:
SELECT a.*, b.*
FROM a JOIN b ON 
UNION ALL
SELECT a.*, NULL, NULL, ..., NULL
FROM a
WHERE NOT EXISTS (
  SELECT * FROM b WHERE 
)

Tidak ada yang ingin menulis yang terakhir, jadi kami menulis OUTER JOIN (yang biasanya lebih dioptimalkan dengan database). Seperti BATIN, kata kunci LUAR adalah opsional, di sini. OUTER JOIN datang dalam tiga rasa:

KIRI [ OUTER ] JOIN: meja sebelah kiri dari BERGABUNG ekspresi ditambahkan ke uni seperti yang ditunjukkan di atas.
HAK [ OUTER ] JOIN: tabel kanan dariBERGABUNG` ekspresi ditambahkan ke uni seperti yang ditunjukkan di atas.
PENUH [ OUTER ] JOIN: Kedua tabel JOIN ekspresi yang ditambahkan ke uni seperti yang ditunjukkan di atas. Semua ini dapat dikombinasikan dengan kata kunci MENGGUNAKAN() atau dengan ALAMI (I've benar-benar memiliki dunia nyata-kasus penggunaan untuk ALAM PENUH BERGABUNG baru-baru ini)
Alternatif sintaks

Ada beberapa bersejarah, usang sintaks di Oracle dan SQL Server, yang didukung OUTER JOIN sudah sebelum SQL standar memiliki sintaks ini:

-- Oracle
SELECT *
FROM actor a, film_actor fa, film f
WHERE a.actor_id = fa.actor_id(+)
AND fa.film_id = f.film_id(+)

-- SQL Server
SELECT *
FROM actor a, film_actor fa, film f
WHERE a.actor_id *= fa.actor_id
AND fa.film_id *= f.film_id

Setelah berkata begitu, don't menggunakan sintaks ini. Saya baru saja daftar ini di sini sehingga anda dapat mengenalinya dari posting blog lama / lawas kode.

Dipartisi `OUTER JOIN`

Sedikit orang yang tahu ini, tapi SQL standar menentukan dipartisi OUTER JOIN (dan Oracle menerapkan hal ini). Anda dapat menulis hal-hal seperti ini:

WITH

  -- Using CONNECT BY to generate all dates in January
  days(day) AS (
    SELECT DATE '2017-01-01' + LEVEL - 1
    FROM dual
    CONNECT BY LEVEL = created_at

Bagian dari hasil:

+--------+------------+------------+
| day    | department | created_at |
+--------+------------+------------+
| Jan 01 | Dept 1     |            | -- Didn't match, but still get row
| Jan 02 | Dept 1     |            | -- Didn't match, but still get row
| ...    | Dept 1     |            | -- Didn't match, but still get row
| Jan 09 | Dept 1     |            | -- Didn't match, but still get row
| Jan 10 | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 11 | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 12 | Dept 1     | Jan 10     | -- Matches, so get join result
| ...    | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 31 | Dept 1     | Jan 10     | -- Matches, so get join result

Intinya di sini adalah bahwa semua baris dari dipartisi sisi bergabung akan berakhir dalam hasil terlepas jika BERGABUNG cocok apa-apa pada "di sisi lain BERGABUNG". Singkat cerita: Ini adalah untuk mengisi jarang data dalam laporan. Sangat berguna!

SEMI BERGABUNG

Serius? Tidak ada jawaban lain punya ini? Tentu saja tidak, karena itu doesn't memiliki native sintaks SQL, sayangnya (seperti ANTI BERGABUNG di bawah). Tapi kita bisa menggunakan IN() dan ADA(), misalnya, untuk menemukan semua aktor yang telah bermain dalam film:

SELECT *
FROM actor a
WHERE EXISTS (
  SELECT * FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Yang MANA.actor_id = fa.actor_id predikat bertindak sebagai semi bergabung predikat. Jika anda don't percaya, memeriksa rencana eksekusi, misalnya di Oracle. Anda'll melihat bahwa database mengeksekusi SEMI BERGABUNG dengan operasi, bukan ADA() predikat.

ANTI BERGABUNG

Ini adalah hanya kebalikan dari SEMI BERGABUNG (berhati-hati untuk tidak menggunakan TIDAK meskipun, karena memiliki sebuah peringatan penting) Berikut ini adalah semua pelaku tanpa film:

SELECT *
FROM actor a
WHERE NOT EXISTS (
  SELECT * FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Beberapa orang (terutama orang MySQL) juga menulis ANTI BERGABUNG seperti ini:

SELECT *
FROM actor a
LEFT JOIN film_actor fa
USING (actor_id)
WHERE film_id IS NULL

Saya pikir bersejarah alasan kinerja.

LATERAL BERGABUNG

OMG, yang satu ini juga keren. I'm hanya satu lagi itu? Berikut ini's keren query:

SELECT a.first_name, a.last_name, f.*
FROM actor AS a
LEFT OUTER JOIN LATERAL (
  SELECT f.title, SUM(amount) AS revenue
  FROM film AS f
  JOIN film_actor AS fa USING (film_id)
  JOIN inventory AS i USING (film_id)
  JOIN rental AS r USING (inventory_id)
  JOIN payment AS p USING (rental_id)
  WHERE fa.actor_id = a.actor_id -- JOIN predicate with the outer query!
  GROUP BY f.film_id
  ORDER BY revenue DESC
  LIMIT 5
) AS f
ON true

Itu akan menemukan TOP 5 pendapatan memproduksi film per aktor. Setiap kali anda perlu TOP-N-per-sesuatu query, LATERAL BERGABUNG akan menjadi teman anda. Jika anda're SQL Server orang, maka anda tahu ini BERGABUNG jenis di bawah nama APPLY

SELECT a.first_name, a.last_name, f.*
FROM actor AS a
OUTER APPLY (
  SELECT f.title, SUM(amount) AS revenue
  FROM film AS f
  JOIN film_actor AS fa ON f.film_id = fa.film_id
  JOIN inventory AS i ON f.film_id = i.film_id
  JOIN rental AS r ON i.inventory_id = r.inventory_id
  JOIN payment AS p ON r.rental_id = p.rental_id
  WHERE fa.actor_id = a.actor_id -- JOIN predicate with the outer query!
  GROUP BY f.film_id
  ORDER BY revenue DESC
  LIMIT 5
) AS f

OK, mungkin itu's kecurangan, karena LATERAL BERGABUNG atau TERAPKAN ekspresi adalah benar-benar "correlated subquery" yang menghasilkan beberapa baris. Tapi jika kita memungkinkan untuk "berkorelasi subqueries", kita juga bisa berbicara tentang...

MULTISET

Ini hanya benar-benar dilaksanakan oleh Oracle dan Informix (untuk pengetahuan saya), tetapi hal ini dapat ditiru di PostgreSQL menggunakan array dan/atau XML dan SQL Server menggunakan XML. MULTISET menghasilkan korelasi subquery dan sarang yang dihasilkan set baris di outer query. Di bawah ini query memilih semua aktor dan untuk masing-masing aktor mengumpulkan film-film mereka di bersarang koleksi:

SELECT a.*, MULTISET (
  SELECT f.*
  FROM film AS f
  JOIN film_actor AS fa USING (film_id)
  WHERE a.actor_id = fa.actor_id
) AS films
FROM actor

Seperti yang anda lihat, ada banyak jenis BERGABUNG dari sekedar "membosankan" BATIN, LUAR, dan CROSS JOIN yang biasanya disebutkan. Lebih detail di artikel saya. Dan tolong, berhenti menggunakan diagram Venn untuk menggambarkan mereka.

38

0

Komentar (2)

Gisway

Sumber Tidak AMP-versi Sunting

Saya telah menciptakan sebuah ilustrasi yang menjelaskan lebih baik daripada kata-kata, menurut saya:

TylerH

Jawaban edit 7 Maret 2018 в 7:27

9

0

Komentar (2)

peufeu

Sumber Tidak AMP-versi Sunting

I'm akan mendorong hewan peliharaan mengesalkan saya: dengan MENGGUNAKAN kata kunci.

Jika kedua tabel di kedua sisi BERGABUNG dengan mereka foreign key benar bernama (yaitu, nama yang sama, tidak hanya "id) maka hal ini dapat digunakan:

SELECT ...
FROM customers JOIN orders USING (customer_id)

Saya menemukan ini sangat praktis, dapat dibaca, dan tidak digunakan cukup sering.

TylerH

Jawaban edit 7 Maret 2018 в 7:27

-3

0

Komentar (1)

M-D · Accepted Answer · 2013-07-30T11:48:12+00:00

Apa `SQL JOIN` ?

SQL JOIN adalah metode untuk mengambil data dari dua atau lebih tabel database.

Apa yang berbeda `SQL BERGABUNG ?

Ada total lima `BERGABUNG. Mereka adalah :

  1. JOIN or INNER JOIN
  2. OUTER JOIN

     2.1 LEFT OUTER JOIN or LEFT JOIN
     2.2 RIGHT OUTER JOIN or RIGHT JOIN
     2.3 FULL OUTER JOIN or FULL JOIN

  3. NATURAL JOIN
  4. CROSS JOIN
  5. SELF JOIN

JOIN atau INNER JOIN :

Ini semacam BERGABUNG, kita mendapatkan semua catatan yang sesuai dengan kondisi di kedua tabel, dan catatan di kedua tabel yang tidak cocok yang tidak dilaporkan.

Dengan kata lain, INNER JOIN didasarkan pada satu fakta bahwa: HANYA entri yang cocok di KEDUA tabel HARUS dicantumkan.

Perhatikan bahwa BERGABUNG tanpa BERGABUNG kata kunci (seperti BATIN, LUAR, KIRI, dll) adalah sebuah INNER JOIN. Dengan kata lain, BERGABUNG adalah Sintaksis gula untuk INNER JOIN (lihat di: https://stackoverflow.com/questions/565620/difference-between-join-and-inner-join).

OUTER JOIN :

OUTER JOIN mengambil

Baik, cocok baris dari satu tabel dan semua baris dalam tabel lainnya Atau, semua baris dalam semua tabel (itu doesn't peduli apakah ada atau tidak ada pertandingan).

Ada tiga jenis Outer Join :

2.1 LEFT OUTER JOIN atau LEFT JOIN

Ini join mengembalikan semua baris dari tabel kiri dalam hubungannya dengan baris yang cocok dari kanan meja. Jika tidak ada kolom yang sesuai di tabel kanan, itu kembali NULL nilai-nilai.

2.2 RIGHT OUTER JOIN atau RIGHT JOIN

Ini BERGABUNG mengembalikan semua baris dari tabel yang tepat dalam hubungannya dengan baris yang cocok dari kiri meja. Jika tidak ada kolom yang sesuai di sebelah kiri meja, itu kembali NULL nilai-nilai.

2.3 FULL OUTER JOIN atau PENUH BERGABUNG

Ini BERGABUNG menggabungkan LEFT OUTER JOIN dan RIGHT OUTER JOIN. Kembali baris-baris dari tabel ketika kondisi terpenuhi dan mengembalikan nilai "NULL" ketika tidak ada pertandingan.

Dengan kata lain, OUTER JOIN ini didasarkan pada kenyataan bahwa: HANYA entri yang cocok di salah SATU meja (KANAN atau KIRI) atau KEDUA tabel(FULL) HARUS tercantum.

Note that `OUTER JOIN` is a loosened form of `INNER JOIN`.

NATURAL JOIN :

Hal ini didasarkan pada dua kondisi :

JOIN dilakukan pada semua kolom dengan nama yang sama untuk kesetaraan.
Menghilangkan duplikat dari kolom hasil.

Hal ini tampaknya menjadi lebih teoritis di alam dan sebagai hasilnya (mungkin) paling DBMS don't bahkan repot-repot untuk mendukung ini.

CROSS JOIN :

Ini adalah produk Cartesian dari dua tabel yang terlibat. Hasil dari CROSS JOIN tidak akan masuk akal dalam sebagian besar situasi. Selain itu, kita tidak't perlu ini di semua (atau setidaknya, harus tepat).

DIRI BERGABUNG :

Hal ini tidak berbeda bentuk JOIN, melainkan merupakan BERGABUNG (BATIN, LUAR, dll) dari sebuah tabel untuk dirinya sendiri.

Bergabung berdasarkan Operator

Tergantung pada operator yang digunakan untuk BERGABUNG klausa, bisa ada dua jenis `BERGABUNG. Mereka

Equi JOIN
Theta JOIN
Equi JOIN :

Untuk apa BERGABUNG jenis (BATIN, LUAR, dll), jika kita HANYA menggunakan kesetaraan operator (=), maka kita mengatakan bahwa JOIN adalah sebuah EQUI JOIN.

Theta JOIN :

Ini adalah sama seperti EQUI JOIN tapi hal ini memungkinkan semua operator lain seperti >, <, >= dll.

Banyak yang menganggap keduanya EQUI JOIN dan Theta BERGABUNG mirip dengan BATIN, LUAR dll BERGABUNG. Tapi saya sangat percaya bahwa kesalahan dan membuat ide-ide yang samar-samar. KarenaINNER JOIN,OUTER JOINdll semua yang berhubungan dengan tabel dan data mereka sedangkanEQUI JOINdanTHETA JOIN` hanya terhubung dengan operator yang kita gunakan dalam bekas.

sekali Lagi, ada banyak yang menganggap ALAM BERGABUNG sebagai semacam "aneh" EQUI JOIN. Pada kenyataannya, itu adalah benar, karena yang pertama kondisi yang saya sebutkan untuk NATURAL JOIN. Namun, kita don't harus membatasi bahwa hanya untuk BERGABUNG ALAM itu sendiri.INNER JOIN itu, OUTER JOIN ini dll bisa menjadiEQUI JOIN` juga.

SQL BERGABUNG dan berbeda jenis Bergabung

Apa SQL JOIN ?

Apa yang berbeda `SQL BERGABUNG ?

JOIN atau INNER JOIN :

OUTER JOIN :

NATURAL JOIN :

CROSS JOIN :

DIRI BERGABUNG :

Bergabung berdasarkan Operator

Equi JOIN :

Theta JOIN :

Jenis BERGABUNG:

Contoh:

Pertama dan terpenting: Bergabung adalah produk cartesian

INNER JOIN (Theta-BERGABUNG)

EQUI JOIN

NATURAL JOIN

OUTER JOIN

Alternatif sintaks

Dipartisi OUTER JOIN

SEMI BERGABUNG

ANTI BERGABUNG

LATERAL BERGABUNG

MULTISET

Apa `SQL JOIN` ?

Dipartisi `OUTER JOIN`