JOIN - MaxCompute - 阿里云

MaxCompute memungkinkan Anda menggunakan operasi JOIN untuk menggabungkan tabel dan mengembalikan data yang memenuhi kondisi penggabungan serta kueri. Topik ini menjelaskan berbagai jenis operasi JOIN seperti LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, INNER JOIN, NATURAL JOIN, implicit JOIN, dan multiple JOIN operations.

Ikhtisar

MaxCompute mendukung jenis-jenis JOIN berikut:

LEFT OUTER JOIN
Juga dikenal sebagai LEFT JOIN. Operasi ini mengembalikan semua baris dari tabel kiri, termasuk baris yang tidak cocok dengan baris apa pun di tabel kanan.
Catatan
Dalam operasi JOIN, tabel kiri biasanya merupakan tabel besar, sedangkan tabel kanan adalah tabel kecil dalam banyak kasus. Jika nilai-nilai di beberapa baris tabel kanan duplikat, disarankan untuk tidak melakukan beberapa operasi LEFT JOIN berturut-turut. Melakukan beberapa operasi LEFT JOIN berturut-turut dapat menyebabkan pembengkakan data dan mengganggu pekerjaan Anda.
RIGHT OUTER JOIN
Juga dikenal sebagai RIGHT JOIN. Operasi ini mengembalikan semua baris dari tabel kanan, termasuk baris yang tidak cocok dengan baris apa pun di tabel kiri.
FULL OUTER JOIN
Juga dikenal sebagai FULL JOIN. Operasi ini mengembalikan semua baris dari kedua tabel, baik kiri maupun kanan.
INNER JOIN
Kata kunci INNER bersifat opsional. Operasi INNER JOIN mengembalikan baris jika ada kecocokan antara tabel kiri dan kanan.
NATURAL JOIN
Dalam operasi NATURAL JOIN, bidang-bidang yang digunakan untuk menggabungkan dua tabel ditentukan berdasarkan bidang umum dari kedua tabel tersebut. MaxCompute mendukung OUTER NATURAL JOIN. Jika klausa USING digunakan, operasi NATURAL JOIN hanya mengembalikan bidang umum sekali.
Operasi JOIN implisit
Anda dapat melakukan operasi JOIN implisit tanpa perlu menentukan kata kunci JOIN secara eksplisit.
Operasi JOIN ganda
MaxCompute mendukung beberapa operasi JOIN. Anda dapat menggunakan tanda kurung () untuk menentukan prioritas operasi JOIN. Operasi JOIN yang dilingkupi oleh tanda kurung () memiliki prioritas lebih tinggi.

Catatan

Jika pernyataan SQL mencakup klausa WHERE dan klausa JOIN digunakan sebelum klausa WHERE, operasi JOIN dilakukan terlebih dahulu. Hasil operasi JOIN kemudian difilter berdasarkan kondisi yang ditentukan dalam klausa WHERE. Hasil akhirnya adalah irisan dari dua tabel, bukan semua baris dalam sebuah tabel.
Anda dapat menggunakan parameter odps.task.sql.outerjoin.ppd untuk mengontrol apakah akan menggunakan kondisi non-JOIN dalam klausa OUTER JOIN ON sebagai data input operasi JOIN. Parameter ini dapat dikonfigurasi pada tingkat proyek atau sesi.
- Jika parameter ini diatur ke false, kondisi non-JOIN dalam klausa ON dianggap sebagai kondisi dalam klausa WHERE untuk subquery operasi JOIN. Ini adalah perilaku non-standar. Disarankan untuk menentukan kondisi non-JOIN dalam klausa WHERE.
- Jika parameter ini diatur ke false, dua pernyataan SQL berikut setara. Namun, jika parameter ini diatur ke true, kedua pernyataan SQL tersebut tidak setara.
```
SELECT A.*, B.* FROM A LEFT JOIN B ON A.c1 = B.c1 and A.c2='xxx';

SELECT A.*, B.* FROM (SELECT * FROM A WHERE c2='xxx') A LEFT JOIN B ON A.c1 = B.c1;
```

Catatan Penggunaan

Dalam operasi JOIN, kondisi filter key is not null dari operasi JOIN secara otomatis ditambahkan untuk perhitungan. Baris dengan nilai null pada kunci join disaring setelah operasi JOIN.

Batasan

Saat melakukan operasi JOIN, perhatikan batasan berikut:

MaxCompute tidak mendukung CROSS JOIN. Operasi CROSS JOIN menggabungkan dua tabel tanpa memerlukan kondisi dalam klausa ON.
Anda harus menggunakan equi-joins dan menggabungkan kondisi menggunakan AND. Dalam operasi MAPJOIN, Anda dapat menggunakan non-equi joins atau menggabungkan beberapa kondisi menggunakan OR. Untuk informasi lebih lanjut, lihat MAPJOIN.

Sintaksis

<table_reference> JOIN <table_factor> [<join_condition>]
| <table_reference> {LEFT OUTER|RIGHT OUTER|FULL OUTER|INNER|NATURAL} JOIN <table_reference> <join_condition>

table_reference: Wajib. Pernyataan kueri untuk tabel kiri tempat operasi JOIN dilakukan. Format nilai parameter ini adalah table_name [alias] | table_query [alias] |....
table_factor: Wajib. Pernyataan kueri untuk tabel kanan atau tabel tempat operasi JOIN dilakukan. Format nilai parameter ini adalah table_name [alias] | table_subquery [alias] |....
join_condition: Opsional. Kondisi JOIN adalah kombinasi dari satu atau lebih ekspresi kesetaraan. Format nilai parameter ini adalah on equality_expression [and equality_expression].... equality_expression adalah ekspresi kesetaraan.

Catatan

Jika kondisi pemangkasan partisi ditentukan dalam klausa WHERE, pemangkasan partisi berlaku untuk tabel induk dan anak. Jika kondisi pemangkasan partisi ditentukan dalam klausa ON, pemangkasan partisi hanya berlaku untuk tabel anak. Akibatnya, pemindaian tabel penuh dijalankan untuk tabel induk. Untuk informasi lebih lanjut, lihat Periksa apakah pemangkasan partisi efektif.

Data sampel

Data sampel sumber disediakan untuk membantu Anda memahami contoh-contoh dalam topik ini. Pernyataan berikut menunjukkan cara membuat tabel sale_detail dan sale_detail_jt serta menyisipkan data ke dalam tabel.

