Pertanyaan INNER BERGABUNG ON vs WHERE klausa


Untuk kesederhanaan, asumsikan semua bidang yang relevan NOT NULL.

Anda dapat melakukan:

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1, table2
WHERE
    table1.foreignkey = table2.primarykey
    AND (some other conditions)

Atau:

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1 INNER JOIN table2
    ON table1.foreignkey = table2.primarykey
WHERE
    (some other conditions)

Lakukan kedua cara ini dengan cara yang sama MySQL?


761
2018-06-19 16:16


asal


Jawaban:


INNER JOIN adalah sintaks ANSI yang harus Anda gunakan.

Hal ini umumnya dianggap lebih mudah dibaca, terutama ketika Anda bergabung dengan banyak tabel.

Ini juga dapat dengan mudah diganti dengan OUTER JOIN kapanpun dibutuhkan.

Itu WHERE sintaks lebih berorientasi pada model relasional.

Hasil dari dua tabel JOINed adalah produk kartesius dari tabel di mana filter diterapkan yang hanya memilih baris tersebut dengan menggabungkan kolom pencocokan.

Lebih mudah melihat ini dengan WHERE sintaksis.

Adapun contoh Anda, di MySQL (dan dalam SQL umumnya) dua pertanyaan ini adalah sinonim.

Perhatikan juga bahwa MySQL juga memiliki STRAIGHT_JOIN ayat.

Menggunakan klausul ini, Anda dapat mengontrol JOIN order: tabel mana yang dipindai di loop luar dan mana yang ada di loop dalam.

Anda tidak dapat mengendalikan ini di MySQL menggunakan WHERE sintaksis.


611
2018-06-19 16:17



Orang lain telah menunjukkan bahwa INNER JOIN membantu pembacaan manusia, dan itu adalah prioritas utama; Saya setuju. Biarkan saya mencoba menjelaskan Mengapa sintaks bergabung lebih mudah dibaca.

Kueri SELECT dasar adalah ini:

SELECT stuff
FROM tables
WHERE conditions

Klausa SELECT memberi tahu kita apa kami kembali; klausa DARI memberitahu kita dimana kita mendapatkannya dari, dan klausa WHERE memberitahu kita yang yang kita dapatkan.

GABUNG adalah pernyataan tentang tabel, bagaimana mereka terikat bersama (secara konseptual, sebenarnya, menjadi satu tabel). Elemen permintaan apa pun yang mengontrol tabel - tempat kami mendapatkan barang - semantik milik klausa FROM (dan tentu saja, di sanalah elemen GABUNG pergi). Puting bergabung-elemen ke dalam klausa WHERE mengonfigurasi yang dan darimana; itu sebabnya sintaks JOIN lebih disukai.


145
2018-06-19 16:30



 Menerapkan pernyataan kondisional di ON / WHERE

Di sini saya telah menjelaskan tentang langkah-langkah pemrosesan query logis.


Referensi: Di ​​dalam Microsoft SQL Server 2005 T-SQL Querying
Penerbit: Microsoft Press
Tanggal Pub: 07 Maret 2006
Cetak ISBN-10: 0-7356-2313-9
Cetak ISBN-13: 978-0-7356-2313-2
Halaman: 640

Di dalam Microsoft SQL Server 2005 T-SQL Querying

(8)  SELECT (9) DISTINCT (11) TOP <top_specification> <select_list>
(1)  FROM <left_table>
(3)       <join_type> JOIN <right_table>
(2)       ON <join_condition>
(4)  WHERE <where_condition>
(5)  GROUP BY <group_by_list>
(6)  WITH {CUBE | ROLLUP}
(7)  HAVING <having_condition>
(10) ORDER BY <order_by_list>

Aspek nyata pertama dari SQL yang berbeda dari bahasa pemrograman lainnya adalah urutan di mana kode diproses. Dalam sebagian besar bahasa pemrograman, kode diproses sesuai urutan penulisan. Dalam SQL, klausa pertama yang diproses adalah klausa FROM, sementara klausa SELECT, yang muncul pertama, diproses hampir terakhir.

Setiap langkah menghasilkan tabel virtual yang digunakan sebagai input ke langkah berikut. Tabel virtual ini tidak tersedia untuk pemanggil (aplikasi klien atau permintaan luar). Hanya tabel yang dihasilkan oleh langkah terakhir yang dikembalikan ke pemanggil. Jika klausa tertentu tidak ditentukan dalam kueri, langkah yang sesuai hanya dilewati.

