WHERE

WHERE 句を使用すると、SELECT の FROM 句から取得されるデータをフィルタリングできます。 WHERE 句がある場合は、その後に UInt8 型の式が続く必要があります。この式が 0 と評価される行は、それ以降の変換処理や結果から除外されます。 WHERE 句に続く式では、多くの場合、比較や論理演算子、あるいは多数の通常の関数のいずれかが使用されます。基盤となるテーブルエンジンが対応している場合、WHERE 式は索引やパーティションプルーニングを利用できるように評価されます。

PREWHEREPREWHERE と呼ばれるフィルタリング最適化もあります。 Prewhere は、フィルタリングをより効率的に適用するための最適化です。 PREWHERE 句が明示的に指定されていない場合でも、これはデフォルトで有効になっています。

`NULL` の判定

値が NULL かどうかを調べる必要がある場合は、次を使用します。

IS NULL または isNull
IS NOT NULL または isNotNull

それ以外の方法では、NULL を含む式は決して条件を通過しません。

論理演算子でデータを絞り込む

複数の条件を組み合わせるには、WHERE 句とあわせて次の論理関数を使用できます。

and() または AND
not() または NOT
or() または NOT
xor()

条件として UInt8 カラムを使用する

ClickHouse では、UInt8 カラムをブール条件として直接使用でき、0 は false、0 以外の値 (通常は 1) は true を表します。その例を以下のセクションに示します。

比較演算子の使用

次の比較演算子を使用できます。

演算子	関数	説明	例
`a = b`	`equals(a, b)`	等しい	`price = 100`
`a == b`	`equals(a, b)`	等しい (別の構文)	`price == 100`
`a != b`	`notEquals(a, b)`	等しくない	`category != 'Electronics'`
`a <> b`	`notEquals(a, b)`	等しくない (別の構文)	`category <> 'Electronics'`
`a < b`	`less(a, b)`	より小さい	`price < 200`
`a <= b`	`lessOrEquals(a, b)`	以下	`price <= 200`
`a > b`	`greater(a, b)`	大なり	`price > 500`
`a >= b`	`greaterOrEquals(a, b)`	以上	`price >= 500`
`a LIKE s`	`like(a, b)`	パターンマッチング (大文字と小文字を区別)	`name LIKE '%top%'`
`a NOT LIKE s`	`notLike(a, b)`	パターンに一致しない (大文字と小文字を区別)	`name NOT LIKE '%top%'`
`a ILIKE s`	`ilike(a, b)`	パターンマッチング (大文字と小文字を区別しない)	`name ILIKE '%LAPTOP%'`
`a SIMILAR TO s`	`similarTo(a, b)`	SQL標準に準拠したパターンマッチング (大文字と小文字を区別)	`name SIMILAR TO 'Lap(top)?'`
`a NOT SIMILAR TO s`	`notSimilarTo(a, b)`	SQL標準に準拠したパターンに一致しない (大文字と小文字を区別)	`name NOT SIMILAR TO 'Lap(top)?'`
`a BETWEEN b AND c`	`a >= b AND a <= c`	範囲チェック (両端を含む)	`price BETWEEN 100 AND 500`
`a NOT BETWEEN b AND c`	`a < b OR a > c`	範囲外のチェック	`price NOT BETWEEN 100 AND 500`

パターンマッチングと条件式

比較演算子に加えて、WHERE句ではパターンマッチングと条件式も使用できます。

機能	構文	大文字と小文字の区別	パフォーマンス	適した用途
`LIKE`	`col LIKE '%pattern%'`	はい	高速	大文字と小文字を区別するパターンマッチング
`ILIKE`	`col ILIKE '%pattern%'`	いいえ	低速	大文字と小文字を区別しない検索
`SIMILAR TO`	`col SIMILAR TO 'p[ab]+%'`	はい	低速	正規表現のメタ文字を使用するSQL標準のパターン
`if()`	`if(cond, a, b)`	該当なし	高速	単純な二者択一条件
`multiIf()`	`multiIf(c1, r1, c2, r2, def)`	該当なし	高速	複数の条件
`CASE`	`CASE WHEN ... THEN ... END`	該当なし	高速	SQL標準の条件ロジック

使用例については、「パターンマッチングと条件式」を参照してください。

リテラル、カラム、またはサブクエリを含む式

