All Products
Search

This Product

    MaxCompute:Subqueries

    Document Center

    MaxCompute:Subqueries

    Last Updated:Aug 21, 2026

    Subquery adalah pernyataan SELECT yang bersarang dalam kueri lain dan memungkinkan Anda mengkueri set hasil dari kueri tersebut. Kasus penggunaan umum mencakup menghitung nilai agregat, memeriksa keberadaan baris, memfilter data berdasarkan hasil kueri lain, melakukan pembaruan atau penghapusan berkorelasi, menyederhanakan operasi JOIN untuk mengembalikan satu nilai, menggunakan hasil sebagai tabel turunan untuk query luar, menyediakan dasar untuk pengurutan atau pengelompokan, serta melakukan perbandingan baris per baris. Topik ini menjelaskan jenis-jenis subquery yang didukung MaxCompute dan cara menggunakannya.

    Fitur

    MaxCompute mendukung jenis subquery berikut:

    • Subquery dasar

      Subquery dasar digunakan dalam klausa FROM suatu kueri dan berperan sebagai tabel turunan. Anda dapat menggunakannya untuk perhitungan kompleks atau transformasi data dalam suatu kueri.

    • IN subquery

      Gunakan IN subquery dalam klausa WHERE untuk memilih baris dari query luar yang sesuai dengan kumpulan nilai yang dikembalikan oleh subquery.

    • NOT IN subquery

      NOT IN subquery digunakan dalam klausa WHERE untuk menghapus baris dari query luar yang sesuai dengan set hasil subquery.

    • EXISTS subquery

      EXISTS subquery menguji apakah subquery mengembalikan baris apa pun. Subquery ini berguna untuk memeriksa keberadaan baris terkait tanpa memerlukan data aktual yang dikandungnya.

    • NOT EXISTS subquery

      NOT EXISTS subquery memilih baris dari query luar yang tidak memiliki kecocokan dalam subquery.

    • Scalar subquery

      Scalar subquery mengembalikan satu nilai (satu baris dengan satu kolom). Subquery ini biasanya digunakan dalam daftar SELECT atau sebagai nilai kondisi dalam klausa WHERE atau HAVING.

    Catatan

    Pada waktu eksekusi, query optimizer sering mengonversi beberapa subquery—seperti scalar, IN, NOT IN, EXISTS, atau NOT EXISTS subquery—menjadi operasi JOIN. MAPJOIN adalah algoritma broadcast join yang efisien. Jika Anda menentukan bahwa hasil subquery merupakan tabel kecil, Anda dapat menambahkan petunjuk ke subquery tersebut untuk secara eksplisit menggunakan algoritma MAPJOIN. Untuk informasi lebih lanjut, lihat SUBQUERY_MAPJOIN HINT.

    Data sampel

    Contoh dalam topik ini menggunakan tabel sale_detail. Jalankan pernyataan berikut untuk membuat dan mengisinya:

    -- Buat tabel partisi
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
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china') PARTITION (sale_date='2014', region='shanghai');

-- Masukkan data ke partisi 2013/china
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china')
VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);

-- Masukkan data ke partisi 2014/shanghai
INSERT INTO sale_detail PARTITION (sale_date='2014', region='shanghai')
VALUES ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);

    Untuk memverifikasi data yang dimasukkan:

    SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail;

    Hasil:

    +------------+-------------+-------------+------------+------------+