Deskripsi Singkat Fase Pemrosesan Kueri Logis

Jangan terlalu khawatir jika deskripsi langkah-langkahnya tampaknya tidak masuk akal untuk saat ini. Ini disediakan sebagai referensi. Bagian yang muncul setelah contoh skenario akan mencakup langkah-langkah dalam lebih banyak detail.

  1. DARI: Produk Cartesian (cross join) dilakukan antara dua tabel pertama dalam klausa FROM, dan sebagai hasilnya, tabel virtual VT1 dihasilkan.

  2. AKTIF: Filter ON diterapkan ke VT1. Hanya baris untuk yang <join_condition> TRUE dimasukkan ke VT2.

  3. LUAR BIASA (bergabung): Jika GABUNG OUTER ditetapkan (sebagai lawan dari CROSS JOIN atau INNER JOIN), baris dari tabel atau tabel yang diawetkan untuk mana kecocokan tidak ditemukan ditambahkan ke baris dari VT2 sebagai baris luar, menghasilkan VT3. Jika lebih dari dua tabel muncul dalam klausa FROM, langkah 1 hingga 3 diterapkan berulang kali antara hasil penggabungan terakhir dan tabel berikutnya dalam klausa FROM hingga semua tabel diproses.

  4. DI MANA: Filter WHERE diterapkan ke VT3. Hanya baris untuk yang <where_condition> TRUE dimasukkan ke VT4.

  5. GRUP OLEH: Baris dari VT4 disusun dalam kelompok berdasarkan daftar kolom yang ditentukan dalam klausa GROUP BY. VT5 dihasilkan.

  6. CUBE | ROLLUP: Supergroup (kelompok kelompok) ditambahkan ke baris dari VT5, menghasilkan VT6.

  7. MEMILIKI: Filter HAVING diterapkan ke VT6. Hanya grup untuk yang <having_condition> TRUE dimasukkan ke VT7.

  8. PILIH: Daftar PILIH diproses, menghasilkan VT8.

  9. DISTINCT: Baris duplikat dihapus dari VT8. VT9 dihasilkan.

  10. ORDER BY: Baris-baris dari VT9 disortir menurut daftar kolom yang ditentukan dalam klausa ORDER BY. Kursor dihasilkan (VC10).

  11. ATAS: Jumlah atau persentase baris yang ditentukan dipilih dari awal VC10. Tabel VT11 dihasilkan dan dikembalikan ke pemanggil.



     Oleh karena itu, (INNER JOIN) ON akan memfilter data (jumlah data VT akan dikurangi di sini sendiri) sebelum menerapkan klausa WHERE. Kondisi penggabungan selanjutnya akan dijalankan dengan data yang difilter yang meningkatkan kinerja. Setelah itu hanya kondisi WHERE yang akan menerapkan kondisi filter.

(Menerapkan pernyataan kondisional dalam ON / WHERE tidak akan membuat banyak perbedaan dalam beberapa kasus. Hal ini tergantung berapa banyak tabel yang telah Anda gabungkan dan jumlah baris yang tersedia di setiap tabel gabungan)


112
2017-12-22 06:24



The implicit bergabung dengan sintaks ANSI lebih tua, kurang jelas dan tidak direkomendasikan.

Selain itu, aljabar relasional memungkinkan pertukaran dari predikat di WHERE klausa dan INNER JOIN, begitu saja INNER JOIN kueri dengan WHERE klausa dapat memiliki predikat yang diatur ulang oleh pengoptimal.

Saya sarankan Anda menulis pertanyaan dengan cara yang paling mudah dibaca mungkin.

Terkadang ini termasuk membuat INNER JOIN relatif "tidak lengkap" dan menempatkan beberapa kriteria dalam WHERE hanya untuk membuat daftar kriteria penyaringan lebih mudah dikelola.

Misalnya, alih-alih:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
    AND c.State = 'NY'
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
    AND a.Status = 1

Menulis:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
WHERE c.State = 'NY'
    AND a.Status = 1

Tetapi itu tergantung, tentu saja.


55
2018-06-19 16:23



Bergabung implisit (yang merupakan permintaan pertama Anda dikenal) menjadi jauh lebih membingungkan, sulit dibaca, dan sulit dikelola setelah Anda perlu mulai menambahkan lebih banyak tabel ke kueri Anda. Bayangkan melakukan permintaan dan jenis gabung yang sama di empat atau lima tabel yang berbeda ... itu mimpi buruk.

