全部产品
Search

搜索本产品

    MaxCompute：Subqueries

    文档中心

    MaxCompute：Subqueries

    更新时间：Jul 02, 2025

    Anda dapat menggunakan subquery untuk melakukan kueri lebih lanjut berdasarkan hasil dari sebuah kueri. Subquery berguna untuk menghitung nilai agregat secara terpisah, memeriksa keberadaan rekaman, menyaring data, mengaitkan operasi pembaruan atau penghapusan, menyederhanakan JOIN, menggunakan hasil sebagai tabel turunan, atau sebagai dasar untuk pengurutan dan perbandingan baris-per-baris. Topik ini menjelaskan definisi dan penggunaan subquery yang didukung di MaxCompute.

    Deskripsi

    Subquery bersarang dalam pernyataan untuk melakukan kueri data kompleks. MaxCompute mendukung jenis-jenis subquery berikut:

    • Subquery Dasar

      Subquery dalam klausa FROM digunakan sebagai tabel sementara untuk komputasi kompleks atau konversi data dalam kueri.

    • IN SUBQUERY

      IN subquery digunakan dalam klausa WHERE untuk mencocokkan sekelompok nilai yang dikembalikan oleh subquery. Cocok untuk menanyakan data dari baris yang sesuai dengan kondisi tertentu.

    • NOT IN SUBQUERY

      Subquery NOT IN digunakan untuk mengecualikan satu set data dari set lainnya. Jika Anda menggunakan subquery NOT IN dalam klausa WHERE, baris yang memenuhi kondisi subquery akan dihapus dari hasil (kueri utama).

    • EXISTS SUBQUERY

      EXISTS subquery digunakan dalam kueri utama untuk memeriksa apakah subquery mengembalikan baris apa pun. Cocok untuk memeriksa keberadaan rekaman tanpa memedulikan isi yang dikembalikan.

    • NOT EXISTS SUBQUERY

      NOT EXISTS subquery bekerja berlawanan dengan EXISTS subquery. Rekaman dalam kueri utama hanya dipilih jika subquery tidak mengembalikan baris. Cocok untuk menanyakan baris yang tidak cocok dalam subquery.

    • SCALAR SUBQUERY

      Scalar subquery mengembalikan tepat satu nilai kolom dari satu baris. Dalam kebanyakan kasus, scalar subquery dapat digunakan dalam pernyataan SELECT atau klausa WHERE atau HAVING. Cocok untuk menghitung nilai agregat tertentu atau mendapatkan nilai kolom dari sebuah baris.

    Catatan

    Beberapa subquery seperti SCALAR, IN, NOT IN, EXISTS, dan NOT EXISTS dapat dikonversi menjadi operasi JOIN selama eksekusi. MAPJOIN adalah algoritma JOIN yang efisien. Jika hasil dari SUBQUERY adalah tabel kecil, Anda dapat menggunakan HINT dalam pernyataan subquery untuk secara eksplisit menentukan algoritma MAPJOIN.

    Data sampel

    Data sampel sumber disediakan untuk membantu Anda lebih memahami contoh-contoh dalam topik ini. Pernyataan sampel:

    -- Buat tabel partisi bernama sale_detail.
create table if not exists sale_detail
(
shop_name     string,
customer_id   string,
total_price   double
)
partitioned by (sale_date string, region string);

-- Tambahkan partisi ke tabel sale_detail.
alter table sale_detail add partition (sale_date='2013', region='china') partition (sale_date='2014', region='shanghai');

-- Masukkan data ke dalam tabel sale_detail.
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);

    Kueri data dalam tabel sale_detail. Pernyataan sampel:

    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   |
