Tablestore:Pernyataan DQL

Sintaksis

SELECT
    [ALL | DISTINCT | DISTINCTROW]
    select_expr [, select_expr] ...
    [FROM table_references | join_expr]
    [WHERE where_condition]
    [GROUP BY groupby_condition]
    [HAVING having_condition]
    [ORDER BY order_condition]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

Parameter

Contoh

Kueri semua data dari exampletable, mengembalikan maksimal 20 baris:

SELECT * FROM exampletable LIMIT 20;

Kueri dengan kondisi dan pengurutan:

SELECT pk, col_long, col_keyword FROM exampletable WHERE col_long > 100 ORDER BY col_long DESC LIMIT 10;

Deduplikasi hasil:

SELECT DISTINCT col_keyword FROM exampletable;

Kelompokkan dan hitung:

SELECT col_keyword, COUNT(*) AS cnt FROM exampletable GROUP BY col_keyword HAVING cnt > 1;

Paginasi hasil (lewati 10 baris pertama dan kembalikan 5 baris):

SELECT * FROM exampletable LIMIT 10, 5;

Contoh

SELECT COUNT(*) FROM exampletable;
SELECT SUM(col_long), AVG(col_long) FROM exampletable;
SELECT col_keyword, COUNT(*) AS cnt, MAX(col_long) FROM exampletable GROUP BY col_keyword;

Sintaksis

table_references join_type table_references [ ON join_condition | USING ( join_column [, ...] ) ]

table_references : {
  table_name [ [ AS ] alias_name ]
  | select_statement
}

join_type : {
  [ INNER ] JOIN
  | LEFT [ OUTER ] JOIN
  | RIGHT [ OUTER ] JOIN
  | CROSS JOIN
}

Parameter

SELECT * FROM t1 JOIN t2 ON t1.col_a = t2.col_a AND t1.col_b = t2.col_b;
        SELECT * FROM t1 JOIN t2 USING (col_a, col_b);

Algoritma join

Tablestore menggunakan INDEX JOIN secara default dan beralih ke HASH JOIN ketika kolom join pada tabel kanan tidak memenuhi kondisi indeks.

Contoh

Asumsikan dua tabel, orders dan customers:

-- orders table
+----------+-------------+------------+--------------+
| order_id | customer_id | order_date | order_amount |
+----------+-------------+------------+--------------+
|     1001 |           1 | 2023-01-01 |           50 |
|     1002 |           2 | 2023-01-02 |           80 |
|     1003 |           3 | 2023-01-03 |          180 |
|     1004 |           4 | 2023-01-04 |          220 |
|     1005 |           6 | 2023-01-05 |          250 |
+----------+-------------+------------+--------------+

-- customers table
+-------------+---------------+----------------+
| customer_id | customer_name | customer_phone |
+-------------+---------------+----------------+
|           1 |         Alice |    11111111111 |
|           2 |           Bob |    22222222222 |
|           3 |         Carol |    33333333333 |
|           4 |         David |    44444444444 |
|           5 |           Eve |    55555555555 |
+-------------+---------------+----------------+

INNER JOIN hanya mengembalikan baris di mana customer_id cocok di kedua tabel. Pesanan 1005 (customer_id=6) dikecualikan karena tidak ada baris yang cocok di tabel kanan.

SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;
-- Equivalent:
SELECT * FROM orders JOIN customers USING(customer_id);

LEFT JOIN mengembalikan semua baris dari tabel kiri (orders). Kolom dari tabel kanan diisi dengan NULL jika tidak ada kecocokan.

SELECT * FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id;

CROSS JOIN mengembalikan Produk Kartesius dari kedua tabel.

SELECT * FROM orders CROSS JOIN customers;