Menggunakan gabungan eksplisit (contoh kedua Anda) jauh lebih mudah dibaca dan mudah dipelihara.


25
2018-06-19 16:19



Saya juga akan menunjukkan bahwa menggunakan sintaks yang lebih tua lebih tunduk pada kesalahan. Jika Anda menggunakan gabungan dalam tanpa klausa ON, Anda akan mendapatkan kesalahan sintaks. Jika Anda menggunakan sintaks yang lebih tua dan melupakan salah satu ketentuan gabung di mana klausa, Anda akan mendapatkan gabungan silang. Para pengembang sering memperbaikinya dengan menambahkan kata kunci yang berbeda (daripada memperbaiki bergabung karena mereka masih tidak menyadari bergabung itu sendiri rusak) yang mungkin muncul untuk menyembuhkan masalah, tetapi akan memperlambat permintaan jauh.

Selain itu untuk pemeliharaan jika Anda memiliki gabungan silang dalam sintaks yang lama, bagaimana pengelola mengetahui jika Anda bermaksud memilikinya (ada situasi di mana diperlukan gabungan silang) atau apakah itu kecelakaan yang harus diperbaiki?

Biarkan saya mengarahkan Anda ke pertanyaan ini untuk melihat mengapa sintaks implisit buruk jika Anda menggunakan gabungan kiri. Sybase * = ke Ansi Standard dengan 2 tabel luar yang berbeda untuk tabel dalam yang sama

Plus (kata-kata kasar pribadi di sini), standar menggunakan gabungan eksplisit adalah lebih dari 20 tahun, yang berarti implit bergabung sintaks telah usang selama 20 tahun. Apakah Anda akan menulis kode aplikasi menggunakan sintaks yang sudah usang selama 20 tahun? Mengapa Anda ingin menulis kode basis data itu?


21
2018-06-19 16:46



Mereka memiliki makna yang berbeda yang dapat dibaca manusia.

Namun, tergantung pada pengoptimal kueri, mereka mungkin memiliki arti yang sama ke mesin.

Anda harus selalu membuat kode agar dapat dibaca.

Artinya, jika ini adalah hubungan bawaan, gunakan gabungan eksplisit. jika Anda cocok dengan data yang terkait lemah, gunakan klausa di mana.


12
2018-06-19 16:20



SQL: 2003 standar mengubah beberapa aturan diutamakan sehingga pernyataan JOIN diutamakan melalui "koma" bergabung. Ini benar-benar dapat mengubah hasil kueri Anda bergantung pada bagaimana pengaturannya. Ini menyebabkan beberapa masalah bagi sebagian orang ketika MySQL 5.0.12 beralih ke mengikuti standar.

Jadi dalam contoh Anda, pertanyaan Anda akan bekerja sama. Tetapi jika Anda menambahkan tabel ketiga: SELECT ... FROM table1, table2 JOIN table3 ON ... DI MANA ...

Sebelum MySQL 5.0.12, table1 dan table2 akan bergabung terlebih dahulu, lalu table3. Sekarang (5.0.12 dan on), table2 dan table3 bergabung terlebih dahulu, lalu table1. Itu tidak selalu mengubah hasilnya, tetapi bisa dan Anda mungkin bahkan tidak menyadarinya.

Saya tidak pernah menggunakan sintaks "koma" lagi, memilih contoh kedua Anda. Ini jauh lebih mudah dibaca, kondisi GABUNG dengan GABUNG, tidak dipisahkan ke dalam bagian permintaan terpisah.


10
2018-06-19 17:28



Saya tahu Anda berbicara tentang MySQL, tapi bagaimanapun juga: Dalam Oracle 9 bergabung eksplisit dan bergabung implisit akan menghasilkan rencana eksekusi yang berbeda. AFAIK yang telah dipecahkan di Oracle 10+: tidak ada perbedaan seperti itu lagi.


4
2018-06-19 17:03



ANSI bergabung dengan sintaks pasti lebih portabel.

Saya akan melalui peningkatan Microsoft SQL Server, dan saya juga akan menyebutkan bahwa sintaks = * dan * = untuk gabungan luar di SQL Server tidak didukung (tanpa mode kompatibilitas) untuk server sql 2005 dan kemudian.


1
2018-06-19 16:50