# If You Know SQL: Queries Side by Side

The SQL you already write beside its MongoDB twin, from SELECT with WHERE, ORDER BY and a row limit to INSERT, UPDATE and DELETE, with a first look at GROUP BY and JOIN in a pipeline.

Apprentice · MongoDB · SQL · Updated November 19, 2026

By Gregor Dzierzon

## After this lesson you can

- Translate a SELECT with WHERE, ORDER BY and a row limit into find with a filter, a projection, sort and limit
- Translate INSERT, UPDATE and DELETE into insertOne, updateOne and deleteOne, with the WHERE clause as the filter

## Key takeaways

- SELECT's column list becomes find's projection, WHERE becomes its filter, ORDER BY becomes sort, and a row limit becomes limit.
- INSERT, UPDATE and DELETE become insertOne, updateOne and deleteOne, and the WHERE clause becomes the filter.
- The rows affected count becomes matchedCount, modifiedCount and deletedCount.
- GROUP BY and JOIN become stages of an aggregation pipeline, $group and $lookup.

*Optional: this lesson is for readers who already know SQL. Nothing later in the course depends on it.*

When you know SQL, the fastest way into MongoDB's queries is a phrasebook: the sentence you'd write, beside the one MongoDB wants. You'll use it whenever you move a query from one database to the other, or read MongoDB code in a project that also has SQL. The examples use the Riverbend Bike Shop store from *If You Know SQL: One Store Designed Both Ways*, as tables and as documents. The SQL is standard SQL, and its output comes from SQL Server.

## SELECT beside find

The two most expensive bikes, with their names and prices:

```sql
SELECT name, price
FROM products
WHERE type = 'bike'
ORDER BY price DESC
OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY;
```

```text
name               price
-----------------  -------
Gravel Runner      1199.00
Riverbend Cruiser  649.00
```

```mongodb
db.products.find({ type: "bike" }, { _id: 0, name: 1, price: 1 }).sort({ price: -1 }).limit(2)
```

```text
[
  { name: 'Gravel Runner', price: Decimal128('1199.00') },
  { name: 'Riverbend Cruiser', price: Decimal128('649.00') }
]
```

Clause by clause:

| SQL | MongoDB | Here |
|---|---|---|
| `WHERE` | the filter, `find`'s first argument | `{ type: "bike" }` |
| the column list | the projection, the second argument | `{ _id: 0, name: 1, price: 1 }` |
| `ORDER BY ... DESC` | `sort`, with -1 for down | `.sort({ price: -1 })` |
| `FETCH FIRST 2 ROWS ONLY` | `limit` | `.limit(2)` |
| `OFFSET` | `skip` | (not needed here) |

Two differences to watch. A projection always includes `_id` unless you turn it off with `_id: 0`, where `SELECT` gives only the columns you name. And `FETCH FIRST` is the standard spelling of a row limit: MySQL, PostgreSQL and SQLite also write `LIMIT 2`, and SQL Server also writes `TOP 2`. MongoDB has one way, `.limit(2)`.

## INSERT, UPDATE and DELETE

Adding a product is a row in SQL and a document in MongoDB:

```sql
INSERT INTO products (product_id, name, type, price, stock)
VALUES (7, 'Commuter Rack', 'rack', 45.00, 6);
```

```text
(1 rows affected)
```

```mongodb
db.products.insertOne({ _id: 7, name: "Commuter Rack", type: "rack", price: NumberDecimal("45.00"), stock: 6 })
```

```text
{ acknowledged: true, insertedId: 7 }
```

Selling the last Riverbend Cruiser, only if one is left, is the conditional update from *Transactions, and Why You'll Need Them Less*. The `WHERE` becomes the filter, and `SET stock = stock - 1` becomes `$inc` with -1:

```sql
UPDATE products
SET stock = stock - 1
WHERE product_id = 1 AND stock >= 1;
```

```text
(1 rows affected)
```

```mongodb
db.products.updateOne({ _id: 1, stock: { $gte: 1 } }, { $inc: { stock: -1 } })
```

