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() または OR
xor()

条件としての UInt8 カラムの使用

ClickHouse では、UInt8 カラムをブール条件として直接使用でき、0 は false、それ以外の非ゼロ値 (一般的には 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 ...)`

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

-- 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 - どちらか一方の条件だけが真でなければならない (両方が真であってはならない) ：

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;
-- or
WHERE in_stock = 1;

2. false と明示的に等値比較する (= 0 または = false) :

SELECT * FROM products
WHERE in_stock = false;
-- or
WHERE in_stock = 0;

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

SELECT * FROM products
WHERE in_stock != false;
-- or
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) はタプルです。

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 の使用例

-- Find products with 'o' in the name
SELECT * FROM products WHERE name LIKE '%o%';
-- Result: Laptop, Monitor

-- Find products starting with 'L'
SELECT * FROM products WHERE name LIKE 'L%';
-- Result: Laptop, Lamp

-- Find products with exactly 4 characters
SELECT * FROM products WHERE name LIKE '____';
-- Result: Desk, Lamp

ILIKE の使用例

-- Case-insensitive search for 'LAPTOP'
SELECT * FROM products WHERE name ILIKE '%laptop%';
-- Result: Laptop

-- Case-insensitive prefix match
SELECT * FROM products WHERE name ILIKE 'l%';
-- Result: Laptop, Lamp

IF の使用例

-- Different price thresholds by category
SELECT * FROM products
WHERE if(category = 'Electronics', price < 500, price < 200);
-- Result: Mouse, Chair, Monitor
-- (Electronics under $500 OR Furniture under $200)

-- Filter based on stock status
SELECT * FROM products
WHERE if(in_stock, price > 100, true);
-- Result: Laptop, Chair, Monitor, Desk, Lamp
-- (In stock items over $100 OR all out-of-stock items)

multiIf の使用例

-- Multiple category-based conditions
SELECT * FROM products
WHERE multiIf(
    category = 'Electronics', price < 600,
    category = 'Furniture', in_stock = true,
    false
);
-- Result: Mouse, Monitor, Chair
-- (Electronics < $600 OR in-stock Furniture)

-- Tiered filtering
SELECT * FROM products
WHERE multiIf(
    price > 500, category = 'Electronics',
    price > 100, in_stock = true,
    true
);
-- Result: 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

NULL の判定​

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

条件としての UInt8 カラムの使用​

比較演算子の使用​

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

リテラル、カラム、サブクエリを用いた式​

例​

NULL のテスト​

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

条件として UInt8 カラムを使う​

比較演算子の使用​

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

LIKE の使用例​

ILIKE の使用例​

IF の使用例​

multiIf の使用例​

CASE の例​

`NULL` の判定

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

条件としての UInt8 カラムの使用

比較演算子の使用

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

リテラル、カラム、サブクエリを用いた式

例

`NULL` のテスト

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

条件として UInt8 カラムを使う

比較演算子の使用

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

LIKE の使用例

ILIKE の使用例

IF の使用例

multiIf の使用例

CASE の例