| 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   |
+------------+-------------+-------------+------------+------------+
    Mengkueri tabel partisi tanpa menentukan partisi memerlukan SET odps.sql.allow.fullscan=true; sebelum pernyataan tersebut. Di lingkungan produksi, tentukan partisi secara langsung untuk menghindari full table scan.

    Subquery dasar

    Kueri standar beroperasi pada tabel utama. Namun, kueri juga dapat beroperasi pada pernyataan select lain. Jenis kueri ini disebut subquery. Dalam klausa from, subquery dapat diperlakukan sebagai tabel dan digabungkan dengan tabel atau subquery lain menggunakan operasi join. Untuk informasi lebih lanjut tentang operasi join, lihat JOIN.

    • Sintaks

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

    • Parameter

      • select_expr: Wajib. Formatnya adalah col1_name, col2_name, regular expression,..., yang menentukan kolom reguler, kolom kunci partisi, atau ekspresi reguler yang akan dikueri.

      • select_statement: Wajib. Pernyataan subquery. Untuk informasi lebih lanjut tentang formatnya, lihat Sintaks SELECT.

      • sq_alias_name: Opsional. Alias untuk subquery.

      • table_name: Wajib. Nama tabel utama.

    • Contoh

      • Contoh 1: Perintah berikut memberikan contoh sintaks subquery dasar.

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

        Hasil:

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

      • Contoh 2: Dalam klausa from, subquery dapat diperlakukan sebagai tabel dan digunakan dalam operasi join dengan tabel atau subquery lain. Contoh perintahnya sebagai berikut.

        -- Buat tabel lalu lakukan operasi join.
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:

        +------------+-----------------+-----------------+
| _c0        | scalarsubquery1 | scalarsubquery2 |
+------------+-----------------+-----------------+
| 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 mirip dengan left semi join.

    • Sintaks

      • Format 1

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

        Jika select_expr2 adalah kolom kunci partisi, subquery select <select_expr2> from <table_name2> dieksekusi dalam pekerjaan terpisah dan tidak dikonversi menjadi semi join. Hasil eksekusi kemudian dibandingkan dengan select_expr2. Partisi dalam table_name1 tidak dibaca jika nilai select_expr2-nya tidak ada dalam hasil yang dikembalikan. Hal ini memastikan bahwa partition pruning tetap efektif.

      • Format 2

        MaxCompute tidak hanya mendukung in subquery tetapi juga kondisi berkorelasi. Klausa where <table_name2_colname> = <table_name1>.<colname> dalam subquery merupakan kondisi berkorelasi. MaxCompute 1.0 tidak mendukung ekspresi yang mereferensikan baik tabel sumber dalam subquery maupun tabel sumber dalam query luar. MaxCompute 2.0 mendukung penggunaan ini, di mana kondisi filter ini menjadi bagian dari kondisi on dalam 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 digunakan sebagai kondisi join, misalnya jika berada di luar klausa where atau berada dalam klausa where tetapi tidak dapat dikonversi menjadi kondisi join. Dalam kasus ini, subquery tidak dapat dikonversi menjadi semi join, pekerjaan terpisah harus dijalankan untuk mengeksekusi subquery, dan kondisi berkorelasi tidak didukung.

      • Format 3

        Berdasarkan kemampuan di atas, MaxCompute kompatibel dengan dukungan PostgreSQL untuk multiple columns. Dibandingkan dengan membagi implementasi menjadi beberapa subquery, pendekatan ini mengurangi satu proses JOIN dan menghemat sumber daya komputasi. Penggunaan multi-kolom yang didukung adalah sebagai berikut:

        • Ekspresi yang mengikuti in dapat berupa pernyataan SELECT multi-kolom sederhana.

        • Anda dapat menggunakan fungsi agregat dalam ekspresi yang mengikuti in. Untuk informasi lebih lanjut, lihat Fungsi agregat.

        • Ekspresi setelah in dapat berupa konstanta.

    • Parameter

      • select_expr1: Wajib. Formatnya adalah col1_name, col2_name, regular expression,.... Parameter ini menentukan kolom reguler, kolom kunci partisi, atau ekspresi reguler yang akan dikueri.

      • table_name1, table_name2: Wajib. Nama tabel.

      • select_expr2, select_expr3: Wajib. Nama kolom yang dipetakan dari table_name1 dan table_name2.

      • col_name: Wajib. Nama kolom.

    • Catatan penggunaan

      Saat Anda menggunakan subquery dengan IN, baris yang berisi nilai NULL secara otomatis dihapus dari set hasil subquery.

    • Contoh

      • Contoh 1: Gunakan sintaks subquery Format 1.

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

        Hasil:

        +-----------+-------------+-------------+-----------+--------+