```text
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}
```

Run either one a second time and it changes nothing: SQL reports `(0 rows affected)`, and MongoDB `matchedCount: 0`. The rows affected count became two numbers: how many documents the filter matched, and how many actually changed. Removing the rack again:

```sql
DELETE FROM products WHERE product_id = 7;
```

```text
(1 rows affected)
```

```mongodb
db.products.deleteOne({ _id: 7 })
```

```text
{ acknowledged: true, deletedCount: 1 }
```

One trap: `UPDATE` and `DELETE` without a `WHERE` change every row, but `updateOne` and `deleteOne` change only the first match. The "every match" versions are `updateMany` and `deleteMany`, so choose the method on purpose.

## GROUP BY and JOIN: a first look at the pipeline

Counting products by type is a `GROUP BY`:

```sql
SELECT type, COUNT(*) AS products
FROM products
GROUP BY type
ORDER BY type;
```

```text
type    products
------  --------
bike    3
care    1
helmet  1
light   1
```

MongoDB does this in an **aggregation pipeline**: a list of stages that documents pass through in order. `$group` (read "group") makes one result per value of `"$type"` (read "dollar type": each product's `type`) and adds 1 for each product, and `$sort` puts the types in order:

```mongodb
db.products.aggregate([ { $group: { _id: "$type", products: { $sum: 1 } } }, { $sort: { _id: 1 } } ])
```

```text
[
  { _id: 'bike', products: 3 },
  { _id: 'care', products: 1 },
  { _id: 'helmet', products: 1 },
  { _id: 'light', products: 1 }
]
```

A `JOIN` has a stage too. Each order with its customer's name:

```sql
SELECT o.order_id, c.name AS customer, o.status
FROM orders o
  JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.order_id;
```

```text
order_id  customer  status
--------  --------  -------
1001      Maya      placed
1002      Leo       shipped
1003      Maya      shipped
```

`$lookup` (read "look up") brings in the documents from `customers` whose `_id` matches each order's `customerId`, as an array named `customer`, and `$project` keeps only the fields to show:

```mongodb
db.orders.aggregate([ { $lookup: { from: "customers", localField: "customerId", foreignField: "_id", as: "customer" } }, { $project: { status: 1, "customer.name": 1 } } ])
```

```text
[
  { _id: 1001, status: 'placed', customer: [ { name: 'Maya' } ] },
  { _id: 1002, status: 'shipped', customer: [ { name: 'Leo' } ] },
  { _id: 1003, status: 'shipped', customer: [ { name: 'Maya' } ] }
]
```

You'll reach for `$lookup` less often than for `JOIN`, though: an order's lines are already inside the order, so the join SQL needs most often isn't needed at all. Module 4 teaches the pipeline stage by stage, `$group` and `$lookup` included.

## Check yourself

2 questions to try on the lesson page.

## So what

Your SQL still works as a way of thinking: WHERE is a filter, ORDER BY a sort, UPDATE an update with a filter. What changes is the spelling, and how often you need a join at all.

## Key terms

- [SELECT](https://www.ask-codey.com/glossary/select-database)
- [WHERE](https://www.ask-codey.com/glossary/where-database)
- [ORDER BY](https://www.ask-codey.com/glossary/order-by)
- [find](https://www.ask-codey.com/glossary/find-mongodb)
- [sort, limit and skip](https://www.ask-codey.com/glossary/sort-limit-and-skip)
- [Aggregation Pipeline](https://www.ask-codey.com/glossary/aggregation-pipeline)

## Related

- [If You Know SQL: One Store Designed Both Ways](https://www.ask-codey.com/lessons/mongodb-if-you-know-sql-one-store-both-ways)
- [Finding Documents with find and findOne](https://www.ask-codey.com/lessons/mongodb-find-and-findone)
- [Sorting and Paging: sort, limit and skip](https://www.ask-codey.com/lessons/mongodb-sorting-and-paging)

Source: https://www.ask-codey.com/lessons/mongodb-if-you-know-sql-queries-side-by-side