WHERE 句の後に続く式には、リテラル、カラム、またはサブクエリを含めることもできます。サブクエリは、条件で使用される値を返す、ネストされた SELECT ステートメントです。

Type	Definition	Evaluation	Performance	Example
リテラル	固定の定数値	クエリ記述時	最速	`WHERE price > 100`
カラム	テーブルデータへの参照	行ごと	高速	`WHERE price > cost`
サブクエリ	ネストされた `SELECT`	クエリ実行時	状況による	`WHERE id IN (SELECT ...)`

複雑な条件では、リテラル、カラム、サブクエリを組み合わせて使用できます。

-- Literal + Column
WHERE price > 100 AND category = 'Electronics'

-- Column + Subquery
WHERE price > (SELECT AVG(price) FROM products) AND in_stock = true

-- Literal + Column + Subquery
WHERE category = 'Electronics'
  AND price < 500
  AND id IN (SELECT product_id FROM bestsellers)

-- All three with logical operators
WHERE (price > 100 OR category IN (SELECT category FROM featured))
  AND in_stock = true
  AND name LIKE '%Special%'

例

`NULL` の判定

NULL 値を含むクエリ:

CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE=MergeTree() ORDER BY x;
INSERT INTO t_null VALUES (1, NULL), (2, 3);

SELECT * FROM t_null WHERE y IS NULL;
SELECT * FROM t_null WHERE y != 0;

┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
└───┴──────┘
┌─x─┬─y─┐
│ 2 │ 3 │
└───┴───┘

論理演算子を使用したデータのフィルタリング

以下のテーブルとデータがあるとします。

CREATE TABLE products (
    id UInt32,
    name String,
    price Float32,
    category String,
    in_stock Bool
) ENGINE = MergeTree()
ORDER BY id;

INSERT INTO products VALUES
(1, 'Laptop', 999.99, 'Electronics', true),
(2, 'Mouse', 25.50, 'Electronics', true),
(3, 'Desk', 299.00, 'Furniture', false),
(4, 'Chair', 150.00, 'Furniture', true),
(5, 'Monitor', 350.00, 'Electronics', true),
(6, 'Lamp', 45.00, 'Furniture', false);

1. AND - 両方の条件を満たす必要があります:

SELECT * FROM products
WHERE category = 'Electronics' AND price < 500;

   ┌─id─┬─name────┬─price─┬─category────┬─in_stock─┐
1. │  2 │ Mouse   │  25.5 │ Electronics │ true     │
2. │  5 │ Monitor │   350 │ Electronics │ true     │
   └────┴─────────┴───────┴─────────────┴──────────┘

2. OR - 少なくとも1つの条件が真である必要があります:

SELECT * FROM products
WHERE category = 'Furniture' OR price > 500;

   ┌─id─┬─name───┬──price─┬─category────┬─in_stock─┐
1. │  1 │ Laptop │ 999.99 │ Electronics │ true     │
2. │  3 │ Desk   │    299 │ Furniture   │ false    │
3. │  4 │ Chair  │    150 │ Furniture   │ true     │
4. │  6 │ Lamp   │     45 │ Furniture   │ false    │
   └────┴────────┴────────┴─────────────┴──────────┘

3. NOT - 条件を否定する:

SELECT * FROM products
WHERE NOT in_stock;

   ┌─id─┬─name─┬─price─┬─category──┬─in_stock─┐
1. │  3 │ Desk │   299 │ Furniture │ false    │
2. │  6 │ Lamp │    45 │ Furniture │ false    │
   └────┴──────┴───────┴───────────┴──────────┘

4. XOR - ちょうど1つの条件だけが真である必要があります (両方は不可) ：

SELECT *
FROM products
WHERE xor(price > 200, category = 'Electronics')

   ┌─id─┬─name──┬─price─┬─category────┬─in_stock─┐
1. │  2 │ Mouse │  25.5 │ Electronics │ true     │
2. │  3 │ Desk  │   299 │ Furniture   │ false    │
   └────┴───────┴───────┴─────────────┴──────────┘

5. 複数の演算子の組み合わせ:

SELECT * FROM products
WHERE (category = 'Electronics' OR category = 'Furniture')
  AND in_stock = true
  AND price < 400;

   ┌─id─┬─name────┬─price─┬─category────┬─in_stock─┐
