メインコンテンツまでスキップ

SELECT

SELECT

説明​

1つまたは複数のテーブル、ビュー、またはマテリアライズドビューからデータをクエリします。SELECT文は通常、以下の節で構成されています。

SELECTは独立したステートメントまたは他のステートメントにネストされた節として動作することができます。SELECT節の出力は他のステートメントの入力として使用することができます。

StarRocksのクエリ文は基本的にSQL92の標準に準拠しています。サポートされているSELECTの使用方法について簡単に説明します。

注意

StarRocksの内部テーブルのテーブル、ビュー、またはマテリアライズドビューからデータをクエリするには、これらのオブジェクトに対するSELECT権限が必要です。外部データソースのテーブル、ビュー、またはマテリアライズドビューからデータをクエリするには、対応する外部カタログに対するUSAGE権限が必要です。

WITH​

SELECT文の前に追加できる節で、SELECT内で複数回参照される複雑な式に名前を付けるためのエイリアスを定義します。

CRATE VIEWと似ていますが、節で定義されたテーブル名や列名はクエリ終了後に保持されず、実際のテーブルまたはビューの名前とは競合しません。

WITH節を使用することの利点は次のとおりです。

重複を減らし、クエリ内の重複を減らして、便利でメンテナンスしやすくします。

最も複雑なクエリの一部を別のブロックに抽象化することにより、SQLコードがより読みやすく理解しやすくなります。

例:

-- 外部レベルとして1つのサブクエリを定義し、
-- 初期ステージの UNION ALL クエリの一部としてもう1つのサブクエリを定義する。

with t1 as (select 1),t2 as (select 2)
select * from t1 union all select * from t2;

結合​

結合操作は、2つ以上のテーブルからデータを結合し、それらの一部の列からなる結果セットを返します。

StarRocksでは、セルフ結合、クロス結合、インナー結合、アウター結合、セミ結合、アンチ結合をサポートしています。アウター結合には、左結合、右結合、およびフル結合が含まれます。

構文:

SELECT select_list FROM
table_or_subquery1 [INNER] JOIN table_or_subquery2 |
table_or_subquery1 {LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]} JOIN table_or_subquery2 |
table_or_subquery1 {LEFT | RIGHT} SEMI JOIN table_or_subquery2 |
table_or_subquery1 {LEFT | RIGHT} ANTI JOIN table_or_subquery2 |
[ ON col1 = col2 [AND col3 = col4 ...] |
USING (col1 [, col2 ...]) ]
[other_join_clause ...]
[ WHERE where_clauses ]
SELECT select_list FROM
table_or_subquery1, table_or_subquery2 [, table_or_subquery3 ...]
[other_join_clause ...]
WHERE
col1 = col2 [AND col3 = col4 ...]
SELECT select_list FROM
table_or_subquery1 CROSS JOIN table_or_subquery2
[other_join_clause ...]
[ WHERE where_clauses ]

セルフ結合​

StarRocksはセルフ結合をサポートしており、セルフ結合とセルフ結合の結合です。たとえば、同じテーブルの異なる列を結合します。

セルフ結合を特定する特別な構文は実際にはありません。セルフ結合の結合の両側の条件は同じテーブルから来ます。

それらには異なるエイリアスを割り当てる必要があります。

例:

SELECT lhs.id, rhs.parent, lhs.c1, rhs.c2 FROM tree_data lhs, tree_data rhs WHERE lhs.id = rhs.parent;

クロス結合​

クロス結合は非常に多くの結果を生成するため、クロス結合を使用する場合は注意が必要です。

クロス結合を使用する必要がある場合でも、フィルタ条件を使用して返される結果が少なくなるようにする必要があります。例:

SELECT * FROM t1, t2;

SELECT * FROM t1 CROSS JOIN t2;

インナー結合​

インナー結合は、最もよく知られていて一般的に使用される結合です。2つの類似したテーブルから要求した列の結果を返しますが、両方のテーブルの列に同じ値が含まれる場合に結合されます。

