Deskripsi penggunaan gabungan dalam Microsoft query

Apa itu gabungan?

Gabungan adalah koneksi di antara dua tabel di mana dua tabel digabungkan sesuai dengan bidang yang sama, membuat tabel virtual baru (yang bisa disimpan sebagai tabel nyata). Misalnya, dengan dua tabel berikut ini: Color_Table: Join_Field Color_Field 1 Red 2 Blue 3 Green Pattern_Table: Join_Field Pattern_Field 2 Striped 3 Checkered 4 Polka-Dot gabungan sederhana menyerupai yang berikut ini: Join_Field Color_Field Pattern_Field 2 Blue Striped 3 Green Checkered Tabel hasil hanya berisi catatan 2 dan 3 dalam bidang Join_Field karena merupakan satu-satunya rekaman yang ada di Color_Table dan Pattern_Table. Contoh praktis gabungan adalah daftar produk dan produsen peritel; Tabel produk dan tabel vendor dapat digabungkan pada bidang ID Produk.

Gabungan dalam

Gabungan dalam contoh sebelumnya, yang disebut Inner Join, adalah tipe penggabungan yang paling sederhana. Biasanya, Anda hanya ingin menggunakan sebagian dari bidang dalam tabel. Misalnya, dalam gabungan yang nyata, seperti di atas, Anda mungkin ingin mengecualikan Join_Field, sehingga gabungan terlihat seperti ini: Color_Field Pattern_Field Blue Striped Green Checkered Dalam Microsoft query, Inner Join adalah tipe gabungan default (untuk informasi selengkapnya, lihat halaman 105 dalam "panduan pengguna Microsoft query," versi 1,0).

Pernyataan SQL

Pernyataan SELECT Structured Query Language (SQL) adalah tipe makro yang bisa Anda gunakan saat Anda membuat gabungan. Perhatikan bahwa SQL sangat berbeda dari bahasa makro lainnya dari Microsoft Excel (Visual Basic for Applications, dan bahasa makro Excel 4,0). TIDAK perlu memahami SQL agar mudah membuat gabungan dalam Microsoft query. Setiap gabungan memiliki pernyataan SELECT yang terkait dengannya. Anda dapat menampilkan pernyataan SELECT untuk gabungan apa pun di Microsoft query dengan mengklik "SQL" di toolbar. Seperti perekam makro Microsoft Excel, Anda bisa menggunakan kueri untuk merekam pernyataan SELECT. Tidak seperti perekam makro Microsoft Excel, perekam pernyataan SELECT selalu aktif dan tidak dapat dinonaktifkan. Berikut cara pernyataan SELECT mungkin terlihat di Microsoft query untuk gabungan dalam di atas: SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM `c:\database`.Color_Table, `c:\database`.Pattern_Table WHERE Color_Table.Join_Field = Pattern_Table.Join_Field Perhatikan bahwa kami menggunakan nama database ' C:\database ' yang merupakan ekstensi nama file untuk database Microsoft Access, yang bisa memiliki beberapa tabel dalam satu file. Dalam beberapa database lain, seperti dBASE, Paradox, dan FoxPro, setiap tabel harus memiliki file sendiri. Dalam kasus ini, sintaks SQL mungkin tampak berlebihan karena nama tabel selalu sama dengan nama file tanpa ekstensi. Sintaks SQL bervariasi antara kueri mesin; Misalnya, di Microsoft Access, kueri dari contoh di atas menyerupai yang berikut ini: SELECT Color_Table.[Color_Field], Pattern_Table.Pattern_Field FROM Pattern_Table INNER JOIN Color_Table ON Pattern_Table.[Join_Field] = Color_Table.[Join_Field]; Jalur ke tabel tidak digunakan dalam Microsoft Access karena tabel berada dalam file. MDB Microsoft Access. Meskipun tabel eksternal dilampirkan dan digunakan dalam kueri, pernyataan Microsoft Access SQL tidak menampilkan jalur ke tabel eksternal.

Gabungan luar