1. │  2 │ Mouse   │  25.5 │ Electronics │ true     │
2. │  4 │ Chair   │   150 │ Furniture   │ true     │
3. │  5 │ Monitor │   350 │ Electronics │ true     │
   └────┴─────────┴───────┴─────────────┴──────────┘

6. 関数構文の使用:

SELECT * FROM products
WHERE and(or(category = 'Electronics', price > 100), in_stock);

   ┌─id─┬─name────┬──price─┬─category────┬─in_stock─┐
1. │  1 │ Laptop  │ 999.99 │ Electronics │ true     │
2. │  2 │ Mouse   │   25.5 │ Electronics │ true     │
3. │  4 │ Chair   │    150 │ Furniture   │ true     │
4. │  5 │ Monitor │    350 │ Electronics │ true     │
   └────┴─────────┴────────┴─────────────┴──────────┘

SQLのキーワード構文 (AND, OR, NOT, XOR) は一般に読みやすいですが、複雑な式や動的なクエリを組み立てる場合には、関数構文が便利なこともあります。

UInt8カラムを条件として使用する

前の例のテーブルでは、カラム名をそのまま条件として使用できます。

SELECT * FROM products
WHERE in_stock

   ┌─id─┬─name────┬──price─┬─category────┬─in_stock─┐
1. │  1 │ Laptop  │ 999.99 │ Electronics │ true     │
2. │  2 │ Mouse   │   25.5 │ Electronics │ true     │
3. │  4 │ Chair   │    150 │ Furniture   │ true     │
4. │  5 │ Monitor │    350 │ Electronics │ true     │
   └────┴─────────┴────────┴─────────────┴──────────┘

比較演算子の使用

以下の例では、上記の例で使用したテーブルとデータを用います。簡潔にするため、結果は省略しています。 1. true との明示的な等値比較 (= 1 または = true) :

SELECT * FROM products
WHERE in_stock = true;
-- または
WHERE in_stock = 1;

2. false との明示的な等価比較 (= 0 または = false) :

SELECT * FROM products
WHERE in_stock = false;
-- または
WHERE in_stock = 0;

3. 不等比較 (!= 0 または != false) :

SELECT * FROM products
WHERE in_stock != false;
-- または
WHERE in_stock != 0;

4. 大なり:

SELECT * FROM products
WHERE in_stock > 0;

5. 以下:

SELECT * FROM products
WHERE in_stock <= 0;

6. 他の条件と組み合わせる:

SELECT * FROM products
WHERE in_stock AND price < 400;

7. IN 演算子の使用: 以下の例では、(1, true) は Tuple 型です。

SELECT * FROM products
WHERE in_stock IN (1, true);

これには、Arrayを使うこともできます：

SELECT * FROM products
WHERE in_stock IN [1, true];

8. 比較スタイルの混用:

SELECT * FROM products
WHERE category = 'Electronics' AND in_stock = true;

パターンマッチングと条件式

以下の例では、上記の例で使用したテーブルとデータを使います。簡潔さのため、結果は省略します。

LIKEの例

-- 名前に 'o' を含む商品を検索
SELECT * FROM products WHERE name LIKE '%o%';
-- 結果: Laptop, Monitor

-- 'L' で始まる商品を検索
SELECT * FROM products WHERE name LIKE 'L%';
-- 結果: Laptop, Lamp

-- ちょうど4文字の商品を検索
SELECT * FROM products WHERE name LIKE '____';
-- 結果: Desk, Lamp

ILIKEの例

-- 大文字と小文字を区別しない 'LAPTOP' の検索
SELECT * FROM products WHERE name ILIKE '%laptop%';
-- 結果: Laptop

-- 大文字と小文字を区別しないプレフィックス一致
SELECT * FROM products WHERE name ILIKE 'l%';
-- 結果: Laptop, Lamp

SIMILAR TO の例

LIKE とは異なり、SIMILAR TO のパターンでは正規表現のメタ文字 |、*、+、?、{、}、(、)、[、] も使用できます。また、LIKE と同様に、パターンは値全体と一致する必要があります。

-- Alternation: either of two names
SELECT * FROM products WHERE name SIMILAR TO 'Desk|Lamp';
-- Result: Desk, Lamp

