Subquery - PolarDB

Topik ini menjelaskan jenis-jenis subquery yang didukung oleh PolarDB-X 1.0, serta batasan dan pertimbangan tambahan saat menggunakan subquery di PolarDB-X 1.0.

Batasan

Dibandingkan dengan MySQL asli, PolarDB-X 1.0 memiliki batasan berikut terkait penggunaan subquery:

Subquery tidak dapat digunakan dalam klausa HAVING. Contoh:

SELECT name, AVG( quantity )
FROM tb1
GROUP BY name
HAVING AVG( quantity ) > 2* (
   SELECT AVG( quantity )
   FROM tb2
 );

Subquery tidak dapat digunakan dalam klausa JOIN ON. Contoh:

SELECT * FROM tb1 p JOIN tb2 s on (p.id=s.id and p.quantity>All(select quantity from tb3))

ROW subquery dan scalar subquery tidak dapat digunakan bersamaan sebelum dan sesudah tanda sama dengan (=). Contoh:
```
select * from tb1 where row(id, name) = (select id, name from tb2)        
```

Subquery tidak dapat digunakan dalam klausa UPDATE SET. Contoh:

UPDATE t1 SET c1 = (SELECT c2 FROM t2 WHERE t1.c1 = t2.c1) LIMIT 10

Pertimbangan tambahan

Di PolarDB-X 1.0, beberapa subquery hanya dapat dieksekusi menggunakan operator APPLY, menghasilkan query yang kurang efisien. Hindari pernyataan SQL berikut yang tidak efisien:

Pernyataan SQL dengan operator OR dalam klausa WHERE yang mencakup subquery. Efisiensi eksekusi menurun berdasarkan data pada tabel luar. Contoh:

Efisien: select * from tb1 where id in (select id from tb2)
Efisien: select * from tb1 where id in (select id from tb2) and id>3
Tidak efisien: select * from tb1 where id in (select id from tb2) or  id>3

Subquery terkait yang item terkaitnya digunakan dalam fungsi atau dikombinasikan dengan operator bukan sama dengan. Contoh:

Efisien: select * from tb1 a where id in
      (select id from tb2 b where a.name=b.name)
Tidak efisien: select * from tb1 a where id in
      (select id from tb2 b where UPPER(a.name)=b.name)
Tidak efisien: select * from tb1 a where id in
      (select id from tb2 b where a.decimal_test=abs(b.decimal_test))
Tidak efisien: select * from tb1 a where id in
      (select id from tb2 b where a.name! =b.name)
Tidak efisien: select * from tb1 a where id in
      (select id from tb2 b where a.name>=b.name)

Subquery terkait yang item terkaitnya dihubungkan dengan kondisi lain menggunakan operator OR. Contoh:

Efisien: select * from tb1 a where id in
      (select id from tb2 b where a.name=b.name
                                  and b.date_test<'2015-12-02')
Tidak efisien: select * from tb1 a where id in
      (select id from tb2 b where a.name=b.name
                                  or b.date_test<'2015-12-02')
Tidak efisien: select * from tb1 a where id in
      (select id from tb2 b where a.name=b.name
                                  or b.date_test=a.date_test)

Scalar subquery yang memiliki item terkait. Contoh:

Efisien: select * from tb1 a where id >
        (select id from tb2 b where b.date_test<'2015-12-02')
Tidak efisien: select * from tb1 a where id >
        (select id from tb2 b where a.name=b.name 
                                    and b.date_test<'2015-12-02')

Subquery yang item terkaitnya melampaui tingkat korelasi. Contoh:
- Pernyataan SQL dengan beberapa tingkat korelasi. Item terkait di setiap subquery hanya berkorelasi dengan tingkat atas. Pernyataan ini efisien.
```
Efisien: select * from tb1 a where id in(select id from tb2 b 
        where a.name=b.name and 
        exists (select name from tb3 c where b.address=c.address))  
```
- Pernyataan SQL dengan beberapa tingkat korelasi. Item terkait dari subquery di tabel c berkorelasi dengan kolom di tabel a. Pernyataan ini tidak efisien.
```
Tidak efisien: select * from tb1 a where id in(select id from tb2 b 
        where a.name=b.name and 
        exists (select name from tb3 c where a.address=c.address)) 
```
Catatan Pada contoh sebelumnya, baik tabel a dan tabel b, maupun tabel b dan tabel c termasuk dalam tingkat korelasi yang sama. Korelasi antara tabel a dan tabel c melampaui tingkat korelasi.
Subquery yang berisi klausa GROUP BY. Pastikan item terkait berkorelasi dengan kolom pengelompokan. Contoh:
- Subquery SQL berisi fungsi agregat dan item terkait. Item terkait b.pk berkorelasi dengan kolom pengelompokan pk. Pernyataan ini efisien.
```
Efisien: select * from tb1 a where exists 
    (select pk from tb2 b 
                where a.pk=b.pk and  b.date_test='2003-04-05' 
                group by pk);
```
- Subquery SQL berisi fungsi agregat dan item terkait. Item terkait b.date_test tidak berkorelasi dengan kolom pengelompokan pk. Pernyataan ini tidak efisien.
```
Tidak efisien: select * from tb1 a where exists 
    (select pk from tb2 b 
                where a.date_test=b.date_test and b.date_test='2003-04-05' 
                group by pk);
```