Tipe gabungan lainnya disebut gabungan luar. Dengan gabungan luar, Anda mendapatkan semua catatan dari satu tabel dan hanya catatan dari tabel lain yang memiliki nilai yang cocok dari tabel pertama. Ini mungkin membiarkan beberapa entri bidang kosong, atau "null." Untuk setiap dua tabel yang akan digabungkan, ada dua kemungkinan gabungan luar, "gabungan luar kiri" dan "gabungan luar kanan" (dinamakan demikian karena Anda biasanya melihat tabel secara berdampingan). Menggunakan dua tabel sebelumnya dalam contoh, berikut ini adalah salah satu dari dua gabungan luar yang memungkinkan: Join_Field Color_Field Pattern_Field 1 Red (NULL) 2 Blue Striped 3 Green Checkered Gabungan lain yang memungkinkan adalah sebagai berikut: Join_Field Color_Field Pattern_Field 2 Blue Striped 3 Green Checkered 4 (NULL) Polka-Dot Catatan: dalam gabungan, Anda tidak melihat kata "NULL" saat Anda menampilkan data; Gunakan kata kunci "NULL" saat Anda bekerja dengan gabungan. Di Microsoft query, kedua jenis gabungan luar dapat dibuat dengan mudah menggunakan mouse (untuk informasi selengkapnya tentang prosedur ini, lihat halaman 112 dari "panduan pengguna Microsoft query," versi 1,0). Berikut ini adalah bagaimana pernyataan SQL mungkin mencari contoh kedua gabungan luar: SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM {oj `c:\database`.Color_Table LEFT OUTER JOIN `c:\database`.Pattern_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} Untuk membuat contoh praktis gabungan luar, Buatlah daftar produk perusahaan dengan angka penjualan untuk produk yang telah dijual, namun tidak termasuk produk yang belum terjual. Untuk melakukan ini, gunakan bidang ID Produk untuk bergabung dengan tabel produk dan tabel penjualan.

Gabungan subtract

Jenis gabungan ketiga adalah gabungan subtract. Gabungan subtract berlawanan dengan gabungan luar; ini hanya menyertakan catatan tersebut dalam satu tabel yang tidak cocok dengan catatan apa pun di tabel lainnya. Seperti gabungan luar, ada dua kemungkinan pengurangan subtract untuk dua tabel yang ingin Anda gabungkan; Namun, mereka biasanya tidak disebut sebagai "Left subtract Join" atau "Right subtract Join." Gabungan subtract biasanya mengembalikan bidang dari salah satu tabel, karena dengan definisi bidang tabel lain hanya mengembalikan nilai NULL. Berikut ini adalah salah satu kemungkinan subtract: join_Field Color_Field 1 Red dan di sini adalah yang lainnya: Join_Field Pattern_Field 4 Polka-Dot Dalam Microsoft query, gabungan subtract dibuat dengan terlebih dahulu membuat gabungan luar, lalu menggunakan kriteria "IS NULL" pada bidang yang sesuai (Pattern_Field dalam contoh pertama di atas; Color_Field dalam contoh kedua) untuk mengecualikan rekaman yang cocok antara tabel. Berikut ini adalah bagaimana pernyataan SQL mungkin mencari penggabungan subtract pertama di atas: SELECT Color_Table.Join_Field, Color_Table.Color_Field FROM {oj `c:\database`.Color_Table LEFT OUTER JOIN `c:\database`.Pattern_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} WHERE (Pattern_Table.Pattern_Field Is Null) Untuk membuat contoh praktis gabungan subtract, daftar pelanggan yang belum pernah dipesan baru-baru ini. Untuk melakukan ini, gunakan bidang ID pesanan untuk bergabung dengan tabel pelanggan dan tabel pesanan.

Gabungan luar penuh

Gabungan tipe keempat adalah gabungan luar penuh. Gabungan luar penuh adalah kombinasi gabungan luar dengan gabungan subtract gratis. Gabungan luar penuh menyertakan semua catatan dari kedua tabel dan menggabungkan catatan tersebut yang umum antara kedua tabel. Berikut ini adalah gabungan luar penuh: Join_Field Color_Field Pattern_Field 1 Red (NULL) 2 Blue Striped 3 Green Checkered 4 (NULL) Polka-Dot Di Microsoft query, gabungan luar penuh dibuat dengan menyisipkan operator UNION pada baris-nya sendiri antara pernyataan SELECT Outer Join dan pernyataan subtract SELECT (Lihat di atas). Untuk membuat gabungan luar penuh di Microsoft query, gunakan langkah-langkah berikut ini untuk membuat kalimat SQL terkait:

1. Buat gabungan luar, lalu klik baru pada menu file dan buat gabungan subtract.

2. Salin SQL gabungan subtract.

3. Beralih ke gabungan luar, ketikkan gabungan kata pada baris di bawah pernyataan SQL gabungan luar, tempelkan SQL gabungan di bawah kata UNION, dan tutup jendela subtract gabungan.

Catatan Hasil dari tabel berikut ini melewatkan nilai Join_Field "4" karena catatan yang nilainya Join_Field sama dengan 4 dalam tabel Pattern_Table. Kalimat SQL di bawah ini tidak memilih bidang Pattern_Table. John_Field. Join_Field Color_Field Pattern_Field (NULL) Polka-Dot 1 Red (NULL) 2 Blue Striped 3 Green Checkered Berikut ini adalah bagaimana SQL mungkin terlihat untuk gabungan luar penuh di atas (perintah di bawah operator gabungan ditempelkan): SELECT Color_Table.Join_Field, Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM {oj `C:\database`.Pattern_Table LEFT OUTER JOIN `C:\database`.Color_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} UNION SELECT Color_Table.Join_Field, Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM {oj `C:\database`.Color_Table LEFT OUTER JOIN `C:\database`.Pattern_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} WHERE (Color_Table.Color_Field Is Null) or(Pattern_Table.Pattern_Field Is Null) Untuk membuat contoh praktis gabungan luar penuh, gabungkan daftar pelanggan yang tumpang tindih yang digunakan oleh Departemen yang berbeda, termasuk Nomor Faks (yang hanya ada di daftar pertama), dan nama email internet (yang hanya ada di daftar kedua). Setiap departemen bisa terus menggunakan daftar parsial saat memiliki daftar lengkap yang tersedia. Mereka dapat bergabung dalam bidang ID pelanggan.

Bergabung sendiri

Jenis gabungan kelima adalah gabungan mandiri. Penggabungan mandiri adalah koneksi tempat bidang dalam tabel dicocokkan dengan bidang yang berbeda dalam salinan tabel yang sama. Menggunakan tabel contoh ini: Table_Three Employee_ID Employee_Name Reports_To 1 Bob 3 2 Sue (NULL) 3 Jim 2 4 Jane 3 dan salinannya, sebagai berikut: Table_Three_01 Employee_ID Employee_Name Reports_To 1 Bob 3 2 Sue (NULL) 3 Jim 2 4 Jane 3 Gabungan diri dapat digunakan untuk membuat daftar nama karyawan dengan nama penyelia mereka. Employee_ID di Table_Three akan digabungkan dengan Reports_To di Table_Three_01. Yang berikut ini mungkin terlihat terlebih dahulu: Employee_Name Employee_Name Bob Jim Sue (NULL) Jim Sue Jane Jim Namun, karena membingungkan untuk memiliki nama bidang yang sama untuk kedua bidang, Ubah salah satu nama bidang, sebagai berikut: Employee_Name Supervisor Bob Jim Sue (NULL) Jim Sue Jane Jim Berikut ini adalah bagaimana SQL mungkin mencari gabungan di atas: SELECT table_three.Employee_Name, table_three_01.Employee_Name 'Supervisor' FROM `c:\database`.table_three, `c:\database`.table_three_01 WHERE table_three.Employee_ID = table_three_01.Reports_To Saat Anda mengembalikan data ke Microsoft Excel, itu tidak digunakan untuk mengganti nama bidang di Microsoft query. Ini benar karena Microsoft Excel menggunakan nama bidang asli. Untuk informasi selengkapnya tentang masalah ini, lihat artikel berikut ini di Basis Pengetahuan Microsoft: 121551 : XL5: bidang, dan bukan nama kolom dalam MSQUERY yang dikembalikan ke Excel, Microsoft Excel makro harus mengubah nama kolom setiap kali data yang dikembalikan refresh (kecuali Anda mengembalikan data dalam PivotTable, dalam hal ini Pivot itu sendiri bisa membuat dan menyimpan nama bidang kustom).

Gabungan gabungan dan alami