| 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 sintaks subquery Format 2.

        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:

        +-----------+-------------+-------------+-----------+--------+
| 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: Menggunakan pernyataan SELECT multi-kolom.

        -- Untuk mempermudah pemahaman, data sampel dibuat ulang di sini.
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 setelah `in` adalah pernyataan SELECT multi-kolom sederhana.
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: Gunakan fungsi agregat dalam ekspresi setelah `in`.
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 setelah `in` adalah 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 mirip dengan left anti join, tetapi keduanya tidak identik. Jika kolom yang ditentukan yang dikembalikan oleh subquery berisi nilai NULL apa pun, ekspresi not in dievaluasi sebagai NULL. Hal ini menyebabkan kondisi where gagal, dan tidak ada data yang dikembalikan. Ini berbeda dari left anti join.

    • Sintaks

      • Format 1

        select <select_expr1> from <table_name1> where <select_expr2> not in (select <select_expr2> from <table_name2>);
-- Pernyataan ini setara dengan pernyataan LEFT ANTI JOIN berikut.
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 adalah kolom kunci partisi, subquery select <select_expr2> from <table_name2> dieksekusi dalam pekerjaan terpisah dan tidak dikonversi menjadi anti join. Hasil eksekusi dibandingkan dengan nilai select_expr2. Partisi dalam table_name1 yang nilai select_expr2-nya tidak ada dalam set hasil yang dikembalikan tidak akan dibaca, sehingga memastikan partition pruning tetap efektif.

      • Format 2

        MaxCompute tidak hanya mendukung subquery NOT IN tetapi juga kondisi berkorelasi. Klausa where <table_name2_colname> = <table_name1>.<colname> dalam subquery merupakan kondisi berkorelasi. MaxCompute 1.0 tidak mendukung ekspresi yang mereferensikan baik tabel sumber dalam subquery maupun tabel sumber query luar. MaxCompute 2.0 mendukung penggunaan ini. Kondisi filter ini merupakan bagian dari kondisi ON dalam 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 saat tidak digunakan sebagai kondisi join, seperti ketika muncul di luar klausa where atau berada dalam klausa where tetapi tidak dapat dikonversi menjadi kondisi join. Dalam kasus ini, subquery tidak dapat dikonversi menjadi anti join. Pekerjaan terpisah harus diluncurkan untuk menjalankan subquery, dan kondisi berkorelasi tidak didukung.

      • Format 3

        Berdasarkan kemampuan di atas, MaxCompute kompatibel dengan dukungan PostgreSQL untuk multiple columns. Dibandingkan dengan membagi implementasi menjadi beberapa subquery, pendekatan ini mengurangi satu proses JOIN dan menghemat sumber daya komputasi. Penggunaan multi-kolom yang didukung adalah sebagai berikut:

        • Ekspresi setelah not in dapat berupa pernyataan SELECT yang mengembalikan multiple columns.

        • Anda dapat menggunakan fungsi agregat dalam ekspresi yang mengikuti not in. Untuk informasi lebih lanjut, lihat Fungsi agregat.

        • Ekspresi yang mengikuti not in dapat berupa konstanta.

    • Parameter

      • select_expr1: Wajib. Formatnya adalah col1_name, col2_name, regular expression,.... Parameter ini menentukan kolom reguler, kolom kunci partisi, atau ekspresi reguler yang akan dikueri.

      • table_name1, table_name2: Wajib. Nama tabel.

      • select_expr2, select_expr3: Wajib. Nama kolom yang dipetakan dari table_name1 dan table_name2.

      • col_name: Wajib. Nama kolom.

    • Catatan penggunaan

      Saat Anda menggunakan subquery dengan NOT IN, baris yang berisi nilai NULL secara otomatis dihapus dari set hasil subquery.

    • Contoh

      • Contoh 1: Gunakan sintaks subquery Format 1.

        -- Buat tabel baru bernama shop1 dan masukkan data ke dalamnya.
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:

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

      • Contoh 2: Gunakan sintaks subquery Format 2.

        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:

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

      • Contoh 3: not in subquery tidak digunakan sebagai kondisi join. Berikut contoh perintahnya.

        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;

        Karena klausa where berisi and, kueri tidak dapat dikonversi menjadi anti join dan malah dieksekusi sebagai subquery dalam pekerjaan terpisah.

        Hasil:

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

      • Contoh 4: Jika hasil subquery berisi nilai NULL, tidak ada data yang dikembalikan. Perintah berikut memberikan contohnya.

        -- Buat tabel baru bernama sale dan masukkan data ke dalamnya.
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:

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

      • Contoh 5: Menggunakan pernyataan SELECT multi-kolom.

        -- Untuk mempermudah pemahaman, data sampel dibuat ulang di sini. Data ini sama dengan data sampel di bagian 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 setelah `not in` adalah pernyataan SELECT multi-kolom sederhana.
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: Gunakan fungsi agregat dalam ekspresi setelah `not in`.
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 setelah `not in` adalah 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

    Saat Anda menggunakan exists subquery, subquery tersebut mengembalikan True jika subquery memiliki setidaknya satu baris data, dan False jika tidak.

    MaxCompute hanya mendukung subquery where yang berisi kondisi berkorelasi. exists subquery diimplementasikan sebagai left semi join.

    • Sintaks

      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. Formatnya adalah col1_name, col2_name, regular expression,..., yang menentukan kolom reguler, kolom kunci partisi, atau ekspresi reguler yang akan dikueri.

      • table_name1, table_name2: Wajib. Nama tabel.

      • col_name: Wajib. Nama kolom.

    • Contoh

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

      Hasil:

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

    Saat Anda menggunakan not exists subquery, subquery tersebut mengembalikan True jika subquery tidak berisi data. Jika tidak, subquery mengembalikan False.

    MaxCompute hanya mendukung subquery where yang berisi kondisi berkorelasi. not exists subquery diimplementasikan sebagai left anti join.

    • Sintaks

      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. Formatnya adalah col1_name, col2_name, regular expression,..., yang menentukan kolom reguler, kolom kunci partisi, atau ekspresi reguler yang akan dikueri.

      • table_name1, table_name2: Wajib. Nama tabel.

      • col_name: Wajib. Nama kolom.

    • 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 ini setara dengan pernyataan berikut.