両方のテーブルの列名が同じ場合は、完全な名前（テーブル名.列名の形式）を使用するか、列名にエイリアスを付ける必要があります。

例:

次の3つのクエリは同等です。

SELECT t1.id, c1, c2 FROM t1, t2 WHERE t1.id = t2.id;

SELECT t1.id, c1, c2 FROM t1 JOIN t2 ON t1.id = t2.id;

SELECT t1.id, c1, c2 FROM t1 INNER JOIN t2 ON t1.id = t2.id;

アウター結合​

アウター結合は、左のテーブルまたは右のテーブルまたは両方の行を返します。別のテーブルに一致するデータがない場合、NULLに設定されます。例:

SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id;

SELECT * FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id;

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id;

等しい結合と等しくない結合​

通常、ユーザーは最も等しい結合を使用し、結合条件の演算子が等号であることを要求します。

等しくない結合は結合条件が!=、等号である場合に使用できます。等しくない結合は大量の結果を生成し、計算中にメモリ制限を超えることがあります。

注意して使用してください。等しくない結合は内部結合のみをサポートします。例:

SELECT t1.id, c1, c2 FROM t1 INNER JOIN t2 ON t1.id = t2.id;

SELECT t1.id, c1, c2 FROM t1 INNER JOIN t2 ON t1.id > t2.id;

セミ結合​

左セミ結合は、右側のテーブルと一致する左側のテーブルの行のみを返します。これは左側のテーブルの行は最大でも1回返されます。右セミ結合は類似して動作しますが、返されるデータは右側のテーブルです。例:

SELECT t1.c1, t1.c2, t1.c2 FROM t1 LEFT SEMI JOIN t2 ON t1.id = t2.id;

アンチ結合​

アンチ結合（反結合）は左側のテーブルから右側のテーブルと一致しない行のみを返します。

右アンチ結合はこれとは逆の比較を行い、左側のテーブルと一致しない行だけを返します。例:

SELECT t1.c1, t1.c2, t1.c2 FROM t1 LEFT ANTI JOIN t2 ON t1.id = t2.id;

等価結合と等価でない結合​

StarRocksでサポートされているさまざまな結合は、結合条件で指定された等価結合と非等価結合に分類することができます。

等価結合

セルフ結合、クロス結合、インナー結合、アウター結合、セミ結合、アンチ結合

等価でない結合

クロス結合、インナー結合、左セミ結合、左アンチ結合、アウター結合

  • 等価結合等価結合は、結合項を=演算子で結合する結合条件を使用します。例: a JOIN b ON a.id = b.id。
  • 等価でない結合等価でない結合は、<、<=、>、>=、<>などの比較演算子で結合項を結合する結合条件を使用します。例: a JOIN b ON a.id < b.id。等価でない結合は等価結合よりも処理が遅くなります。非等価結合を使用する際は注意が必要です。次の2つの例は、非等価結合の実行方法を示しています: 
    SELECT t1.id, c1, c2 
    FROM t1 
    INNER JOIN t2 ON t1.id < t2.id;
      
    SELECT t1.id, c1, c2 
    FROM t1 
    LEFT JOIN t2 ON t1.id > t2.id;

ORDER BY​

SELECT文のORDER BY節は、1つまたは複数の列の値を比較して結果セットをソートします。

ORDER BYは時間とリソースを消費する操作です。ソートするために、すべての結果を1つのノードに送信してマージする必要があります。ソートはORDER BYのないクエリよりもメモリリソースを消費します。

したがって、ソートされた結果セットから最初のN個の結果のみが必要な場合は、LIMIT節を使用してメモリ使用量とネットワークオーバーヘッドを削減することができます。LIMIT節が指定されていない場合、デフォルトで最初の65535行が返されます。

構文:

ORDER BY <column_name> 
    [ASC | DESC]
    [NULLS FIRST | NULLS LAST]