-- A bracket expression combined with the LIKE wildcard `%`
SELECT * FROM products WHERE name SIMILAR TO '[LM]%';
-- Result: Laptop, Mouse, Monitor, Lamp

-- Repetition: a name of five or more characters
SELECT * FROM products WHERE name SIMILAR TO '_{5,}';
-- Result: Laptop, Mouse, Chair, Monitor

-- Negation
SELECT * FROM products WHERE name NOT SIMILAR TO '(Desk|Lamp)';
-- Result: Laptop, Mouse, Chair, Monitor

ESCAPE 句を使用すると、任意のエスケープ文字を指定できます。この句は LIKE、ILIKE、NOT LIKE、NOT ILIKE、NOT SIMILAR TO でも使用できます：

-- `#%` denotes a literal `%`, so this matches names containing a percent sign
SELECT * FROM products WHERE name SIMILAR TO '%#%%' ESCAPE '#';
-- Result: (none)

パターンの文法の詳細については、SIMILAR TO 演算子のドキュメントを参照してください。

IFの例

-- カテゴリ別の価格しきい値
SELECT * FROM products
WHERE if(category = 'Electronics', price < 500, price < 200);
-- 結果: Mouse, Chair, Monitor
-- (Electronicsは$500未満、またはFurnitureは$200未満)

-- 在庫状況に基づくフィルター
SELECT * FROM products
WHERE if(in_stock, price > 100, true);
-- 結果: Laptop, Chair, Monitor, Desk, Lamp
-- ($100超の在庫あり商品、または在庫切れ商品すべて)

multiIf の使用例

-- カテゴリに基づく複数の条件
SELECT * FROM products
WHERE multiIf(
    category = 'Electronics', price < 600,
    category = 'Furniture', in_stock = true,
    false
);
-- 結果: Mouse, Monitor, Chair
-- (Electronics < $600 または在庫ありの Furniture)

-- 段階的なフィルタリング
SELECT * FROM products
WHERE multiIf(
    price > 500, category = 'Electronics',
    price > 100, in_stock = true,
    true
);
-- 結果: Laptop, Chair, Monitor, Lamp

CASE の例

単純CASE:

-- Different rules per category
SELECT * FROM products
WHERE CASE category
    WHEN 'Electronics' THEN price < 400
    WHEN 'Furniture' THEN in_stock = true
    ELSE false
END;
-- Result: Mouse, Monitor, Chair

検索CASE:

-- Price-based tiered logic
SELECT * FROM products
WHERE CASE
    WHEN price > 500 THEN in_stock = true
    WHEN price > 100 THEN category = 'Electronics'
    ELSE true
END;
-- Result: Laptop, Monitor, Mouse, Lamp

SQLリファレンス

データ型

エンジン

関数

フォーマット

設定

システムテーブル

データレイク

`NULL` の判定

論理演算子でデータを絞り込む

条件として UInt8 カラムを使用する

比較演算子の使用

パターンマッチングと条件式

リテラル、カラム、またはサブクエリを含む式

例

`NULL` の判定

論理演算子を使用したデータのフィルタリング

UInt8カラムを条件として使用する

比較演算子の使用

パターンマッチングと条件式

LIKEの例

ILIKEの例

SIMILAR TO の例

IFの例

multiIf の使用例

CASE の例

​NULL の判定

​論理演算子でデータを絞り込む

​条件として UInt8 カラムを使用する

​比較演算子の使用

​パターンマッチングと条件式

​リテラル、カラム、またはサブクエリを含む式

​例

​NULL の判定

​論理演算子を使用したデータのフィルタリング

​UInt8カラムを条件として使用する

​比較演算子の使用

​パターンマッチングと条件式

​LIKEの例

​ILIKEの例

​SIMILAR TO の例

​IFの例

​multiIf の使用例

​CASE の例

`NULL` の判定

論理演算子でデータを絞り込む

条件として UInt8 カラムを使用する

比較演算子の使用

パターンマッチングと条件式

リテラル、カラム、またはサブクエリを含む式

例

`NULL` の判定

論理演算子を使用したデータのフィルタリング

UInt8カラムを条件として使用する

比較演算子の使用

パターンマッチングと条件式

LIKEの例

ILIKEの例

SIMILAR TO の例

IFの例

multiIf の使用例

CASE の例