MongoDB WHERE IN: the SQL equivalent of $in and $nin

#The clause that looks identical and is not

WHERE ... IN is one of the few pieces of SQL that survives the trip to MongoDB almost unchanged:

SELECT name FROM products WHERE category IN ('tools', 'garden')

db.products.find(
  { category: { $in: ["tools", "garden"] } },
  { name: 1 }
)

$in takes an array and matches a document when the field equals any element of it. That is the whole operator. The surprises are not in the happy path — they are in what happens when the field is missing, when the list is the result of another query, and when you negate it.

#A field that is not there

In SQL a NULL never matches an IN list, because comparing anything to NULL yields unknown rather than true. MongoDB has two ways for a value to be absent — the field is set to null, or the document has no such field at all — and $in treats them the same way for the purpose of matching null:

db.products.find({ category: { $in: [null] } })

That matches documents whose category is explicitly null and documents that never had a category. It is a useful behaviour, and it is not what SQL does. If you need the SQL reading, ask about existence directly:

db.products.find({ category: { $in: [null], $exists: true } })

#NOT IN is where the bodies are buried

The mirror image is $nin, and it is the operator that most often produces a result nobody expected:

SELECT name FROM products WHERE category NOT IN ('tools', 'garden')

db.products.find({ category: { $nin: ["tools", "garden"] } })

These two do not agree on documents with no category. MongoDB returns them — a missing field is not any of the listed values, so it satisfies $nin. SQL does not return the equivalent rows, because NULL NOT IN (...) is unknown, and a row is only selected when the condition is true.

There is a second trap, and it belongs to SQL rather than to MongoDB. If the list itself contains a NULL, NOT IN returns nothing at all:

SELECT name FROM products
WHERE category NOT IN (SELECT category FROM archived)

If a single row of archived has a NULL category, that query answers with an empty result, whatever else is in the table. It is not a bug — comparing a value to an unknown is unknown, and NOT IN needs every comparison to be true. It is simply a rule that almost nobody remembers until a report comes back empty.

#When the list comes from a query

The example above is the interesting case: the list is not literal, it is a subquery. MongoDB has no operator that takes a second collection's values as an $in list, so the work has to happen somewhere:

• run the inner query first and feed its values into $in, which is one extra round trip and is what most tools do;
• or express the whole thing as an aggregation with $lookup, matching the two collections in one pipeline.

Neither is wrong. The first is simpler and fine for a list that stays small. The second keeps it to one command, and is the only option when the list is large enough that inlining it would build an enormous query document.

#Matching inside an array

One more difference worth knowing, because it has no SQL equivalent at all. If the field is itself an array, $in matches when any element of the field appears in the list:

db.products.find({ tags: { $in: ["sale", "clearance"] } })

A document with tags: ["new", "sale"] matches. In SQL that idea needs a join table and an EXISTS, which is a good illustration of why the two models are not simply different spellings of each other.

#Let the translation happen for you

Every rule above is mechanical, and every one of them is easy to get wrong at half past five on a Friday. TableCore takes the SQL, shows you the MongoDB query it generated, and runs it — so the NOT IN semantics are something you can read rather than something you have to remember. SQL support records the places where the engine knowingly decides something differently from SQL, NOT IN among them.

Try it in the browser-based SQL to MongoDB converter. If your query also groups the matching rows, see how SQL GROUP BY becomes a pipeline; if it reaches into a second collection, the $lookup guide covers the join form.