+------------+-------------+-------------+------------+------------+

    Subquery Dasar

    Objek kueri umum adalah tabel tujuan. Anda juga dapat menggunakan pernyataan SELECT sebagai objek kueri. Dalam hal ini, pernyataan SELECT dianggap sebagai subquery. Subquery dalam klausa FROM dapat digunakan sebagai tabel. Anda dapat join subquery dengan tabel atau subquery lainnya. Untuk informasi lebih lanjut tentang operasi JOIN, lihat JOIN.

    • Sintaksis

      select <select_expr> from (<select_statement>) [<sq_alias_name>];

    • Parameter

      • select_expr: wajib. Nilai parameter ini dalam format col1_name, col2_name, Regular expression,.... Format ini menunjukkan kolom biasa atau kolom kunci partisi yang ingin Anda tanyakan atau ekspresi reguler yang digunakan untuk kueri.

      • select_statement: wajib. Parameter ini menentukan klausa subquery. Untuk informasi lebih lanjut tentang sintaksis, lihat Sintaksis SELECT.

      • sq_alias_name: opsional. Parameter ini menentukan alias subquery.

      • table_name: wajib. Parameter ini menentukan nama tabel yang ingin Anda tanyakan.

    • Contoh

      • Contoh 1: Pernyataan sintaksis subquery:

        set odps.sql.allow.fullscan=true;
select * from (select shop_name from sale_detail) a;

        Hasil berikut dikembalikan:

        +------------+
| shop_name  |
+------------+
| s1         |
| s2         |
| s3         |
| null       |
| s6         |
| s7         |
+------------+

      • Contoh 2: Dalam contoh ini, subquery dalam klausa FROM digunakan sebagai tabel, dan subquery tersebut joined dengan tabel atau subquery lainnya. Pernyataan sampel:

        -- Buat tabel dan gabungkan tabel dengan subquery.
create table shop as select shop_name,customer_id,total_price from sale_detail;
select a.shop_name, a.customer_id, a.total_price from
(select * from shop) a join sale_detail on a.shop_name = sale_detail.shop_name;

        Hasil berikut dikembalikan:

        +------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| null       | c5          | NULL        |
| s6         | c6          | 100.4       |
| s7         | c7          | 100.5       |
| s1         | c1          | 100.1       |
| s2         | c2          | 100.2       |
| s3         | c3          | 100.3       |
+------------+-------------+-------------+

    IN SUBQUERY

    IN SUBQUERY digunakan dengan cara yang mirip dengan LEFT SEMI JOIN.

    • Sintaksis

      • Sintaksis 1

        select<select_expr1>from<table_name1>where<select_expr2>in(select<select_expr3>from<table_name2>);
