EXISTS / IN / NOT EXISTS(サブクエリ)

Oracle

-- Customers with orders (EXISTS)
SELECT name FROM customer m
WHERE EXISTS (SELECT 1 FROM orders s WHERE s.customer_id = m.id);

-- List membership with IN
SELECT name FROM customer
WHERE city IN ('Ankara', 'Izmir');

-- Customers with no orders at all (NOT EXISTS)
SELECT name FROM customer m
WHERE NOT EXISTS (SELECT 1 FROM orders s WHERE s.customer_id = m.id);

EXISTS は相関サブクエリが少なくとも 1 行を返すかを確認する(最初の一致で停止するため高速)。IN は値がリスト/サブクエリの結果に含まれるかを確認する。注意: サブクエリが NULL を生成し得る場合、NOT IN は予期しない結果になる。NOT EXISTS のほうが安全。Oracle のオプティマイザは多くの場合、両者を同じ実行計画に変換する。

PostgreSQL

SELECT name FROM customer m
WHERE EXISTS (SELECT 1 FROM orders s WHERE s.customer_id = m.id);

SELECT name FROM customer
WHERE city IN (SELECT city FROM target_cities);

SELECT name FROM customer m
WHERE NOT EXISTS (SELECT 1 FROM orders s WHERE s.customer_id = m.id);

PostgreSQL は EXISTS/IN/NOT EXISTS を完全にサポートする。NOT IN では、サブクエリに NULL が含まれると結果全体が空になることがあるため、NOT EXISTS または LEFT JOIN ... IS NULL が推奨される。プランナはほとんどの場合、IN と EXISTS をセミジョインとして同等に扱う。

MySQL

SELECT name FROM customer m
WHERE EXISTS (SELECT 1 FROM orders s WHERE s.customer_id = m.id);

SELECT name FROM customer
WHERE city IN ('Ankara', 'Izmir');

SELECT name FROM customer m
WHERE NOT EXISTS (SELECT 1 FROM orders s WHERE s.customer_id = m.id);

MySQL/MariaDB は EXISTS/IN/NOT EXISTS をサポートする。MySQL 5.6 より前は相関サブクエリが遅くなることがあった。5.6+ はセミジョインとマテリアライズの最適化で IN サブクエリを高速化した。NOT IN + NULL の落とし穴はここでも当てはまる。

SQL Server

SELECT name FROM customer m
WHERE EXISTS (SELECT 1 FROM orders s WHERE s.customer_id = m.id);

SELECT name FROM customer
WHERE city IN (SELECT city FROM target_cities);

SELECT name FROM customer m
WHERE NOT EXISTS (SELECT 1 FROM orders s WHERE s.customer_id = m.id);

SQL Server は EXISTS/IN/NOT EXISTS をサポートし、オプティマイザはほとんどの場合これらをセミ/アンチジョインとして処理する。NOT IN のサブクエリに NULL が含まれると、結果が予期せず空になることがある。NOT EXISTS が安全な選択肢。同様のメンバーシップロジックには INTERSECT/EXCEPT も使用できる。

MongoDB

// IN equivalent: the $in operator
db.customer.find({ city: { $in: ["Ankara", "Izmir"] } })

// EXISTS equivalent: match check with $lookup + $match
db.customer.aggregate([
  { $lookup: { from: "orders", localField: "id",
      foreignField: "customer_id", as: "s" } },
  { $match: { "s.0": { $exists: true } } }  // at least one order
])

MongoDB では値リストのメンバーシップは $in / $nin 演算子で行う。サブクエリの EXISTS に相当するのは、$lookup で一致するドキュメントの配列を取得し、配列が空かどうかでフィルタリングすること。フィールドの存在確認は別の { field: { $exists: true } } 演算子(行の関係ではなく、フィールドの有無)。

Redis

# Membership check with Set/Hash:
SISMEMBER target_cities "Ankara"   # is it in the set? (IN-like)
EXISTS customer:1                 # does the key exist?

Redis にはサブクエリがない。値のメンバーシップには Set に対する SISMEMBER を使用する(IN に類似)。EXISTS コマンドはサブクエリではなく、指定したキーが存在するかを返す。リレーショナルな存在確認は、複数のキーを読み取ってアプリケーション側で行う。

Elasticsearch

# IN equivalent: terms query
GET customer/_search
{ "query": { "terms": { "city": ["Ankara", "Izmir"] } } }

# Field existence: exists query
GET customer/_search
{ "query": { "exists": { "field": "phone" } } }

Elasticsearch で IN に相当するのは 'terms' クエリ(複数の値のいずれかに一致)で、NOT IN は bool の must_not 内の terms。'exists' クエリは SQL の EXISTS には相当せず、ドキュメント内のフィールドに値があるかを確認する。テーブル間の相関 EXISTS には通常、非正規化が必要。