ASCは結果を昇順で返すことを示します。DESCは結果を降順で返すことを示します。順序が指定されていない場合、ASC（昇順）がデフォルトです。例:

select * from big_table order by tiny_column, short_column desc;

NULL値のソート順：NULLS FIRSTは、NULL値を非NULL値の前に返します。NULLS LASTは、NULL値を非NULL値の後に返します。

例:

select  *  from  sales_record  order by  employee_id  nulls first;

GROUP BY​

GROUP BY節は通常、COUNT()、SUM()、AVG()、MIN()、MAX()などの集計関数と一緒に使用されます。

GROUP BYで指定された列は集計操作に参加しません。GROUP BY節はHAVING節と組み合わせて使用し、集計関数によって生成された結果をフィルタリングするために使用することができます。

例:

select tiny_column, sum(short_column)
from small_table 
group by tiny_column;
+-------------+---------------------+
| tiny_column |  sum('short_column')|
+-------------+---------------------+
|      1      |        2            |
|      2      |        1            |
+-------------+---------------------+

2 rows in set (0.07 sec)

HAVING​

HAVING節は表の行データをフィルタリングするのではなく、集約関数の結果をフィルタリングします。

一般に、HAVINGは集計関数（COUNT()、SUM()、AVG()、MIN()、MAX()など）とGROUP BY節と一緒に使用されます。

例:

select tiny_column, sum(short_column) 
from small_table 
group by tiny_column 
having sum(short_column) = 1;
+-------------+---------------------+
|tiny_column  | sum('short_column') |
+-------------+---------------------+
|         2   |        1            |
+-------------+---------------------+

1 row in set (0.07 sec)
select tiny_column, sum(short_column) 
from small_table 
group by tiny_column 
having tiny_column > 1;
+-------------+---------------------+
|tiny_column  | sum('short_column') |
+-------------+---------------------+
|      2      |          1          |
+-------------+---------------------+

1 row in set (0.07 sec)

LIMIT​

LIMIT節は返される行数の最大数を制限します。返される行数の最大数の設定は、StarRocksがメモリ使用量を最適化するのに役立ちます。

この節は主に以下のシナリオで使用されます:

ソート済みの結果セットの上位N件の結果を返す。

以下のテーブルに含まれるデータの数が多すぎるか、where節があまりにも多くのデータをフィルタリングしないため、クエリ結果セットのサイズが制限される必要がある。

使用方法: LIMIT節の値は数値リテラル定数である必要があります。

例:

mysql> select tiny_column from small_table limit 1;

+-------------+
|tiny_column  |
+-------------+
|     1       |
+-------------+

1 row in set (0.02 sec)
mysql> select tiny_column from small_table limit 10000;

+-------------+
|tiny_column  |
+-------------+
|      1      |
|      2      |
+-------------+

2 rows in set (0.01 sec)

OFFSET​

OFFSET節は、結果セットが最初のいくつかの行をスキップし、その後の結果を直接返します。

結果セットのデフォルトは行0から開始するため、offset 0とno offsetは同じ結果を返します。

一般的に、OFFSET節はORDER BY節およびLIMIT節と組み合わせて使用する必要があります。

例:

mysql> select varchar_column from big_table order by varchar_column limit 3;

+----------------+
| varchar_column | 
+----------------+
|    beijing     | 
|    chongqing   | 
|    tianjin     | 
+----------------+

3 rows in set (0.02 sec)
mysql> select varchar_column from big_table order by varchar_column limit 1 offset 0;

+----------------+
|varchar_column  |
+----------------+
|     beijing    |
+----------------+

1 row in set (0.01 sec)
mysql> select varchar_column from big_table order by varchar_column limit 1 offset 1;

+----------------+
|varchar_column  |
+----------------+
|    chongqing   | 
+----------------+

1 row in set (0.01 sec)
mysql> select varchar_column from big_table order by varchar_column limit 1 offset 2;