-- Pernyataan sebelumnya setara dengan pernyataan berikut dengan LEFT SEMI JOIN:
select<select_expr1>from<table_name1><alias_name1>leftsemijoin<table_name2><alias_name2>on<alias_name1>.<select_expr2>=<alias_name2>.<select_expr3>;
        Catatan

        Jika select_expr2 menentukan kolom kunci partisi, select <select_expr2> from <table_name2> tidak dikonversi menjadi LEFT SEMI JOIN. Pekerjaan terpisah dimulai untuk menjalankan subquery. MaxCompute membandingkan hasil subquery dengan kolom yang Anda tentukan dalam select_expr2 secara berurutan. Jika partisi tabel yang ditentukan oleh table_name1 berisi kolom dalam select_expr2 dan kolom-kolom ini tidak termasuk dalam hasil, MaxCompute tidak membaca data dari partisi-partisi ini. Ini memastikan bahwa pemangkasan partisi tetap valid.

      • Sintaksis 2

        MaxCompute mendukung IN SUBQUERY dan kondisi terkait. where <table_name2_colname> = <table_name1>.<colname> adalah kondisi terkait. MaxCompute V1.0 tidak mendukung ekspresi yang merujuk tabel sumber dari subquery dan kueri utama. MaxCompute V2.0 mendukung ekspresi semacam itu. Kondisi filter ini adalah bagian dari kondisi ON dalam operasi SEMI JOIN.

        select<select_expr1>from<table_name1>where<select_expr2>in(select<select_expr3>from<table_name2>where
<table_name1>.<col_name>=<table_name2>.<col_name>);
        Catatan

        MaxCompute mendukung IN SUBQUERY yang tidak berfungsi sebagai kondisi JOIN. Sebagai contoh, klausa non-WHERE menggunakan IN SUBQUERY, atau klausa WHERE menggunakan IN SUBQUERY yang tidak dapat dikonversi menjadi kondisi JOIN. Dalam hal ini, IN SUBQUERY tidak dapat dikonversi menjadi SEMI JOIN. Pekerjaan terpisah harus dimulai untuk menjalankan subquery. Kondisi terkait tidak didukung.

      • Sintaksis 3

        IN SUBQUERY mendukung subquery multi-kolom berdasarkan kemampuan dan batasan sebelumnya. Aturan ini juga berlaku untuk PostgreSQL. Jika Anda menggunakan Sintaksis 3 untuk IN SUBQUERY, Anda tidak perlu membagi kueri menjadi subquery. Subquery multi-kolom mengurangi satu operasi JOIN dan menghemat sumber daya komputasi. Anda dapat menggunakan subquery multi-kolom dengan cara berikut:

        • Gunakan pernyataan SELECT sederhana di mana Anda menentukan beberapa kolom untuk ekspresi IN SUBQUERY.

        • Gunakan fungsi agregat untuk ekspresi IN SUBQUERY. Untuk informasi lebih lanjut tentang fungsi agregat, lihat Fungsi agregat.

        • Gunakan konstanta untuk ekspresi IN SUBQUERY.

    • Parameter

      • select_expr1: wajib. Nilai parameter ini dalam format col1_name, col2_name, Regular expression,.... Parameter ini menentukan kolom biasa atau kolom kunci partisi yang ingin Anda tanyakan atau ekspresi reguler yang digunakan untuk kueri.

      • table_name1 dan table_name2: wajib. Parameter ini menentukan nama tabel.

      • select_expr2 dan select_expr3: wajib. Parameter ini menentukan nama kolom dalam tabel yang ditentukan oleh table_name1 dan table_name2. Kolom dalam dua tabel dipetakan satu sama lain.

      • col_name: wajib. Parameter ini menentukan nama kolom dalam tabel.

    • Catatan Penggunaan

      Jika Anda menggunakan ekspresi IN SUBQUERY, nilai null secara otomatis dikecualikan dari hasil yang dikembalikan oleh subquery.

    • Contoh

      • Contoh 1: Gunakan Sintaksis 1. Pernyataan sampel:

        set odps.sql.allow.fullscan=true;
select * from sale_detail where total_price in (select total_price from shop);

        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  |
| s6        | c6          | 100.4       | 2014      | shanghai |
| s7        | c7          | 100.5       | 2014      | shanghai |
+-----------+-------------+-------------+-----------+--------+

      • Contoh 2: Gunakan Sintaksis 2. Pernyataan sampel:

        set odps.sql.allow.fullscan=true;
select * from sale_detail where total_price in (select total_price from shop where customer_id = shop.customer_id);

        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  |
| s6        | c6          | 100.4       | 2014      | shanghai |
| s7        | c7          | 100.5       | 2014      | shanghai |
+-----------+-------------+-------------+-----------+--------+

      • Contoh 3: Beberapa kolom ditentukan dalam pernyataan SELECT untuk subquery. Pernyataan sampel:

        -- Data sampel direkonstruksi untuk membantu Anda memahami contoh ini.
