Pahami bagaimana penempatan filter di klausa ON dan WHERE mempengaruhi hasil JOIN - MaxCompute

Saat menulis pernyataan SQL MaxCompute yang melibatkan operasi JOIN dan penyaringan data secara bersamaan, penting untuk memperhatikan posisi kondisi filter dalam berbagai jenis operasi JOIN. Urutan evaluasi dapat menghasilkan hasil kueri yang berbeda. Topik ini menjelaskan dampak dari kondisi filter pada hasil kueri dalam berbagai operasi JOIN.

Ikhtisar

Tabel berikut menjelaskan operasi JOIN yang didukung oleh SQL MaxCompute.

Operasi	Deskripsi
INNER JOIN	Mengembalikan baris yang memiliki nilai kolom yang cocok di tabel kiri dan tabel kanan berdasarkan kondisi join.
LEFT JOIN	Mengembalikan semua baris dari tabel kiri dan baris yang cocok dari tabel kanan berdasarkan kondisi join. Jika sebuah baris di tabel kiri tidak memiliki baris yang cocok di tabel kanan, nilai NULL dikembalikan di kolom dari tabel kanan dalam set hasil.
RIGHT JOIN	Mengembalikan semua baris dari tabel kanan dan baris yang cocok dari tabel kiri berdasarkan kondisi join. Jika sebuah baris di tabel kanan tidak memiliki baris yang cocok di tabel kiri, nilai NULL dikembalikan di kolom dari tabel kiri dalam set hasil.
FULL JOIN	Mengembalikan semua baris di tabel kiri dan tabel kanan baik kondisi join terpenuhi atau tidak. Dalam set hasil, nilai NULL dikembalikan di kolom dari tabel yang kekurangan baris yang cocok di tabel lainnya.
LEFT SEMI JOIN	Mengembalikan hanya baris di tabel kiri yang memiliki baris yang cocok di tabel kanan.
LEFT ANTI JOIN	Mengembalikan hanya baris di tabel kiri yang tidak memiliki baris yang cocok di tabel kanan.

Klausa ON dan klausa WHERE dapat digunakan dalam pernyataan SQL yang sama. Contohnya:

(SELECT * FROM A WHERE {subquery_where_condition} A) A
JOIN
(SELECT * FROM B WHERE {subquery_where_condition} B) B
ON {on_condition}
WHERE {where_condition}

Kondisi dalam pernyataan SQL tersebut dievaluasi dalam urutan berikut:

Kondisi {subquery_where_condition} dalam klausa WHERE subqueries
Kondisi {on_condition} dalam klausa ON
Kondisi {where_condition} dalam klausa WHERE setelah klausa JOIN

Oleh karena itu, hasil operasi JOIN mungkin berbeda tergantung apakah kondisi filter ditentukan di {subquery_where_condition}, {on_condition}, atau {where_condition}. Untuk informasi lebih lanjut, lihat Skenario sampel.

Tabel uji

Tabel A

Pernyataan sampel:

CREATE TABLE A AS SELECT * FROM VALUES (1, 20180101),(2, 20180101),(2, 20180102) t (key, ds);

Data sampel:

key	ds
1	20180101
2	20180101
2	20180102

Tabel B

Pernyataan sampel:

CREATE TABLE B AS SELECT * FROM VALUES (1, 20180101),(3, 20180101),(2, 20180102) t (key, ds);

Data sampel:

key	ds
1	20180101
3	20180101
2	20180102

Produk Kartesius Tabel A dan Tabel B

Pernyataan SQL berikut digunakan untuk menghitung Produk Kartesius:

SET odps.sql.allow.cartesian=true;
SELECT * FROM A,B;

Hasil berikut dikembalikan:

+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | 1    | 20180101 |
| 2    | 20180102 | 1    | 20180101 |
| 1    | 20180101 | 3    | 20180101 |
| 2    | 20180101 | 3    | 20180101 |
| 2    | 20180102 | 3    | 20180101 |
| 1    | 20180101 | 2    | 20180102 |
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
+------+------+------+------+

Skenario sampel

INNER JOIN
Operasi INNER JOIN pertama-tama mengambil Produk Kartesius dari baris di Tabel A dan Tabel B, lalu mengembalikan baris dengan nilai kolom yang cocok di kedua tabel berdasarkan klausa ON.
Kesimpulan: Operasi INNER JOIN mengembalikan hasil yang sama, terlepas dari apakah kondisi filter ditentukan di {subquery_where_condition}, {on_condition}, atau {where_condition}.
- Kasus 1: Tentukan kondisi filter dalam klausa {subquery_where_condition}, seperti yang ditunjukkan dalam pernyataan berikut:
```
SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;
```
  Hasil berikut dikembalikan:
```
+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+
```
- Kasus 2: Tentukan kondisi filter dalam klausa {on_condition}, seperti yang ditunjukkan dalam pernyataan berikut:
```
SELECT A.*, B.*
FROM A JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';
```
  Produk Kartesius Tabel A dan Tabel B berisi sembilan baris, di mana hanya satu yang memenuhi kondisi join. Hasil berikut dikembalikan:
```
+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+
```
- Kasus 3: Tentukan kondisi filter dalam klausa WHERE setelah klausa ON, seperti yang ditunjukkan dalam pernyataan berikut:
```
SELECT A.*, B.*
FROM A JOIN B
ON a.key = b.key
WHERE A.ds='20180101' and B.ds='20180101';
```
  Produk Kartesius Tabel A dan Tabel B berisi sembilan baris, di mana hanya tiga yang memenuhi kondisi join. Hasil berikut dikembalikan:
```
+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
+------+------+------+------+
```
  Prosesor kueri kemudian menyaring set hasil sebelumnya berdasarkan A.ds='20180101' and B.ds='20180101' kondisi filter. Hasil berikut dikembalikan:
```
+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+
```
LEFT JOIN
Operasi LEFT JOIN pertama-tama mengambil Produk Kartesius dari baris di Tabel A dan Tabel B, lalu mengembalikan semua baris Tabel A dan baris di Tabel B yang memenuhi kondisi join. Jika tidak ada baris yang cocok di Tabel B untuk baris di Tabel A, baris di Tabel A dikembalikan dengan nilai NULL di kolom Tabel B.
Kesimpulan: Operasi LEFT JOIN mungkin mengembalikan hasil yang berbeda, tergantung apakah kondisi filter ditentukan di {subquery_where_condition}, {on_condition}, atau {where_condition}.
- Operasi mengembalikan hasil yang sama, terlepas dari apakah kondisi filter untuk Tabel A ditentukan di {subquery_where_condition} atau {where_condition}.
- Operasi mengembalikan hasil yang sama, terlepas dari apakah kondisi filter untuk Tabel B ditentukan di {subquery_where_condition} atau {on_condition}.
- Kasus 1: Tentukan kondisi filter dalam klausa {subquery_where_condition}, seperti yang ditunjukkan dalam pernyataan berikut:
```
SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
LEFT JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;
```
  Hasil berikut dikembalikan:
```
+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | NULL | NULL |
+------+------+------+------+
```
- Kasus 2: Tentukan kondisi filter dalam klausa {on_condition}, seperti yang ditunjukkan dalam pernyataan berikut:
```
SELECT A.*, B.*
FROM A LEFT JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';
```
  Produk Kartesius Tabel A dan Tabel B berisi sembilan baris, di mana hanya satu yang memenuhi kondisi join. Dua baris lainnya di Tabel A tidak memiliki baris yang cocok di Tabel B, sehingga nilai NULL dikembalikan di kolom Tabel B untuk dua baris tersebut. Hasil berikut dikembalikan:
```
+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | NULL | NULL |
| 2    | 20180102 | NULL | NULL |
+------+------+------+------+
```
- Kasus 3: Tentukan kondisi filter dalam klausa WHERE setelah klausa ON, seperti yang ditunjukkan dalam pernyataan berikut:
```
SELECT A.*, B.*
FROM A LEFT JOIN B
ON a.key = b.key
WHERE A.ds='20180101' and B.ds='20180101';
```
  Produk Kartesius Tabel A dan Tabel B berisi sembilan baris, di mana hanya tiga yang memenuhi kondisi join. Hasil berikut dikembalikan:
```
+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
+------+------+------+------+
```
  Prosesor kueri kemudian menyaring set hasil sebelumnya berdasarkan kondisi filter A.ds='20180101' and B.ds='20180101'. Hasil berikut dikembalikan:
```
+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+
```
RIGHT JOIN
Operasi RIGHT JOIN mirip dengan operasi LEFT JOIN, kecuali bahwa kedua tabel digunakan secara terbalik. Operasi ini mengembalikan semua baris Tabel B dan baris di Tabel A yang memenuhi kondisi join.
- Kesimpulan: Operasi RIGHT JOIN mungkin mengembalikan hasil yang berbeda, tergantung apakah kondisi filter ditentukan di {subquery_where_condition}, {on_condition}, atau {where_condition}.
- Operasi mengembalikan hasil yang sama, terlepas dari apakah kondisi filter untuk Tabel B ditentukan di {subquery_where_condition} atau {where_condition}.
- Operasi mengembalikan hasil yang sama, terlepas dari apakah kondisi filter untuk Tabel A ditentukan di {subquery_where_condition} atau {on_condition}.
FULL JOIN
Operasi FULL JOIN mengambil Produk Kartesius dari baris di Tabel A dan Tabel B, lalu mengembalikan semua baris di kedua tabel, baik kondisi join terpenuhi atau tidak. Nilai NULL dikembalikan di kolom tabel yang kekurangan baris yang cocok di tabel lainnya.
Kesimpulan: Operasi FULL JOIN mungkin mengembalikan hasil yang berbeda, tergantung apakah kondisi filter ditentukan di {subquery_where_condition}, {on_condition}, atau {where_condition}.
- Kasus 1: Tentukan kondisi filter dalam klausa {subquery_where_condition}, seperti yang ditunjukkan dalam pernyataan berikut:
```
SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
FULL JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;
```
  Hasil berikut dikembalikan:
```
+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 2    | 20180101 | NULL | NULL |
| 1    | 20180101 | 1    | 20180101 |
| NULL | NULL | 3    | 20180101 |
+------+------+------+------+
```
- Kasus 2: Tentukan kondisi filter dalam klausa {on_condition}, seperti yang ditunjukkan dalam pernyataan berikut:
```
SELECT A.*, B.*
FROM A FULL JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';
```
  Produk Kartesius Tabel A dan Tabel B berisi sembilan baris, di mana hanya satu yang memenuhi kondisi join. Dalam set hasil, untuk dua baris di Tabel A yang tidak cocok dengan baris di Tabel B, nilai NULL dikembalikan di kolom Tabel B. Untuk dua baris di Tabel B yang tidak cocok dengan baris di Tabel A, nilai NULL dikembalikan di kolom Tabel A.
```
+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| NULL | NULL | 2    | 20180102 |
| 2    | 20180101 | NULL | NULL |
| 2    | 20180102 | NULL | NULL |
| 1    | 20180101 | 1    | 20180101 |
| NULL | NULL | 3    | 20180101 |
+------+------+------+------+
```
- Kasus 3: Tentukan kondisi filter dalam klausa WHERE setelah klausa ON, seperti yang ditunjukkan dalam pernyataan berikut:
```
SELECT A.*, B.*
FROM A FULL JOIN B
ON a.key = b.key
WHERE A.ds='20180101' and B.ds='20180101';
```
  Produk Kartesius Tabel A dan Tabel B berisi sembilan baris, di mana hanya tiga yang memenuhi kondisi join. Hasil berikut dikembalikan:
```
+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+
```
  Baris di Tabel B yang tidak memiliki baris yang cocok di Tabel A dikembalikan dalam set hasil, dengan nilai NULL di kolom Tabel A untuk baris tersebut. Hasil berikut dikembalikan:
```
+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
| 1    | 20180101 | 1    | 20180101 |
| NULL | NULL | 3    | 20180101 |
+------+------+------+------+
```
  Prosesor kueri kemudian menyaring set hasil sebelumnya berdasarkan kondisi filter A.ds='20180101' and B.ds='20180101'.
```
+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+
```
LEFT SEMI JOIN
Operasi LEFT SEMI JOIN mengembalikan hanya baris di Tabel A yang memiliki baris yang cocok di Tabel B. Operasi ini tidak mengembalikan baris dari Tabel B, sehingga Anda tidak dapat menentukan kondisi filter untuk Tabel B dalam klausa WHERE setelah klausa ON.
Kesimpulan: Operasi LEFT SEMI JOIN mengembalikan hasil yang sama, terlepas dari apakah kondisi filter ditentukan di {subquery_where_condition}, {on_condition}, atau {where_condition}.
- Kasus 1: Tentukan kondisi filter dalam klausa {subquery_where_condition}, seperti yang ditunjukkan dalam pernyataan berikut:
```
SELECT A.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
LEFT SEMI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;
```
  Hasil berikut dikembalikan:
```
+------+------+
| key  | ds   |
+------+------+
| 1    | 20180101 |
+------+------+
```
- Kasus 2: Tentukan kondisi filter dalam klausa {on_condition}, seperti yang ditunjukkan dalam pernyataan berikut:
```
SELECT A.*
FROM A LEFT SEMI JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';
```
  Hasil berikut dikembalikan:
```
+------+------+
| key  | ds   |
+------+------+
| 1    | 20180101 |
+------+------+
```
- Kasus 3: Tentukan kondisi filter dalam klausa WHERE setelah klausa ON, seperti yang ditunjukkan dalam pernyataan berikut:
```
SELECT A.*
FROM A LEFT SEMI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key
WHERE A.ds='20180101';
```
  Hasil berikut dikembalikan:
```
+------+------+
| key  | ds   |
+------+------+
| 1    | 20180101 |
+------+------+
```
  Prosesor kueri kemudian menyaring set hasil sebelumnya berdasarkan kondisi filter A.ds='20180101'. Hasil berikut dikembalikan:
```
+------+------+
| key  | ds   |
+------+------+
| 1    | 20180101 |
+------+------+
```
LEFT ANTI JOIN
Operasi LEFT ANTI JOIN mengembalikan hanya baris di Tabel A yang tidak memiliki baris yang cocok di Tabel B. Operasi ini tidak mengembalikan baris dari Tabel B, sehingga Anda tidak dapat menentukan kondisi filter untuk Tabel B dalam klausa WHERE setelah klausa ON. Operasi ini biasanya digunakan untuk menggantikan sintaks NOT EXISTS.
Kesimpulan: Operasi LEFT ANTI JOIN mungkin mengembalikan hasil yang berbeda, tergantung apakah kondisi filter ditentukan di {subquery_where_condition}, {on_condition}, atau {where_condition}.
- Operasi mengembalikan hasil yang sama, terlepas dari apakah kondisi filter untuk Tabel A ditentukan di {subquery_where_condition} atau {where_condition}.
- Operasi mengembalikan hasil yang sama, terlepas dari apakah kondisi filter untuk Tabel B ditentukan di {subquery_where_condition} atau {on_condition}.
- Kasus 1: Tentukan kondisi filter dalam klausa {subquery_where_condition}, seperti yang ditunjukkan dalam pernyataan berikut:
```
SELECT A.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
LEFT ANTI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;
```
  Hasil berikut dikembalikan:
```
+------+------+
| key  | ds   |
+------+------+
| 2    | 20180101 |
+------+------+
```
- Kasus 2: Tentukan kondisi filter dalam klausa {on_condition}, seperti yang ditunjukkan dalam pernyataan berikut:
```
SELECT A.*
FROM A LEFT ANTI JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';
```
  Hasil berikut dikembalikan:
```
+------+------+
| key  | ds   |
+------+------+
| 2    | 20180101 |
| 2    | 20180102 |
+------+------+
```
- Kasus 3: Tentukan kondisi filter dalam klausa WHERE setelah klausa ON, seperti yang ditunjukkan dalam pernyataan berikut:
```
SELECT A.*
FROM A LEFT ANTI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key
WHERE A.ds='20180101';
```
  Hasil berikut dikembalikan:
```
+------+------+
| key  | ds   |
+------+------+
| 2    | 20180101 |
| 2    | 20180102 |
+------+------+
```
  Prosesor kueri kemudian menyaring set hasil sebelumnya berdasarkan kondisi filter A.ds='20180101'. Hasil berikut dikembalikan:
```
+------+------+
| key  | ds   |
+------+------+
| 2    | 20180101 |
+------+------+
```

Tindakan pencegahan

Untuk operasi INNER JOIN atau LEFT SEMI JOIN, pernyataan SQL mengembalikan hasil yang sama, terlepas dari di mana Anda menentukan kondisi filter untuk tabel kiri dan tabel kanan.
Untuk operasi LEFT JOIN atau LEFT ANTI JOIN, kondisi filter untuk tabel kiri berfungsi sama baik ditentukan di {subquery_where_condition} atau {where_condition}. Kondisi filter untuk tabel kanan berfungsi sama baik ditentukan di {subquery_where_condition} atau {on_condition}.
Untuk operasi RIGHT JOIN, kondisi filter untuk tabel kiri berfungsi sama baik ditentukan di {subquery_where_condition} atau {on_condition}. Kondisi filter untuk tabel kanan berfungsi sama baik ditentukan di {subquery_where_condition} atau {where_condition}.
Untuk operasi FULL OUTER JOIN, kondisi filter hanya dapat ditentukan di {subquery_where_condition}.

Referensi

MaxCompute mendukung operasi JOIN dasar tertentu. Untuk informasi lebih lanjut tentang operasi JOIN dasar yang didukung oleh MaxCompute, lihat JOIN dan SEMI JOIN.
Jika Anda ingin menggabungkan tabel besar dan tabel kecil, Anda dapat secara eksplisit menentukan petunjuk MAPJOIN untuk meningkatkan kinerja kueri. Untuk informasi lebih lanjut tentang MAPJOIN HINT, lihat MAPJOIN HINT.
Jika Anda ingin menggabungkan tabel besar dan tabel berukuran sedang, Anda dapat menggunakan DISTRIBUTED MAPJOIN untuk meningkatkan kinerja kueri. Untuk informasi lebih lanjut tentang DISTRIBUTED MAPJOIN, lihat DISTRIBUTED MAPJOIN.
Jika dua tabel yang ingin Anda gabungkan berisi nilai kunci panas, masalah ekor panjang mungkin terjadi. Anda dapat menggunakan SKEWJOIN HINT untuk mengekstrak nilai kunci panas dari kedua tabel untuk mempercepat pemrosesan. Untuk informasi lebih lanjut tentang SKEWJOIN HINT, lihat SKEWJOIN HINT.