+----------------+
|varchar_column  |
+----------------+
|     tianjin    |     
+----------------+

1 row in set (0.02 sec)

注意: ORDER BYなしでoffset構文を使用することは許可されていますが、この場合、offsetは意味をなしません。

この場合、単にlimit値を取り、offset値は無視されます。したがって、order byなしでoffsetを使用する場合は、limitと一緒に使用することをお勧めします。

UNION​

複数のクエリの結果を結合します。

構文:

query_1 UNION [DISTINCT | ALL] query_2
  • DISTINCT（デフォルト）は重複する行のみを返します。UNIONはUNION DISTINCTと同等です。
  • ALLはすべての行を結合しますが、重複も含まれます。重複の削除はメモリ使用量が多いため、UNION ALLを使用するクエリの方が高速でメモリ使用量が少なくなります。パフォーマンスを向上させるために、UNION ALLを使用してください。

注意

各クエリ文は同じ数の列を返す必要があり、列は互換性のあるデータ型を持っている必要があります。

例:

select1とselect2のテーブルを作成します。

CREATE TABLE select1(
    id          INT,
    price       INT
    )
DISTRIBUTED BY HASH(id);

INSERT INTO select1 VALUES
    (1,2),
    (1,2),
    (2,3),
    (5,6),
    (5,6);

CREATE TABLE select2(
    id          INT,
    price       INT
    )
DISTRIBUTED BY HASH(id);

INSERT INTO select2 VALUES
    (2,3),
    (3,4),
    (5,6),
    (7,8);

例1: 両方のテーブルで一致するすべてのIDを返し、重複を含める。

mysql> (select id from select1) union all (select id from select2) order by id;

+------+
| id   |
+------+
|    1 |
|    1 |
|    2 |
|    2 |
|    3 |
|    5 |
|    5 |
|    5 |
|    7 |
+------+
11 rows in set (0.02 sec)

例2: 両方のテーブルで一致する一意のIDを返す。以下の2つのステートメントは等価です。

mysql> (select id from select1) union (select id from select2) order by id;

+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    5 |
|    7 |
+------+
6 rows in set (0.01 sec)

mysql> (select id from select1) union distinct (select id from select2) order by id;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    5 |
|    7 |
+------+
5 rows in set (0.02 sec)

例3: 両方のテーブルで一致する一意のIDの中で最初の3つを返す。以下の2つのステートメントは等価です。

mysql> (select id from select1) union distinct (select id from select2)
order by id
limit 3;
++------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
4 rows in set (0.11 sec)

mysql> select * from (select id from select1 union distinct select id from select2) as t1
order by id
limit 3;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

INTERSECT​

複数のクエリの結果の共通項、すなわちすべての結果セットに表示される結果を計算します。この節は結果セット内の一意の行のみを返します。ALLキーワードはサポートされていません。

構文:

query_1 INTERSECT [DISTINCT] query_2

注意

  • INTERSECTはINTERSECT DISTINCTと同等です。
  • 各クエリ文は同じ数の列を返し、列は互換性のあるデータ型を持っている必要があります。

例:

UNIONの例で使用される2つのテーブルを使用します。

select1に存在するがselect2に存在しない(id, price)の一意の組み合わせを返します。以下の2つのステートメントは等価です。

mysql> (select id, price from select1) intersect (select id, price from select2)
order by id;

+------+-------+
| id   | price |
+------+-------+
|    2 |     3 |
|    5 |     6 |
+------+-------+

mysql> (select id, price from select1) intersect distinct (select id, price from select2)
order by id;

+------+-------+
| id   | price |
+------+-------+
|    2 |     3 |
|    5 |     6 |
+------+-------+

EXCEPT/MINUS​

左側のクエリの一意な結果で、右側のクエリに存在しない結果を返します。EXCEPTはMINUSと等価です。

構文:

query_1 {EXCEPT | MINUS} [DISTINCT] query_2