create table if not exists t1(a bigint,b bigint,c bigint,d bigint,e bigint);
create table if not exists t2(a bigint,b bigint,c bigint,d bigint,e bigint);
insert into table t1 values (1,3,2,1,1),(2,2,1,3,1),(3,1,1,1,1),(2,1,1,0,1),(1,1,1,0,1);
insert into table t2 values (1,3,5,0,1),(2,2,3,1,1),(3,1,1,0,1),(2,1,1,0,1),(1,1,1,0,1);
-- Skenario 1: Ekspresi IN SUBQUERY adalah pernyataan SELECT sederhana di mana Anda menentukan beberapa kolom.
select a, b from t1 where (c, d) in (select a, b from t2 where e = t1.e);
-- Hasil berikut dikembalikan:
+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 3          |
| 2          | 2          |
| 3          | 1          |
+------------+------------+
-- Skenario 2: Ekspresi IN SUBQUERY menggunakan fungsi agregat.
select a, b from t1 where (c, d) in (select max(a), b from t2 where e = t1.e group by b having max(a) > 0);
-- Hasil berikut dikembalikan:
+------------+------------+
| a          | b          |
+------------+------------+
| 2          | 2          |
+------------+------------+
-- Skenario 3: Ekspresi IN SUBQUERY menggunakan konstanta.
select a, b from t1 where (c, d) in ((1, 3), (1, 1));
-- Hasil berikut dikembalikan:
+------------+------------+
| a          | b          |
+------------+------------+
| 2          | 2          |
| 3          | 1          |
+------------+------------+

    NOT IN SUBQUERY

    NOT IN SUBQUERY digunakan dengan cara yang mirip dengan LEFT ANTI JOIN. Namun, jika nilai baris adalah NULL untuk kolom tertentu dalam tabel yang ingin Anda tanyakan, nilai ekspresi dalam NOT IN SUBQUERY adalah NULL. Dalam hal ini, kondisi WHERE tidak valid, dan tidak ada data yang dikembalikan. Logika pemrosesan ini berbeda dari LEFT ANTI JOIN.

    • Sintaksis

      • Sintaksis 1

        select <select_expr1> from <table_name1> where <select_expr2> not in (select <select_expr2> from <table_name2>);
-- Pernyataan sebelumnya setara dengan pernyataan berikut dengan LEFT ANTI JOIN.
select <select_expr1> from <table_name1> <alias_name1> left anti join <table_name2> <alias_name2> on <alias_name1>.<select_expr1> = <alias_name2>.<select_expr2>;
        Catatan

        Jika select_expr2 menentukan kolom kunci partisi, select <select_expr2> from <table_name2> tidak dikonversi menjadi LEFT ANTI JOIN. Pekerjaan terpisah dimulai untuk menjalankan subquery. MaxCompute membandingkan hasil subquery dengan kolom yang ditentukan dalam select_expr2 secara berurutan. Jika partisi tabel yang ditentukan oleh table_name1 berisi kolom dalam select_expr2 dan kolom-kolom ini tidak termasuk dalam hasil, MaxCompute tidak membaca data dari partisi-partisi ini. Ini memastikan bahwa pemangkasan partisi tetap valid.

      • Sintaksis 2

        MaxCompute mendukung NOT IN SUBQUERY dan kondisi terkait. where <table_name2_colname> = <table_name1>.<colname> adalah kondisi terkait. MaxCompute V1.0 tidak mendukung ekspresi yang merujuk tabel sumber dari subquery dan kueri utama. MaxCompute V2.0 mendukung ekspresi semacam itu. Ekspresi ini adalah bagian dari kondisi ON dalam operasi ANTI JOIN.

        select <select_expr1> from <table_name1> where <select_expr2> not in (select <select_expr2> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);
        Catatan

        MaxCompute mendukung NOT IN SUBQUERY yang tidak berfungsi sebagai kondisi JOIN. Sebagai contoh, klausa non-WHERE menggunakan NOT IN SUBQUERY, atau klausa WHERE menggunakan NOT IN SUBQUERY yang tidak dapat dikonversi menjadi kondisi JOIN. Dalam hal ini, NOT IN SUBQUERY tidak dapat dikonversi menjadi ANTI JOIN. Pekerjaan terpisah harus dimulai untuk menjalankan subquery. Kondisi terkait tidak didukung.

      • Sintaksis 3

        NOT IN SUBQUERY mendukung subquery multi-kolom berdasarkan kemampuan dan batasan sebelumnya. Aturan ini juga berlaku untuk PostgreSQL. Jika Anda menggunakan Sintaksis 3 untuk NOT IN SUBQUERY, Anda tidak perlu membagi kueri menjadi beberapa subquery. Subquery multi-kolom mengurangi satu operasi JOIN dan menghemat sumber daya komputasi. Anda dapat menggunakan subquery multi-kolom dengan cara berikut:

        • Gunakan pernyataan SELECT sederhana di mana Anda menentukan beberapa kolom untuk ekspresi NOT IN SUBQUERY.

        • Gunakan fungsi agregat untuk ekspresi NOT IN SUBQUERY. Untuk informasi lebih lanjut tentang fungsi agregat, lihat Fungsi agregat.

        • Gunakan konstanta untuk ekspresi NOT IN SUBQUERY.

    • Parameter

      • select_expr1: wajib. Nilai parameter ini dalam format col1_name, col2_name, Regular expression,.... Parameter ini menentukan kolom biasa atau kolom kunci partisi yang ingin Anda tanyakan atau ekspresi reguler yang digunakan untuk kueri.

      • table_name1 dan table_name2: wajib. Parameter ini menentukan nama tabel.

      • select_expr2 dan select_expr3: wajib. Parameter ini menentukan nama kolom dalam tabel yang ditentukan oleh table_name1 dan table_name2. Kolom dalam dua tabel dipetakan satu sama lain.

      • col_name: wajib. Parameter ini menentukan nama kolom dalam tabel.

    • Catatan Penggunaan

      Jika Anda menggunakan ekspresi NOT IN SUBQUERY, nilai null secara otomatis dikecualikan dari hasil yang dikembalikan oleh subquery.

    • Contoh

      • Contoh 1: Gunakan Sintaksis 1. Pernyataan sampel:

        -- Buat tabel bernama shop1 dan masukkan data ke dalam tabel.
