MongoDB JOIN: the SQL equivalent of $lookup

#MongoDB has joins — they just look different

The claim that "MongoDB cannot join" is a decade out of date. The $lookup stage performs a left outer join between two collections:

SELECT o.*, c.displayName
FROM orders o
JOIN customers c ON c._id = o.customerId

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      localField: "customerId",
      foreignField: "_id",
      as: "customer"
    }
  }
])

The result is not a flat row, though — and that difference drives everything else in this article.

#Joined data arrives as an array

SQL joins multiply rows. $lookup attaches an array of matching documents to each input document. One order with one matching customer still carries customer: [ { ... } ].

To get the flat, SQL-like shape you usually add an $unwind stage:

{ $unwind: "$customer" }

• Without $unwind, one order stays one document.
• With $unwind, an order with two matches becomes two documents — exactly like a SQL join would behave.
• $unwind drops orders with no match unless you ask it to preserve them.

#INNER vs LEFT: the default is the opposite

A bare SQL JOIN is inner; $lookup is a left outer join. The equivalences are:

#What does not translate

Correlated subqueries in ON clauses, non-equi joins on inequalities and full outer joins need the more general $lookup form with its own pipeline argument — or a different data model. If you find yourself writing three nested $lookup stages, MongoDB is quietly suggesting that the documents should have been embedded in the first place.

#Stop translating by hand

Every example in this article is mechanical work: rename fields, reshape arrays, remember which default is which. TableCore does that translation for you — write the JOIN, inspect the generated MongoDB query, and run it. Which join shapes survive that translation, and which are refused, is listed in SQL support.

Try that translation in the browser-based SQL to MongoDB converter. If your query also aggregates the joined rows, the companion guide explains how SQL GROUP BY becomes a MongoDB pipeline. To carry the result into application code, continue with the path from SQL to MongoDB to C#.