注意

  • EXCEPTはEXCEPT DISTINCTと等価です。ALLキーワードはサポートされていません。
  • 各クエリ文は同じ数の列を返し、列は互換性のあるデータ型を持っている必要があります。

例:

UNIONの例で使用される2つのテーブルを使用します。

select1に存在するがselect2に存在しない(id, price)の一意な組み合わせを返します。

mysql> (select id, price from select1) except (select id, price from select2)
order by id;
+------+-------+
| id   | price |
+------+-------+
|    1 |     2 |
+------+-------+

mysql> (select id, price from select1) minus (select id, price from select2)
order by id;
+------+-------+
| id   | price |
+------+-------+
|    1 |     2 |
+------+-------+

DISTINCT​

DISTINCTキーワードは結果セットの重複を削除します。例:

-- 1つの列から一意の値を返します。
select distinct tiny_column from big_table limit 2;

-- 複数の列の一意の組み合わせを返します。
select distinct tiny_column, int_column from big_table limit 2;

DISTINCTは集約関数（通常はカウント関数）と組み合わせて使用することができます。count(distinct)は、1つまたは複数の列の異なる組み合わせの数を計算するために使用されます。

-- 1つの列の一意の値の数を数えます。
select count(distinct tiny_column) from small_table;
+-------------------------------+
| count(DISTINCT 'tiny_column') |
+-------------------------------+
|             2                 |
+-------------------------------+
1 row in set (0.06 sec)
-- 複数の列の一意の組み合わせの数を数えます。
select count(distinct tiny_column, int_column) from big_table limit 2;

StarRocksは、distinctを同時に使用して複数の集計関数をサポートします。

-- 複数の異なる値を個別に数えます。
select count(distinct tiny_column, int_column), count(distinct varchar_column) from big_table;

サブクエリ​

サブクエリには関連するものと非関連するものの2種類があります。

  • 非関連サブクエリは、外部クエリから独立して結果を取得します。
  • 関連付けられたサブクエリは、外部クエリから値を必要とします。

非関連サブクエリ​

非関連サブクエリは[NOT] INおよびEXISTSをサポートしています。

例:

SELECT x FROM t1 WHERE x [NOT] IN (SELECT y FROM t2);

SELECT * FROM t1 WHERE (x,y) [NOT] IN (SELECT x,y FROM t2 LIMIT 2);

SELECT x FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE y = 1);

v3.0以降では、SELECT... FROM... WHERE... [NOT] INのWHERE節で複数のフィールドを指定することができます。たとえば、第2のSELECTステートメントのWHERE句でのWHERE (x,y)のような表記が可能です。

関連サブクエリ​

関連サブクエリは[NOT] INおよび[NOT] EXISTSをサポートしています。

例:

SELECT * FROM t1 WHERE x [NOT] IN (SELECT a FROM t2 WHERE t1.y = t2.b);

SELECT * FROM t1 WHERE [NOT] EXISTS (SELECT a FROM t2 WHERE t1.y = t2.b);

サブクエリはスカラー部分クエリもサポートしています。関連しないスカラー部分クエリ、関連スカラー部分クエリ、および一般的な関数のパラメータとしてのスカラー部分クエリのパラメータのスカラー量子クエリに分類することができます。

例:

  1. '='記号を持った関連しないスカラー量子クエリ。たとえば、給与が最高額の人の情報を出力します。

    SELECT name FROM table WHERE salary = (SELECT MAX(salary) FROM table);

  2. 比較演算子 >, < などを持つ関連しないスカラー量子クエリ。たとえば、平均以上の給与をもらっている人の情報を出力します。

    SELECT name FROM table WHERE salary > (SELECT AVG(salary) FROM table);

  3. 関連スカラー量子クエリ。たとえば、各部門の最高給与情報を出力します。

    SELECT name FROM table a WHERE salary = (SELECT MAX(salary) FROM table b WHERE b.Department= a.Department);

  4. 一般的な関数のパラメータとしてのスカラー量子クエリ。

    SELECT name FROM table WHERE salary = abs((SELECT MAX(salary) FROM table));