create table shop1 as select shop_name,customer_id,total_price from sale_detail;
insert into shop1 values ('s8','c1',100.1);

select * from shop1 where shop_name not in (select shop_name from sale_detail);

        Hasil berikut dikembalikan:

        +------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s8         | c1          | 100.1       |
+------------+-------------+-------------+

      • Contoh 2: Gunakan Sintaksis 2. Pernyataan sampel:

        set odps.sql.allow.fullscan=true;
select * from shop1 where shop_name not in (select shop_name from sale_detail where customer_id = shop1.customer_id);

        Hasil berikut dikembalikan:

        +------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s8         | c1          | 100.1       |
+------------+-------------+-------------+

      • Contoh 3: NOT IN SUBQUERY tidak berfungsi sebagai kondisi JOIN. Pernyataan sampel:

        set odps.sql.allow.fullscan=true;
select * from shop1 where shop_name not in (select shop_name from sale_detail) and total_price < 100.3;

        NOT IN SUBQUERY tidak dapat dikonversi menjadi ANTI JOIN. Ini karena klausa WHERE mencakup operator AND. Pekerjaan terpisah dimulai untuk menjalankan subquery.

        Hasil berikut dikembalikan:

        +------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s8         | c1          | 100.1       |
+------------+-------------+-------------+

      • Contoh 4: Jika nilai baris dalam tabel yang ingin Anda tanyakan adalah NULL, tidak ada data yang dikembalikan. Pernyataan sampel:

        -- Buat tabel bernama sale dan masukkan data ke dalam tabel.
create table if not exists sale
(
shop_name     string,
customer_id   string,
total_price   double
)
partitioned by (sale_date string, region string);
alter table sale add partition (sale_date='2013', region='china');
insert into sale partition (sale_date='2013', region='china') values ('null','null',null),('s2','c2',100.2),('s3','c3',100.3),('s8','c8',100.8);

set odps.sql.allow.fullscan=true;
select * from sale where shop_name not in (select shop_name from sale_detail);

        Hasil berikut dikembalikan:

        +------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
+------------+-------------+-------------+------------+------------+

      • Contoh 5: Beberapa kolom ditentukan dalam pernyataan SELECT untuk subquery. Pernyataan sampel:

        -- Data sampel direkonstruksi untuk membantu Anda memahami contoh ini. Data sampel sama dengan IN SUBQUERY.