select * from sale_detail a left anti join shop b on a.shop_name = b.shop_name;

      Hasil:

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

    Scalar subquery

    Jika subquery mengembalikan satu baris dan satu kolom, subquery tersebut dapat digunakan sebagai nilai skalar dalam operasi skalar. Jika kueri hanya mengembalikan satu baris, Anda dapat membungkus kueri tersebut dalam fungsi max atau min tanpa mengubah hasilnya. Scalar subquery juga mendukung penggunaan multi-kolom. Misalnya, scalar subquery dapat digunakan sebagai ekspresi multi-kolom dalam daftar SELECT, tetapi hanya ekspresi kesetaraan yang didukung. Scalar subquery juga dapat digunakan sebagai ekspresi BOOLEAN dalam daftar SELECT, tetapi hanya untuk perbandingan kesetaraan. Dalam klausa where, perbandingan multi-kolom didukung, tetapi hanya untuk kesetaraan.

    • Sintaks

      • Format 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 ini 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

        • Output dari select count(*) from <table_name2> where <table_name2_colname> = <table_name1>.<colname> adalah set baris. Pernyataan ini hanya mengembalikan satu baris dan satu kolom. Oleh karena itu, dapat diperlakukan sebagai skalar. Namun, selama implementasi, pernyataan ini dikonversi menjadi operasi join bila memungkinkan.

        • Subquery hanya dapat digunakan sebagai skalar jika kompilator dapat memastikan pada waktu kompilasi bahwa subquery tersebut mengembalikan tepat satu baris dan satu kolom. Jika hal ini hanya dapat ditentukan pada waktu proses, kompilator akan mengembalikan error. Agar kompilator menerima pernyataan tersebut, dua syarat berikut harus dipenuhi:

          • Daftar SELECT subquery menggunakan fungsi agregat yang tidak berada dalam daftar parameter fungsi bernilai tabel.

          • Subquery yang berisi fungsi agregat tidak memiliki klausa group by.

      • Format 2

        select (<select_statement>) from <table_name>;

    • Parameter

      • select_expr: Wajib. Formatnya adalah col1_name, col2_name, regular expression,..., yang menentukan kolom reguler, kolom kunci partisi, atau ekspresi reguler yang akan dikueri.

      • table_name1, table_name2: Wajib. Nama tabel.

      • col_name: Wajib. Nama kolom.

      • scalar_operator: Wajib. Misalnya, lebih besar dari (>), lebih kecil dari (<), sama dengan (=), lebih besar dari atau sama dengan (>=), atau lebih kecil dari atau sama dengan (<=).

      • scalar_value: Wajib. Nilai skalar.

      • select_statement: Wajib. Pernyataan subquery. Jika Anda menggunakan Format 2, subquery harus hanya mengembalikan satu baris. Untuk informasi lebih lanjut tentang formatnya, lihat Sintaks SELECT.

    • Batasan

      • scalar subquery dapat mereferensikan kolom dari query luar, tetapi ketika beberapa pernyataan scalar subquery bersarang, subquery hanya dapat mereferensikan kolom dari query luar langsungnya.

        -- Operasi yang diizinkan.
