WHERE 句

ClickHouse の WHERE 句に関するドキュメント

WHERE 句を使用すると、SELECT の FROM 句から取得されるデータをフィルタリングできます。

WHERE 句がある場合は、その後に UInt8 型の式が続く必要があります。 この式が 0 と評価される行は、それ以降の変換処理や結果から除外されます。

WHERE 句に続く式では、多くの場合、比較 や 論理演算子、あるいは多数の通常の関数のいずれかが使用されます。

基盤となるテーブルエンジンが対応している場合、WHERE 式は索引やパーティションプルーニングを利用できるように評価されます。

PREWHERE

PREWHERE と呼ばれるフィルタリング最適化もあります。 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 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%'	いいえ	低速	大文字と小文字を区別しない検索
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
Literal	固定の定数値	クエリ記述時	最速	WHERE price > 100
Column	テーブルデータへの参照	行ごと	高速	WHERE price > cost
Subquery	ネストされた SELECT	クエリ実行時	状況による	WHERE id IN (SELECT ...)

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

-- リテラル + カラム
WHERE price > 100 AND category = 'Electronics'

-- カラム + サブクエリ
WHERE price > (SELECT AVG(price) FROM products) AND in_stock = true

-- リテラル + カラム + サブクエリ
WHERE category = 'Electronics' 
  AND price < 500
  AND id IN (SELECT product_id FROM bestsellers)

-- 3つすべてを論理演算子で組み合わせた例
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

​

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:

-- カテゴリごとに異なるルール
SELECT * FROM products
WHERE CASE category
    WHEN 'Electronics' THEN price < 400
    WHEN 'Furniture' THEN in_stock = true
    ELSE false
END;
-- 結果: Mouse, Monitor, Chair

検索CASE:

-- 価格ベースの段階的ロジック
SELECT * FROM products
WHERE CASE
    WHEN price > 500 THEN in_stock = true
    WHEN price > 100 THEN category = 'Electronics'
    ELSE true
END;
-- 結果: Laptop, Monitor, Mouse, Lamp