create table if not exists t1(a bigint,b bigint,c bigint,d bigint,e bigint);
create table if not exists t2(a bigint,b bigint,c bigint,d bigint,e bigint);
insert into table t1 values (1,3,2,1,1),(2,2,1,3,1),(3,1,1,1,1),(2,1,1,0,1),(1,1,1,0,1);
insert into table t2 values (1,3,5,0,1),(2,2,3,1,1),(3,1,1,0,1),(2,1,1,0,1),(1,1,1,0,1);
-- Skenario 1: Ekspresi NOT IN SUBQUERY adalah pernyataan SELECT sederhana di mana Anda menentukan beberapa kolom.
select a, b from t1 where (c, d) not in (select a, b from t2 where e = t1.e);
-- Hasil berikut dikembalikan:
+------------+------------+
| a          | b          |
+------------+------------+
| 2          | 1          |
| 1          | 1          |
+------------+------------+
-- Skenario 2: Ekspresi NOT IN SUBQUERY menggunakan fungsi agregat.
select a, b from t1 where (c, d) not in (select max(a), b from t2 where e = t1.e group by b having max(a) > 0);
-- Hasil berikut dikembalikan:
+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 3          |
| 3          | 1          |
| 2          | 1          |
| 1          | 1          |
+------------+------------+
-- Skenario 3: Ekspresi NOT IN SUBQUERY menggunakan konstanta.
select a, b from t1 where (c, d) not in ((1, 3), (1, 1));
-- Hasil berikut dikembalikan:
+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 3          |
| 2          | 1          |
| 1          | 1          |
+------------+------------+

    EXISTS SUBQUERY

    Ketika Anda menggunakan klausa EXISTS SUBQUERY, jika subquery mengembalikan setidaknya satu baris data, True dikembalikan. Jika subquery tidak mengembalikan data, False dikembalikan.

    MaxCompute hanya mendukung subquery WHERE yang memiliki kondisi terkait. Untuk menggunakan klausa EXISTS SUBQUERY, Anda harus mengonversi klausa ini menjadi LEFT SEMI JOIN.

    • Sintaksis

      select <select_expr> from <table_name1> where exists (select <select_expr> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);

    • Parameter

      • select_expr: wajib. Nilai parameter ini dalam format col1_name, col2_name, Regular expression,.... Format ini menunjukkan kolom biasa atau kolom kunci partisi yang ingin Anda tanyakan atau ekspresi reguler yang digunakan untuk kueri.

      • table_name1 dan table_name2: wajib. Parameter ini menentukan nama tabel.

      • col_name: wajib. Parameter ini menentukan nama kolom dalam tabel.

    • Contoh

      set odps.sql.allow.fullscan=true;
select * from sale_detail where exists (select * from shop where customer_id = sale_detail.customer_id);
-- Pernyataan sebelumnya setara dengan pernyataan berikut:
select * from sale_detail a left semi join shop b on a.customer_id = b.customer_id;

      Hasil berikut dikembalikan:

      +------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

    NOT EXISTS SUBQUERY

    Ketika Anda menggunakan klausa NOT EXISTS SUBQUERY, jika tidak ada data yang dikembalikan, True dikembalikan. Jika tidak, False dikembalikan.

    MaxCompute hanya mendukung subquery WHERE yang memiliki kondisi terkait. Untuk menggunakan klausa NOT EXISTS SUBQUERY, Anda harus mengonversi klausa ini menjadi LEFT ANTI JOIN.

    • Sintaksis

      select <select_expr> from <table_name1> where not exists (select <select_expr> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);

    • Parameter

      • select_expr: wajib. Nilai parameter ini dalam format col1_name, col2_name, Regular expression,.... Format ini menunjukkan kolom biasa atau kolom kunci partisi yang ingin Anda tanyakan atau ekspresi reguler yang digunakan untuk kueri.

      • table_name1 dan table_name2: wajib. Parameter ini menentukan nama tabel.

      • col_name: wajib. Parameter ini menentukan nama kolom dalam tabel.

    • Contoh

      set odps.sql.allow.fullscan=true;
select * from sale_detail where not exists (select * from shop where shop_name = sale_detail.shop_name);
-- Pernyataan sebelumnya setara dengan pernyataan berikut:
select * from sale_detail a left anti join shop b on a.shop_name = b.shop_name;

      Hasil berikut dikembalikan:

      +------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