select * from t1 where (select count(*) from t2 where t1.a = t2.a) = 3; 
-- Operasi yang tidak diizinkan. Subquery bersarang mereferensikan `t1.a`, yang berada dua level di luar, bukan dari layer luar langsungnya.
select * from t1 where (select count(*) from t2 where (select count(*) from t3 where t3.a = t1.a) = 2) = 3;

    • Contoh

      • Contoh 1: Perintah berikut memberikan contoh kasus penggunaan umum.

        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:

        +------------+-------------+-------------+
| 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: Menggunakan pernyataan SELECT multi-kolom.

        -- Untuk mempermudah pemahaman, data sampel dibuat ulang di sini.
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: Kolom SELECT adalah ekspresi scalar subquery dengan multiple columns. Hanya ekspresi kesetaraan yang didukung. Penggunaan tidak valid: `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: Kolom SELECT adalah ekspresi Boolean. Hanya perbandingan kesetaraan yang didukung. Penggunaan tidak valid: `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. Penggunaan tidak valid: `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: Menggunakan sintaks subquery Format 2.

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

        Hasil:

        +------------+-----------------+-----------------+-----------------+-----------------+
| _c0        | scalarsubquery1 | scalarsubquery2 | scalarsubquery3 | scalarsubquery4 |
+------------+-----------------+-----------------+-----------------+-----------------+
| 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           |
+------------+-----------------+-----------------+-----------------+-----------------+

    Topik terkait

    Penggunaan subquery yang berlebihan atau penulisan subquery yang tidak efisien dapat menurunkan performa kueri, terutama di lingkungan data besar. Untuk meningkatkan efisiensi kueri, pertimbangkan alternatif seperti tabel temporary, Tampilan yang di-materialisasi, atau merefaktor beberapa subquery menjadi operasi JOIN. Untuk informasi lebih lanjut, lihat Tampilan yang di-materialisasi dan JOIN.