Hampir semua bergabung, termasuk semua contoh yang diberikan sejauh ini, adalah penggabungan dan gabungan alami. Arti dari istilah ini sangat penting bagi pengguna rata-rata Microsoft query, tapi dua paragraf berikutnya berusaha menjelaskan istilah bagi mereka yang mungkin penasaran. Gabungan Equi adalah gabungan rekaman yang diambil berdasarkan bidang gabungan yang memiliki nilai yang cocok dalam kedua tabel. Itu mungkin tampak seperti definisi gabungan, tetapi tidak. Contoh gabungan non-sama adalah gabungan tempat rekaman dalam tabel pertama digabungkan ke catatan tersebut dalam tabel kedua di mana bidang gabungan dalam tabel pertama lebih besar dari (bukan sama dengan) bidang yang digabungkan dalam tabel kedua (atau kurang dari, atau apa pun selain sama dengan). Tentu saja, ini mengembalikan lebih banyak catatan daripada gabungan ekui. Gabungan alami adalah salah satu dari bidang gabungan dua tabel yang dikembalikan. Karena kedua bidang ini menurut definisi identik dalam Equi-Join, sangat berlebihan untuk menyertakan keduanya. Untuk gabungan yang tidak sama, penting untuk menyertakan kedua bidang tersebut. Jadi, gabungan Equi dan gabungan alami menyatu. Anda menginginkan gabungan yang sama (yang paling menggambarkan penggabungan) menjadi gabungan alami dengan mengembalikan salah satu bidang yang digabungkan; Tapi, jika Anda pernah menggunakan gabungan non-sama, Anda mungkin juga ingin menjadikannya gabungan non-alami dengan mengembalikan kedua bidang yang digabungkan. Ada gabungan jenis lain. Spektrum lengkap gabungan yang paling baru ditentukan dalam 1992 dan standar ini dikenal sebagai SQL-92. Beberapa penggabungan tidak penting untuk pengguna Microsoft Excel karena penggabungan ini melakukan hal-hal yang lebih mudah dilakukan di Microsoft Excel.

Produk Cartesian

Mencoba mengembalikan data dari dua atau beberapa tabel tanpa gabungan apa pun yang disebut "produk Cartesian." Produk Cartesian ditentukan sebagai semua kombinasi baris yang mungkin di semua tabel. Pastikan Anda telah bergabung sebelum mencoba mengembalikan data, karena produk Cartesian pada tabel dengan banyak catatan dan/atau di beberapa tabel dapat memakan waktu berjam-jam untuk diselesaikan. Berikut ini adalah produk Cartesian seperti yang digunakan pada dua tabel contoh; Perhatikan bahwa tabel ini hanya 3 Catatan kali 3 rekaman, yang menghasilkan total 9 catatan. Namun, bayangkan jika bukan, tabel adalah 100 catatan kali 1.000 mencatat waktu 10.000 Records; lalu tabel akan berisi catatan 1.000.000.000! Join_Field Join_Field Color_Field Pattern_Field 1 2 Red Striped 1 3 Red Checkered 1 4 Red Polka-Dot 2 2 Blue Striped 2 3 Blue Checkered 2 4 Blue Polka-Dot 3 2 Green Striped 3 3 Green Checkered 3 4 Green Polka-Dot Terkadang, beberapa pengguna ingin menggunakan produk Kartesian; Namun, sebagian besar pengguna yang membuatnya secara tidak sengaja, dan sering dikelirukan karenanya. Karena sebagian besar pengguna mengecualikan sebagian besar bidang dalam gabungan, produk Cartesian dapat dengan mudah terlihat seperti ini: Color_Field Red Red Red Blue Blue Blue Green Green Green Jika catatan 100 ditambahkan ke Pattern_Table, kueri ini akan memiliki catatan 309 (103 merekam masing-masing berwarna merah, biru, dan hijau). Produk Cartesian memiliki pernyataan SELECT yang relatif sederhana. Berikut ini adalah bagaimana SQL mungkin mencari produk Cartesian di atas: SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM `c:\database`.Color_Table, `c:\database`.Pattern_Table Contoh praktis dari produk Kartesian adalah membuat daftar semua kemungkinan kombinasi opsi pada produk merchandise, dengan total harga untuk setiap kombinasi.