Subquery yang didukung

PolarDB-X 1.0 mendukung jenis-jenis subquery berikut:

Perbandingan menggunakan subquery
Perbandingan menggunakan subquery adalah subquery yang menggunakan operator perbandingan. Subquery ini sering digunakan.
- Sintaks:
```
non_subquery_operand comparison_operator (subquery)
comparison_operator: = > < >= <= <> ! =  <=> like        
```
- Contoh:
```
select * from tb1 WHERE 'a' = (SELECT column1 FROM t1)  
```
  Catatan Subquery hanya dapat ditempatkan di sebelah kanan operator perbandingan.
Subquery dengan ANY, ALL, IN/NOT IN, dan EXISTS/NOT EXISTS
- Sintaks:
```
operand comparison_operator ANY (subquery)
operand comparison_operator ALL (subquery)
operand IN (subquery)
operand NOT IN (subquery)
operand EXISTS (subquery)
operand NOT EXISTS (subquery)

comparison_operator:=  >  <  >=  <=  <>  ! =
```
- Contoh
  - ANY: Jika ada baris apa pun yang dikembalikan oleh subquery memenuhi ekspresi sebelum ANY, TRUE akan dikembalikan. Jika tidak, FALSE akan dikembalikan.
  - ALL: Jika semua baris yang dikembalikan oleh subquery memenuhi ekspresi sebelum ALL, TRUE akan dikembalikan. Jika tidak, FALSE akan dikembalikan.
  - IN: Jika IN digunakan sebelum subquery, IN setara dengan =ANY. Contoh:
```
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);
```
  - NOT IN: Jika NOT IN digunakan sebelum subquery, NOT IN setara dengan <>ALL. Contoh:
```
SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);   
```
  - EXISTS: Jika subquery mengembalikan baris apa pun, TRUE akan dikembalikan. Jika tidak, FALSE akan dikembalikan. Contoh:
```
SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);
```
    Catatan Jika subquery berisi baris apa pun, kondisi WHERE akan mengembalikan TRUE meskipun subquery hanya berisi baris NULL.
  - NOT EXISTS: Jika subquery mengembalikan baris apa pun, FALSE akan dikembalikan. Jika tidak, TRUE akan dikembalikan.
ROW subquery
- ROW subquery mendukung operator perbandingan berikut:
```
comparison_operator: =  >  <  >=  <=  <>  ! =  <=>     
```
- Contoh:
```
SELECT * FROM t1
  WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
SELECT * FROM t1
  WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);  
```
  Kedua pernyataan SQL di atas setara. Baris data di tabel t1 hanya akan dikembalikan jika kondisi berikut terpenuhi:
  - Subquery (SELECT col3, col4 FROM t2 WHERE id=10) hanya mengembalikan satu baris. Kesalahan dilaporkan jika beberapa baris dikembalikan.
  - col3 dan col4 yang dikembalikan oleh subquery sama dengan col1 dan col2 di tabel utama.
Subquery terkait
Subquery terkait adalah subquery yang berisi referensi ke tabel luar dalam query luar. Contoh:
```
SELECT * FROM t1
  WHERE column1 = ANY (SELECT column1 FROM t2
                       WHERE t2.column2 = t1.column2);
```
Pada contoh tersebut, subquery tidak berisi tabel t1 dan kolom column2-nya. Dalam hal ini, subquery menemukan tabel di query luar.
Tabel turunan (subquery dalam klausa FROM)
Tabel turunan adalah subquery dalam klausa FROM.
- Sintaks:
```
SELECT ... FROM (subquery) [AS] tbl_name ...
```
- Contoh
  1. Siapkan data:
    Eksekusi pernyataan berikut untuk membuat tabel t1:
```
CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);
```
    Eksekusi pernyataan berikut. Hasil query adalah 2, '2', 4.0.
```
SELECT sb1,sb2,sb3
  FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
  WHERE sb1 > 1;
```
  2. Kueri data: Kueri nilai rata-rata data yang dikelompokkan yang diproses oleh fungsi SUM.
    Jika Anda mengeksekusi pernyataan SQL berikut, kesalahan dilaporkan dan tidak ada hasil yang dikembalikan.
```
SELECT AVG(SUM(s1)) FROM t1 GROUP BY s1;
```
    Anda dapat mengeksekusi pernyataan berikut yang berisi tabel turunan. Hasil query adalah 1.5000.
```
SELECT AVG(sum_s1)
  FROM (SELECT SUM(s1) AS sum_s1
        FROM t1 GROUP BY s1) AS t1;
```
    Catatan
    Tabel turunan harus memiliki alias, seperti t1 dalam pernyataan sebelumnya.
    Tabel turunan dapat mengembalikan skalar, kolom, baris, atau tabel.
    Tabel turunan tidak boleh menjadi subquery terkait. Tabel turunan tidak boleh berisi referensi ke tabel luar dalam query luar.