+------------+-------------+-------------+------------+------------+

    SCALAR SUBQUERY

    Jika hasil keluaran klausa SCALAR SUBQUERY hanya berisi satu baris dan satu kolom data, hasil tersebut dapat digunakan sebagai skalar untuk komputasi data. Jika hasil keluaran SCALAR SUBQUERY hanya berisi satu baris dan satu operator MAX atau MIN bersarang di luar SCALAR SUBQUERY, hasilnya tidak berubah. SCALAR SUBQUERY juga mendukung penggunaan multi-kolom. Sebagai contoh, klausa SELECT dapat berisi ekspresi SCALAR SUBQUERY dengan beberapa kolom, dan hanya mendukung ekspresi nilai sama; klausa SELECT dapat berupa ekspresi BOOLEAN, dan hanya mendukung perbandingan nilai sama; klausa WHERE mendukung perbandingan multi-kolom, dan hanya mendukung perbandingan nilai sama.

    • Sintaksis

      • Sintaksis 1:

        select <select_expr> from <table_name1> where (<select count(*) from <table_name2> where <table_name2_colname> = <table_name1>.<colname>) <Scalar operator> <scalar_value>;
-- Pernyataan sebelumnya setara dengan pernyataan berikut:
select <table_name1>.<select_expr> from <table_name1> left semi join (select <colname>, count(*) from <table_name2> group by <colname> having count(*) <Scalar operator> <scalar_value>) <table_name2> on <table_name1>.<colname> = <table_name2>.<colname>;
        Catatan

        • Hasil keluaran select count(*) from <table_name2> where <table_name2_colname> = <table_name1>.<colname> adalah himpunan baris. Keluaran hanya berisi satu baris dan satu kolom data. Dalam hal ini, hasilnya dapat digunakan sebagai skalar. Dalam aplikasi praktis, SCALAR SUBQUERY dikonversi menjadi JOIN sebanyak mungkin.

        • Hasil keluaran SCALAR SUBQUERY dapat digunakan sebagai skalar hanya jika Anda dapat memastikan dalam fase kompilasi bahwa SCALAR SUBQUERY hanya mengembalikan satu baris dan satu kolom data. Jika Anda tidak dapat membuat konfirmasi ini hingga fase runtime, kompiler akan melaporkan kesalahan. Kompiler dapat mengompilasi pernyataan yang memenuhi persyaratan berikut:

          • Daftar SELECT SCALAR SUBQUERY menggunakan fungsi agregat yang tidak termasuk dalam parameter fungsi tabel bernilai pengguna tertentu (UDTF).

          • SCALAR SUBQUERY yang menggunakan fungsi agregat tidak mencakup klausa GROUP BY.

      • Sintaksis 2:

        select (<select_statement>) from <table_name>;

    • Parameter

      • select_expr: wajib. Nilai parameter ini dalam format col1_name, col2_name, Regular expression,.... Format ini menunjukkan kolom biasa atau kolom kunci partisi yang ingin Anda tanyakan atau ekspresi reguler yang digunakan untuk kueri.

      • table_name1 dan table_name2: wajib. Parameter ini menentukan nama tabel.

      • col_name: wajib. Parameter ini menentukan nama kolom dalam tabel.

      • Scalar operator: wajib. Operator skalar bisa lebih besar dari (>), kurang dari (<), sama dengan (=), lebih besar dari atau sama dengan (>=), atau kurang dari atau sama dengan (<=).

      • scalar_value: wajib. Parameter ini menentukan nilai skalar.

      • select_statement: wajib. Parameter ini menentukan pernyataan subquery. Jika pernyataan subquery mengikuti sintaksis 2, hasil subquery harus berisi hanya satu baris. Untuk informasi lebih lanjut tentang sintaksis, lihat Sintaksis SELECT.

    • Batasan

      • SCALAR SUBQUERY dapat merujuk kolom dari kueri utama. Jika SCALAR SUBQUERY menggunakan sarang bertingkat, hanya kolom paling luar yang dapat dirujuk.

        -- Pernyataan sampel yang dapat dieksekusi:
select * from t1 where (select count(*) from t2 where t1.a = t2.a) = 3; 
-- Pernyataan sampel yang tidak dapat dieksekusi. Ini karena kolom dari kueri utama tidak dapat dirujuk dalam pernyataan SELECT untuk subquery.
select * from t1 where (select count(*) from t2 where (select count(*) from t3 where t3.a = t1.a) = 2) = 3;

    • Contoh

      • Contoh 1: Penggunaan umum. Pernyataan sampel:

        set odps.sql.allow.fullscan=true;
select * from shop where (select count(*) from sale_detail where sale_detail.shop_name = shop.shop_name) >= 1;

        Hasil berikut dikembalikan:

        +------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s1         | c1          | 100.1       |
| s2         | c2          | 100.2       |
| s3         | c3          | 100.3       |
| null       | c5          | NULL        |
| s6         | c6          | 100.4       |
| s7         | c7          | 100.5       |
+------------+-------------+-------------+

      • Contoh 2: Beberapa kolom ditentukan dalam pernyataan SELECT untuk subquery. Pernyataan sampel:

        -- Data sampel direkonstruksi untuk membantu Anda memahami contoh ini.
create table if not exists ts(a bigint,b bigint,c double);
create table if not exists t(a bigint,b bigint,c double);
insert into table ts values (1,3,4.0),(1,3,3.0);
insert into table t values (1,3,4.0),(1,3,5.0);
-- Skenario 1: Pernyataan SELECT berisi ekspresi scalar subquery di mana beberapa kolom ditentukan. Ekspresi tersebut harus berupa ekspresi kesetaraan. Pernyataan sampel yang tidak dapat dieksekusi: select (select a, b from t where c > ts.c) as (a, b), a from ts;
select (select a, b from t where c = ts.c) as (a, b), a from ts;
-- Hasil berikut dikembalikan:
+------------+------------+------------+
| a          | b          | a2         |
+------------+------------+------------+
| 1          | 3          | 1          |
| NULL       | NULL       | 1          |
+------------+------------+------------+
-- Skenario 2: Pernyataan SELECT berisi ekspresi tipe BOOLEAN. Hanya perbandingan kesetaraan yang didukung. Pernyataan sampel yang tidak dapat dieksekusi: select (a,b) > (select a,b from ts where c = t.c) from t;
select (a,b) = (select a,b from ts where c = t.c) from t;
-- Hasil berikut dikembalikan:
+------+
| _c0  |
+------+
| true |
| false |
+------+
-- Skenario 3: Klausa WHERE mendukung perbandingan multi-kolom. Hanya perbandingan kesetaraan yang didukung. Pernyataan sampel yang tidak dapat dieksekusi: select * from t where (a,b) > (select a,b from ts where c = t.c);
select * from t where c > 3.0 and (a,b) = (select a,b from ts where c = t.c);
-- Hasil berikut dikembalikan:
+------------+------------+------------+
| a          | b          | c          |
+------------+------------+------------+
| 1          | 3          | 4.0        |
+------------+------------+------------+
select * from t where c > 3.0 or (a,b) = (select a,b from ts where c = t.c);
-- Hasil berikut dikembalikan:
+------------+------------+------------+
| a          | b          | c          |
+------------+------------+------------+
| 1          | 3          | 4.0        |
| 1          | 3          | 5.0        |
+------------+------------+------------+

      • Contoh 3: Gunakan sintaksis subquery sintaksis 2. Pernyataan sampel:

        set odps.sql.allow.fullscan=true;
select (select * from sale_detail where shop_name='s1') from sale_detail;

        Hasil berikut dikembalikan:

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

    Referensi

    Sejumlah besar subquery atau penggunaan subquery yang tidak tepat dapat menyebabkan kueri lambat, terutama dalam lingkungan big data. Anda dapat menggunakan tabel sementara atau tampilan materialisasi sebagai pengganti subquery, atau merekonstruksi beberapa subquery menjadi operasi JOIN untuk meningkatkan efisiensi kueri. Untuk informasi lebih lanjut, lihat Rekomendasi dan manajemen tampilan materialisasi dan JOIN.