-- Buat dua tabel partisi bernama sale_detail dan sale_detail_jt.
CREATE TABLE if NOT EXISTS sale_detail
(
shop_name     STRING,
customer_id   STRING,
total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

CREATE TABLE if NOT EXISTS sale_detail_jt
(
shop_name     STRING,
customer_id   STRING,
total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

-- Tambahkan partisi ke dua tabel.
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china') PARTITION (sale_date='2014', region='shanghai');
ALTER TABLE sale_detail_jt ADD PARTITION (sale_date='2013', region='china');

-- Sisipkan data ke dalam tabel.
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
INSERT INTO sale_detail PARTITION (sale_date='2014', region='shanghai') VALUES ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
INSERT INTO sale_detail_jt PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s5','c2',100.2);

-- Kueri data dari tabel sale_detail dan sale_detail_jt. Contoh pernyataan:
SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail;
-- Hasil berikut dikembalikan:
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail_jt;
-- Hasil berikut dikembalikan:
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s5         | c2          | 100.2       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

-- Buat tabel untuk operasi JOIN.
SET odps.sql.allow.fullscan=true;
CREATE TABLE shop AS SELECT shop_name, customer_id, total_price FROM sale_detail;

Contoh

Contoh berikut menunjukkan penggunaan JOIN berdasarkan Data sampel.

Contoh 1: LEFT OUTER JOIN. Contoh pernyataan:

-- Fitur pemindaian tabel penuh harus diaktifkan untuk tabel partisi. Jika tidak, operasi JOIN gagal.
SET odps.sql.allow.fullscan=true;
-- Baik tabel sale_detail_jt maupun sale_detail memiliki kolom shop_name. Anda harus menggunakan alias untuk membedakan antara kolom dalam pernyataan SELECT.
SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM sale_detail_jt a 
       LEFT OUTER JOIN sale_detail b ON a.shop_name=b.shop_name;

Hasil berikut dikembalikan:

+------------+------------+
| ashop      | bshop      |
+------------+------------+
| s2         | s2         |
| s1         | s1         |
| s5         | NULL       |
+------------+------------+

Contoh 2: RIGHT OUTER JOIN. Contoh pernyataan:

-- Fitur pemindaian tabel penuh harus diaktifkan untuk tabel partisi. Jika tidak, operasi JOIN gagal.
SET odps.sql.allow.fullscan=true;
-- Baik tabel sale_detail_jt maupun sale_detail memiliki kolom shop_name. Anda harus menggunakan alias untuk membedakan antara kolom dalam pernyataan SELECT.
SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM sale_detail_jt a 
       RIGHT OUTER JOIN sale_detail b ON a.shop_name=b.shop_name;

Hasil berikut dikembalikan:

+------------+------------+
| ashop      | bshop      |
+------------+------------+
| s1         | s1         |
| s2         | s2         |
| NULL       | s3         |
| NULL       | null       |
| NULL       | s6         |
| NULL       | s7         |
+------------+------------+

Contoh 3: FULL OUTER JOIN. Contoh pernyataan:

-- Fitur pemindaian tabel penuh harus diaktifkan untuk tabel partisi. Jika tidak, operasi JOIN gagal.
SET odps.sql.allow.fullscan=true;
-- Baik tabel sale_detail_jt maupun sale_detail memiliki kolom shop_name. Anda harus menggunakan alias untuk membedakan antara kolom dalam pernyataan SELECT.
SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM sale_detail_jt a 
       FULL OUTER JOIN sale_detail b ON a.shop_name=b.shop_name;

Hasil berikut dikembalikan:

+------------+------------+
| ashop      | bshop      |
+------------+------------+
| NULL       | s3         |
| NULL       | s6         |
| s2         | s2         |
| NULL       | null       |
| NULL       | s7         |
| s1         | s1         |
| s5         | NULL       |
+------------+------------+

Contoh 4: INNER JOIN. Contoh pernyataan:

-- Fitur pemindaian tabel penuh harus diaktifkan untuk tabel partisi. Jika tidak, operasi JOIN gagal.
SET odps.sql.allow.fullscan=true;
-- Baik tabel sale_detail_jt maupun sale_detail memiliki kolom shop_name. Anda harus menggunakan alias untuk membedakan antara kolom dalam pernyataan SELECT.
SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM sale_detail_jt a 
       INNER JOIN sale_detail b ON a.shop_name=b.shop_name;

Hasil berikut dikembalikan:

+------------+------------+
| ashop      | bshop      |
+------------+------------+
| s2         | s2         |
| s1         | s1         |
+------------+------------+

Contoh 5: NATURAL JOIN. Contoh pernyataan:

-- Fitur pemindaian tabel penuh harus diaktifkan untuk tabel partisi. Jika tidak, operasi JOIN gagal.
SET odps.sql.allow.fullscan=true;
-- Lakukan operasi NATURAL JOIN.
SELECT * FROM sale_detail_jt NATURAL JOIN sale_detail;
-- Pernyataan sebelumnya setara dengan pernyataan berikut:
SELECT sale_detail_jt.shop_name AS shop_name, sale_detail_jt.customer_id AS customer_id, sale_detail_jt.total_price AS total_price, sale_detail_jt.sale_date as sale_date, sale_detail_jt.region as region from sale_detail_jt 
INNER JOIN sale_detail 
ON sale_detail_jt.shop_name=sale_detail.shop_name AND sale_detail_jt.customer_id=sale_detail.customer_id and sale_detail_jt.total_price=sale_detail.total_price AND sale_detail_jt.sale_date=sale_detail.sale_date AND sale_detail_jt.region=sale_detail.region;

Hasil berikut dikembalikan:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

Contoh 6: implicit JOIN. Contoh pernyataan:

-- Fitur pemindaian tabel penuh harus diaktifkan untuk tabel partisi. Jika tidak, operasi JOIN gagal.
SET odps.sql.allow.fullscan=true;
-- Lakukan operasi JOIN implisit.
SELECT * FROM sale_detail_jt, sale_detail WHERE sale_detail_jt.shop_name = sale_detail.shop_name;
-- Pernyataan sebelumnya setara dengan pernyataan berikut:
SELECT * FROM sale_detail_jt JOIN sale_detail ON sale_detail_jt.shop_name = sale_detail.shop_name;

Hasil berikut dikembalikan:

+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     | shop_name2 | customer_id2 | total_price2 | sale_date2 | region2    |
+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+
| s2         | c2          | 100.2       | 2013       | china      | s2         | c2           | 100.2        | 2013       | china      |
| s1         | c1          | 100.1       | 2013       | china      | s1         | c1           | 100.1        | 2013       | china      |
+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+

Contoh 7: Multiple JOIN operations. Tidak ada prioritas yang ditentukan. Contoh pernyataan:

-- Fitur pemindaian tabel penuh harus diaktifkan untuk tabel partisi. Jika tidak, operasi JOIN gagal.
SET odps.sql.allow.fullscan=true;
-- Baik tabel sale_detail_jt maupun sale_detail memiliki kolom shop_name. Anda harus menggunakan alias untuk membedakan antara kolom dalam pernyataan SELECT.
SELECT a.* FROM sale_detail_jt a FULL OUTER JOIN sale_detail b ON a.shop_name=b.shop_name
        FULL OUTER JOIN sale_detail c ON a.shop_name=c.shop_name;

Hasil berikut dikembalikan:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s5         | c2          | 100.2       | 2013       | china      |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| s1         | c1          | 100.1       | 2013       | china      |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| s2         | c2          | 100.2       | 2013       | china      |
| NULL       | NULL        | NULL        | NULL       | NULL       |
+------------+-------------+-------------+------------+------------+

Contoh 8: Multiple JOIN operations. Gunakan tanda kurung () untuk menentukan prioritas operasi JOIN. Contoh pernyataan:

-- Fitur pemindaian tabel penuh harus diaktifkan untuk tabel partisi. Jika tidak, operasi JOIN gagal.
SET odps.sql.allow.fullscan=true;
-- Lakukan beberapa operasi JOIN. Gunakan tanda kurung () untuk menentukan prioritas.
SELECT * FROM shop JOIN (sale_detail_jt JOIN sale_detail ON sale_detail_jt.shop_name = sale_detail.shop_name) ON shop.shop_name=sale_detail_jt.shop_name;

Hasil berikut dikembalikan:

+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+------------+--------------+--------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     | shop_name2 | customer_id2 | total_price2 | sale_date2 | region2    | shop_name3 | customer_id3 | total_price3 | sale_date3 | region3    |
+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+------------+--------------+--------------+------------+------------+
| s2         | c2          | 100.2       | 2013       | china      | s2         | c2           | 100.2        | 2013       | china      | s2         | c2           | 100.2        | 2013       | china      |
| s1         | c1          | 100.1       | 2013       | china      | s1         | c1           | 100.1        | 2013       | china      | s1         | c1           | 100.1        | 2013       | china      |
+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+------------+--------------+--------------+------------+------------+

Contoh 9: Gunakan JOIN dan WHERE untuk menanyakan jumlah rekaman yang wilayahnya adalah china dan bidang shop_name memiliki nilai yang sama di kedua tabel. Semua rekaman dalam tabel sale_detail dipertahankan. Contoh pernyataan:

-- Fitur pemindaian tabel penuh harus diaktifkan untuk tabel partisi. Jika tidak, operasi JOIN gagal.
SET odps.sql.allow.fullscan=true;
-- Eksekusi pernyataan SQL berikut:
SELECT a.shop_name
    ,a.customer_id
    ,a.total_price
        ,b.total_price 
FROM  (SELECT * FROM sale_detail WHERE region = "china") a 
LEFT JOIN (SELECT * FROM sale_detail_jt WHERE region = "china") b 
ON   a.shop_name = b.shop_name;

Hasil berikut dikembalikan:

+------------+-------------+-------------+--------------+
| shop_name  | customer_id | total_price | total_price2 |
+------------+-------------+-------------+--------------+
| s1         | c1          | 100.1       | 100.1        |
| s2         | c2          | 100.2       | 100.2        |
| s3         | c3          | 100.3       | NULL         |
+------------+-------------+-------------+--------------+

Contoh pernyataan penggunaan yang salah:

SELECT a.shop_name
    ,a.customer_id
    ,a.total_price
        ,b.total_price 
FROM  sale_detail a 
LEFT JOIN sale_detail_jt b 
ON   a.shop_name = b.shop_name 
WHERE  a.region = "china" AND b.region = "china";

Hasil berikut dikembalikan:

+------------+-------------+-------------+--------------+
| shop_name  | customer_id | total_price | total_price2 |
+------------+-------------+-------------+--------------+
| s1         | c1          | 100.1       | 100.1        |
| s2         | c2          | 100.2       | 100.2        |
+------------+-------------+-------------+--------------+

Hasil yang dikembalikan adalah irisan dari dua tabel, bukan semua baris di tabel sale_detail.