EXISTS / IN / NOT EXISTS (subquery)

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 checks whether a correlated subquery returns at least one row (it stops at the first match, so it is fast). IN checks whether a value is in a list/subquery result. Caution: if the subquery can produce NULL, NOT IN gives unexpected results; NOT EXISTS is safer. The Oracle optimizer often turns both into the same plan.

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 fully supports EXISTS/IN/NOT EXISTS. With NOT IN, if the subquery contains NULL the whole result can become empty, so NOT EXISTS or LEFT JOIN ... IS NULL is recommended. The planner handles IN and EXISTS equivalently as a semi-join in most cases.

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 support EXISTS/IN/NOT EXISTS. Before MySQL 5.6 correlated subqueries could be slow; 5.6+ sped up IN subqueries with semi-join and materialization optimizations. The NOT IN + NULL trap applies here as well.

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 supports EXISTS/IN/NOT EXISTS, and the optimizer mostly processes them as semi/anti-joins. If a NOT IN subquery contains NULL, the result can unexpectedly become empty; NOT EXISTS is the safe choice. INTERSECT/EXCEPT can also be used for similar membership logic.

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
])

In MongoDB value-list membership is done with the $in / $nin operators. The equivalent of a subquery EXISTS is to fetch the array of matching documents with $lookup and filter on whether the array is empty. Checking field existence is a separate { field: { $exists: true } } operator (the presence of a field, not a row relationship).

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 has no subqueries. For value membership, use SISMEMBER on a Set (IN-like). The EXISTS command is not a subquery; it returns whether the given key exists. Relational existence checks are done on the application side by reading multiple keys.

Elasticsearch

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

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

In Elasticsearch the IN equivalent is the 'terms' query (match one of several values), and NOT IN is terms inside bool must_not. The 'exists' query does not correspond to SQL EXISTS; it checks whether a field has a value in the document. Correlated EXISTS across tables usually requires denormalization.