WHEREおよび演算子​

SQLの演算子は比較に使用される一連の関数で、主にSELECT文のWHERE節で使用されます。

算術演算子​

算術演算子は通常、左、右、および最も左のオペランドを含む式に現れます。

+および-: ユニットまたは2項演算子として使用されることができます。ユニット演算子として使用される場合、例えば+1、-2.5、または-col_ nameなどです。これは値に+1または-1が乗算されることを意味します。

したがって、セル演算子+は変わらず値を返し、セル演算子-はその値の記号ビットを変更します。

ユーザーは+および-の2つのセル演算子を重ね合わせることができます。たとえば、+5（正の値を返す）、-+2または+2（負の値を返す）ですが、2つの連続した-符号は使用できません。

なぜなら--は次のステートメントのコメントとして解釈されるためです（ユーザーが2つの符号を使用する場合、2つの符号の間にはスペースまたは括弧が必要です。たとえば - (-2) または - -2 となり、実際には + 2 となります）。

バイナリ演算子としての+または-、例えば2+2、3+1.5、またはcol1+col2は、左の値で右の値が減算されることを意味します。左右の値はいずれも数値型である必要があります。

*および/: 乗算および除算を表します。両側のオペランドはデータ型である必要があります。2つの数値が乗算されると小さいオペランドが必要な場合、小さいオペランドが昇格される場合があります（たとえば、SMALLINTからINTまたはBIGINTへ、式の結果は次に大きい型に昇格されます）。

たとえば、TINYINTがINTと乗算される場合、BIGINTタイプの結果が生成されます。2つの数値が掛けられる場合、両方のオペランドと式の結果は精度の損失を避けるためにDOUBLE型として解釈されます。

ユーザーが式の結果を別の型に変換する必要がある場合は、CAST関数を使用して変換する必要があります。

%: モジュロ演算子。左オペランドを右オペランドで割った余りを返します。左右のオペランドはいずれも整数である必要があります。

&、|および^: ビット演算子は、2つのオペランドに対するビットごとのAND、OR、XOR操作の結果を返します。両オペランドは整数型を必要とします。

ビット演算子の2つのオペランドの型が不一致の場合、より小さい型のオペランドは大きい型のオペランドに昇格され、対応するビット演算が行われます。

演算子を複数使用できる場合があります。対応する演算式を丸括弧で囲むことができます。算術演算子には通常、算術演算子としての同じ機能を表す数学関数が存在しません。

たとえば、%演算子を表すMOD()関数はありません。逆に、算術演算子は数学関数に対応する算術演算子を持ちません。たとえば、パワー関数POW()には**の指数演算子の対応する演算子がありません。

ユーザーは、算術演算子を含む式を記述する際に複数の算術演算子を使用することができ、ユーザーは算術式をカッコで囲むことができます。算術演算子は通常数学関数としての同じ機能を表すことはありません。

たとえば、%演算子を表すMOD()関数はありません。逆に、算術演算子は数学関数に対応する算術演算子を持ちません。

注意: ビット演算子の2つのオペランドは整数型である必要がありますが、文字列型とビット型の間での変換はサポートされていません。

BETWEEN演算子​

WHERE節では、式を上限値と下限値の両方と比較する場合があります。式が下限値以上かつ上限値以下であれば、比較の結果はtrueとなります。

構文:

expression BETWEEN lower_bound AND upper_bound

データ型: 通常、式は数値型と評価されますが、他のデータ型にも対応しています。ただし、下限値と上限値の両方が比較可能な文字列であることを確認する必要がある場合は、cast()関数を使用することもできます。

使い方の注意: オペランドが文字列型の場合、大文字で始まる長い文字列は上限値と一致せず、上限値よりも大きいです。例え