> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ditto.live/llms.txt
> Use this file to discover all available pages before exploring further.
# SELECT
> The `SELECT` operation, once executed, retrieves documents from a collection and uses clauses like `WHERE` to specify conditions for filtering the documents to return.
DQL now supports projections and aggregates in addition to `SELECT *` operations. You can select specific fields, perform calculations, and use aggregate functions like `COUNT`, `SUM`, `AVG`, `MIN`, and `MAX`.
## SELECT Statements
The following syntax outlines the basic structure and optional clauses you can use within your `SELECT` statements:
```sql DQL theme={null}
SELECT [DISTINCT] projection
FROM your_collection_name
[WHERE condition]
[GROUP BY groupby_expression_1, groupby_expression_2, ...]
[HAVING condition]
[ORDER BY orderby_expression_1, orderby_expression_2, ... [ASC|DESC]]
[LIMIT limit_value]
[OFFSET number_of_documents_to_skip]
```
Where `projection` can be:
* `*` - Returns all fields from the documents
* `field1, field2, ...` - Returns specific fields
* `expression AS alias` - Returns calculated values with custom names
* Aggregate functions like `COUNT(*)`, `SUM(field)`, etc.
For instance, retrieve all documents in the `cars` collection `WHERE` the `color` property is set to the value 'blue' :
```sql DQL theme={null}
SELECT * FROM cars WHERE color = 'blue'
```
## Clauses for Filtering
The following table provides an overview of the different clauses you can use to define specific conditions and calculations within your DQL `SELECT` statements to provide more granular control over your queries:
| **Clause** | **Description** |
| ---------- | ---------------------------------------------------------------------------------------------------------------------- |
| FROM | The required clause specifying the collection containing the documents for retrieval. (See [FROM](/dql/select#from)) |
| JOIN | Correlates documents from a second (or further) collection using an ON condition. (See [Joins](#joins)) |
| WHERE | Applies filtering conditions to restrict the documents included in the result set. (See [WHERE](/dql/select#where)) |
| GROUP BY | Groups documents based on one or more expressions for aggregate calculations. (See [GROUP BY](/dql/select#group-by)) |
| HAVING | Filters grouped results based on aggregate conditions. (See [HAVING](/dql/select#having)) |
| ORDER BY | Specifies the sorting order of the result set based on one or more expressions. (See [ORDER BY](/dql/select#order-by)) |
| LIMIT | Restricts the number of documents included in the result set. (See [LIMIT](/dql/select#limit)) |
| OFFSET | Skips a specific number of documents before returning the result set. (See [OFFSET](/dql/select#offset)) |
### FROM
Required in each `SELECT` statement you write in DQL, the `FROM` element identifies the collection for document retrieval.
```sql DQL theme={null}
SELECT *
FROM your_collection_name
```
For example, a `SELECT` statement querying documents from the `cars` collection:
```sql DQL theme={null}
SELECT * FROM cars
```
#### USE IDS
The optional `USE IDS` clause allows you to efficiently retrieve specific documents by their IDs without performing a collection scan. This is more performant than using `WHERE _id IN (...)` filters.
**Syntax:**
```sql DQL theme={null}
-- Inline IDs
SELECT * FROM collection_name USE IDS (id1, id2, id3)
-- Using a parameter array
SELECT * FROM collection_name USE IDS LIST :id_array
```
**Examples:**
```sql DQL theme={null}
-- Retrieve specific documents by ID
SELECT * FROM cars USE IDS ('123', '456', '789')
-- Using a parameter array (recommended for dynamic queries)
SELECT * FROM cars USE IDS LIST :car_ids
```
```swift Swift theme={null}
let carIds = ["123", "456", "789"]
let results = await ditto.store.execute(
query: "SELECT * FROM cars USE IDS LIST :car_ids",
arguments: ["car_ids": carIds]
)
```
```kotlin Kotlin theme={null}
val carIds = listOf("123", "456", "789")
val results = ditto.store.execute(
"SELECT * FROM cars USE IDS LIST :car_ids",
mapOf("car_ids" to carIds)
)
```
```javascript JS theme={null}
const carIds = ["123", "456", "789"];
const results = await ditto.store.execute(
"SELECT * FROM cars USE IDS LIST :car_ids",
{ car_ids: carIds }
);
```
```java Java theme={null}
List carIds = Arrays.asList("123", "456", "789");
ditto.store.execute(
"SELECT * FROM cars USE IDS LIST :car_ids",
Collections.singletonMap("car_ids", carIds)
);
```
```csharp C# theme={null}
var carIds = new List { "123", "456", "789" };
await ditto.Store.ExecuteAsync(
"SELECT * FROM cars USE IDS LIST :car_ids",
new Dictionary { { "car_ids", carIds } }
);
```
```cpp C++ theme={null}
std::vector carIds = {"123", "456", "789"};
std::map> args;
args["car_ids"] = carIds;
ditto.get_store().execute(
"SELECT * FROM cars USE IDS LIST :car_ids",
args
).get();
```
```rust Rust theme={null}
let car_ids = vec!["123", "456", "789"];
ditto.store().execute_v2((
"SELECT * FROM cars USE IDS LIST :car_ids",
serde_json::json!({
"car_ids": car_ids
})
)).await?;
```
```dart Dart theme={null}
final carIds = ["123", "456", "789"];
await ditto.store.execute(
"SELECT * FROM cars USE IDS LIST :car_ids",
arguments: {"car_ids": carIds}
);
```
**Performance Tip**: Where possible, simple equality filters on `_id` (like `WHERE _id = '123'`) are automatically optimized internally similar to USE IDS. However, for explicit control and guaranteed performance, USE IDS is recommended. See also the `#auto_use_ids` directive in [Directives](/dql/directives).
#### Collection Aliasing
You can assign an alias to a collection in the FROM clause to create shorter, more readable queries. The `AS` keyword is optional:
```sql DQL theme={null}
-- With AS keyword
SELECT * FROM cars AS c WHERE c.color = 'blue'
-- Without AS keyword
SELECT * FROM cars c WHERE c.color = 'blue'
```
Collection aliases are particularly useful in:
* **Complex queries**: Shortening long collection names for readability
* **Qualified field references**: Explicitly referencing fields from a specific collection
* **Query directives**: Specifying collection-level directives (see [Directives](/dql/directives))
```sql DQL theme={null}
-- Using collection alias with qualified field references
SELECT c.make, c.model, c.year
FROM cars c
WHERE c.color = 'blue'
ORDER BY c.year DESC
-- Using collection alias with directives
/*+ {"c": {"#index": "ix_color"}} */
SELECT * FROM cars c WHERE c.color = 'blue'
```
Collection aliases follow the same identifier rules as projection aliases and can also use backticks for special characters or reserved words.
#### JOIN
Joins are only supported for local SDK queries. Joins are not supported in subscription queries or Big Peer/Server queries. See [restrictions](#restrictions) for more details.
The `FROM` clause may be followed by one or more `JOIN` terms to correlate documents from additional collections. Ditto supports `INNER JOIN`, `LEFT OUTER JOIN`, and `RIGHT OUTER JOIN`. Each join requires an access path that isn't a full collection scan on the inner collection (by default); the `ADVISE` statement can recommend appropriate indexes.
```sql DQL theme={null}
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
WHERE o.status = 'paid'
```
For full syntax, join types, index requirements, query plan details, and advanced usage, see [Joins](#joins) below.
### WHERE
The `WHERE` clause filters data based on either an expression or a set of conditions that narrow the result set returned to you:
```sql DQL theme={null}
SELECT *
FROM your_collection_name
WHERE [condition]
```
For example, here is a basic `SELECT` statement querying documents from the `cars` collection based on a given address:
```sql DQL theme={null}
SELECT * FROM cars
WHERE location.address = '123 Main St, San Francisco, CA 98105'
```
To demonstrate a more complex query, here is a `SELECT` statement that queries using multiple expressions and logical operators to further refine the criteria for document retrieval:
```sql DQL theme={null}
SELECT * FROM cars
WHERE color = 'blue' AND features.trim = 'Standard' OR features.mileage > 10000
```
### ORDER BY
With the `ORDER BY` clause, if you'd like, you can integrate calculations or expressions in your `SELECT` statement. Then sort the resulting documents to return in either ascending (`ASC`) or descending (`DESC`) alphabetical order:
```sql DQL theme={null}
SELECT *
FROM your_collection_name
ORDER BY expression_1, expression_2, ... [ASC|DESC]
```
For example, here is a simple `SELECT` statement that uses the `ORDER BY` clause to query and sort documents from the `cars` collection in descending (`DESC`) alphabetical order based on the field value set for the `color` property:
```sql DQL theme={null}
SELECT *
FROM cars ORDER BY color DESC
```
In this syntax:
* `your_collection_name` is the name of the collection from which you want to retrieve the data.
* `expression_1, expression_2, ...` are the expressions evaluated to sort the result. Expressions are resolved in order.
* `[ASC|DESC]` is an optional parameter that specifies the sort order. If omitted, the default sort order is ascending (`ASC`). To sort in descending order, you can specify `DESC`.
**Example**
In this example, the result set from the query will be sorted in descending order based on the values in the field:
```sql DQL theme={null}
-- Sort by a given field_name
SELECT *
FROM your_collection_name
ORDER BY field_name DESC
```
For instance, here `"blue"` cars return first and other cars sort by the natural order in the collection:
```sql DQL theme={null}
SELECT *
FROM cars
ORDER BY color = 'blue'
```
#### **Sort Order by Object Type**
In DQL, the hierarchy for comparing and sorting objects varies based on the following criteria:
* If ascending (`ASC`) order operations:
* `boolean`
* `number`
* `binary`
* `string`
* `array`
* `object`
* `null`
* `missing`
* If descending (`DESC`) order operations, sorting order is reversed:
* `missing`
* `null`
* `object`
* `array`
* `string`
* `binary`
* `number`
* `boolean`
* If evaluating values, `true` results are prioritized and ordered first followed by `false` results.
#### Expressing Sort Order
Unless explicitly defined as `DESC` in your query, Ditto defaults to sorting in ascending (`ASC`). So, if you want to sort in ascending order, you do *not* have to express that in your query.
### LIMIT
The `LIMIT` clause is used to restrict the number of documents returned by a query, allowing you to specify a maximum limit on the number of documents to be included in the result set:
```sql DQL theme={null}
SELECT *
FROM your_collection_name
LIMIT limit_value
```
In this syntax:
* `your_collection_name` is the name of the collection from which you want to retrieve the data.
* `limit_value` is the maximum number of documents you want to include in the result set.
For example, only return the first 10 documents from the `your_collection_name` collection:
```sql DQL theme={null}
SELECT *
FROM your_collection_name
LIMIT 10
```
### OFFSET
The `OFFSET` clause is used to specify the number of records to skip before starting to return documents from the query result:
```sql DQL theme={null}
SELECT *
FROM your_collection_name
OFFSET number_of_items_to_skip
```
In this syntax:
* `your_collection_name` is the name of the collection from which you want to retrieve the data.
* `number_of_items_to_skip` is the number of items before returning the result set.
Using `OFFSET` with `LIMIT` is a common way to utilize `OFFSET`; for example:
```sql DQL theme={null}
SELECT *
FROM your_collection_name
LIMIT 10
OFFSET 10
```
## Projections
Projections allow you to specify exactly which fields or calculated values to return from your query, rather than returning entire documents. This reduces data transfer and processing overhead.
The same projection syntax is used by the [`RETURNING`](/dql/returning) clause of `INSERT`, `UPDATE` and `DELETE`/`EVICT`/`TOMBSTONE` statements, with a small number of restrictions.
### Basic Field Selection
Select specific fields from documents:
```sql DQL theme={null}
SELECT make, model, year FROM cars
```
### Excluding Fields
To exclude specific fields from a projection, use the `MISSING` keyword. This can be combined with `*` to select all fields except those explicitly excluded:
```sql DQL theme={null}
-- Given a document: { "_id": "abc", "make": "Toyota", "model": "Camry", "color": "blue", "vin": "1234" }
-- Exclude "vin" and "color", keeping everything else:
SELECT cars.*, MISSING vin, MISSING color FROM cars
-- Result: { "_id": "abc", "make": "Toyota", "model": "Camry" }
```
### Aliasing
Use aliases to rename fields in your results:
```sql DQL theme={null}
SELECT make AS manufacturer, model AS car_model FROM cars
```
Default aliases are assigned if not specified:
* Simple field references use the field name as the alias
* Expressions get aliases like `($1)`, `($2)`, etc.
Aliases bind to the element immediately prior in the statement. `SELECT COUNT(*) AS car_count FROM cars` names the output field, but `SELECT COUNT(*) FROM cars AS car_count` aliases the `cars` collection — `COUNT(*)` falls back to its default alias.
#### Alias Constraints
Aliases must follow these rules:
* **Uniqueness**: Each alias in a SELECT list must be unique. Duplicate aliases will raise an error.
* **Identifier Rules**: Aliases must be valid identifiers following field naming conventions.
* **Special Characters**: Use backticks (grave accents) to quote aliases containing special characters or reserved words.
```sql DQL theme={null}
-- Using backticks for special characters
SELECT price AS `final-price`, model AS `car-model` FROM cars
-- Using backticks for reserved words
SELECT status AS `order`, type AS `select` FROM orders
```
#### Alias Scope
Projection aliases defined in the SELECT list can be referenced by the **ORDER BY** clause to sort by an aliased expression:
```sql DQL theme={null}
SELECT make, price * 0.9 AS sale_price FROM cars ORDER BY sale_price DESC
```
`GROUP BY` and `HAVING` do **not** accept projection aliases. They must reference the underlying source expression or aggregate function directly: use `GROUP BY color` (the source field) rather than `GROUP BY c` (a projection alias), and `HAVING COUNT(*) > 5` (the aggregate) rather than `HAVING car_count > 5` (the alias).
### Calculated Fields
Create new fields using expressions:
```sql DQL theme={null}
SELECT make, model, price * 0.9 AS discounted_price FROM cars
```
### DISTINCT Results
Remove duplicate rows from your results:
```sql DQL theme={null}
SELECT DISTINCT color FROM cars
```
**DISTINCT Performance Considerations:**
`DISTINCT` has performance implications you should understand:
**Memory Buffering:**
* All projections must be buffered in memory to enforce uniqueness
* This increases memory requirements proportional to the number of unique results
* Memory usage grows with the result set size
**Redundant Usage:**
* When the `_id` field is included (e.g., `SELECT DISTINCT *`), DISTINCT is redundant
* Each document already has a unique `_id`, making DISTINCT unnecessary
* This adds overhead without benefit - avoid in these cases
**Best Practices:**
* Only use DISTINCT when you truly need unique combinations of projected fields
* Avoid DISTINCT when projecting complete documents (`SELECT DISTINCT *`)
* Consider if your application logic can handle duplicates instead
* For large result sets, evaluate if DISTINCT is worth the memory cost
Projections in a [`RETURNING`](/dql/returning) clause may *not* specify `DISTINCT`; there, the word is taken to be a field name rather than a keyword.
## Aggregate Functions
Aggregate functions perform calculations across multiple documents and return a single result. DQL supports the following aggregate functions:
For a quick reference table of all aggregate functions and their syntax, see [Operator Expressions - Aggregate Functions](/dql/operator-expressions#aggregate-functions).
### COUNT
Count documents or non-null values:
```sql DQL theme={null}
-- Count all documents
SELECT COUNT(*) FROM cars
-- Count non-null values in a field
SELECT COUNT(color) FROM cars
-- Count distinct values
SELECT COUNT(DISTINCT color) FROM cars
```
### SUM
Calculate the sum of numeric values:
```sql DQL theme={null}
SELECT SUM(price) AS total_value FROM cars
-- Sum distinct values only
SELECT SUM(DISTINCT price) FROM cars
```
### AVG
Calculate the average of numeric values:
```sql DQL theme={null}
SELECT AVG(mileage) AS average_mileage FROM cars
```
### MIN and MAX
Find minimum and maximum values:
```sql DQL theme={null}
SELECT MIN(year) AS oldest_year, MAX(year) AS newest_year FROM cars
```
### MID
Calculate the midpoint between minimum and maximum values:
```sql DQL theme={null}
SELECT MID(price) AS midpoint_price FROM cars
-- With DISTINCT
SELECT MID(DISTINCT price) FROM cars
```
The `MID` function returns the midpoint value between `MIN(expr)` and `MAX(expr)`. Non-numeric values are silently ignored.
### MEDIAN
Calculate the median (middle) value:
```sql DQL theme={null}
SELECT MEDIAN(price) AS median_price FROM cars
-- With DISTINCT
SELECT MEDIAN(DISTINCT mileage) FROM cars
```
The `MEDIAN` function returns the middle value when all values are sorted. Non-numeric values are silently ignored.
### Combining Aggregates
Use multiple aggregate functions in a single query:
```sql DQL theme={null}
SELECT
COUNT(*) AS total_cars,
AVG(price) AS avg_price,
MIN(price) AS lowest_price,
MAX(price) AS highest_price
FROM cars
```
### GROUP BY
Group results and calculate aggregates for each group:
```sql DQL theme={null}
SELECT color, COUNT(*) AS car_count, AVG(price) AS avg_price
FROM cars
GROUP BY color
```
When using aggregates with `GROUP BY`, non-aggregate projections must be part of the `GROUP BY` clause:
```sql DQL theme={null}
-- This works: 'make' is in GROUP BY
SELECT make, COUNT(*) AS model_count
FROM cars
GROUP BY make
-- This would fail: 'model' is not in GROUP BY
-- SELECT make, model, COUNT(*) FROM cars GROUP BY make
```
### HAVING
Filter grouped results based on aggregate values:
```sql DQL theme={null}
SELECT color, COUNT(*) AS car_count
FROM cars
GROUP BY color
HAVING COUNT(*) > 5
```
Aggregate functions form a "dam" in the execution pipeline - all documents must be processed before results can be returned. This differs from non-aggregate queries which can stream results.
### Aggregate Function Behavior
Understanding how aggregate functions work internally helps optimize query performance:
**Memory Requirements:**
* Aggregates accumulate results per group, so memory usage depends on the number of groups
* A small number of groups requires little memory regardless of document count
* A large number of groups increases memory requirements proportionally
* `DISTINCT` aggregates must buffer all distinct values in memory, which can significantly increase memory usage with many unique values
**MISSING Values:**
* If no documents qualify for an aggregate in a group, the result is `MISSING` (absent from results)
* To always include a result, use conditional functions: `SUM(IFMISSING(field, 0))`
* `MIN` and `MAX` silently ignore `MISSING` values
* `COUNT` does not count `NULL`, `MISSING`, or `FALSE` values unless explicitly handled
**Type Handling:**
* `SUM`, `AVG`, `MID`, and `MEDIAN` silently ignore non-numeric values
* `MIN` and `MAX` compare values using Ditto's standard type ordering rules
* `COUNT(*)` is equivalent to `COUNT(true)` with no additional overhead
**Performance Tips:**
* Use `COUNT(*)` instead of `COUNT(field)` when counting all documents
* Avoid `DISTINCT` with aggregates unless necessary due to memory overhead
* Minimize the number of groups in `GROUP BY` for better memory efficiency
* Use `HAVING` to filter groups rather than filtering after aggregation
### Advanced Aggregate Examples
**Handling MISSING Values:**
```sql DQL theme={null}
-- Always return 0 instead of MISSING when no documents match
SELECT IFMISSING(SUM(price), 0) AS total_sales FROM orders WHERE status = 'completed'
-- Use default value for missing fields during aggregation
SELECT SUM(IFMISSING(price, 0)) AS total_with_defaults FROM products
-- Count all non-missing, non-null values regardless of truthiness
SELECT COUNT(NOT ISMISSINGORNULL(rating)) AS rated_items FROM reviews
```
**Conditional Aggregation with CASE:**
```sql DQL theme={null}
-- Sum order values by status within a single query
SELECT
COUNT(*) AS total_orders,
SUM(CASE WHEN status = 'completed' THEN final_price ELSE 0 END) AS completed_value,
SUM(CASE WHEN status = 'pending' THEN price * discount ELSE 0 END) AS pending_value,
SUM(CASE WHEN status = 'cancelled' THEN price ELSE 0 END) AS cancelled_value
FROM orders
-- Calculate weighted averages
SELECT
category,
AVG(CASE WHEN priority = 'high' THEN price * 1.5 ELSE price END) AS weighted_avg_price
FROM products
GROUP BY category
-- Sum with conditional multipliers
SELECT
SUM(CASE
WHEN quantity > 100 THEN price * quantity * 0.9
WHEN quantity > 50 THEN price * quantity * 0.95
ELSE price * quantity
END) AS total_revenue
FROM order_items
```
**Complex Business Logic:**
```sql DQL theme={null}
-- Calculate profit margin by category
SELECT
category,
(SUM(sale_price) - SUM(cost_price)) / SUM(sale_price) * 100 AS profit_margin_pct,
COUNT(*) AS items_sold
FROM sales
WHERE sale_date >= '2024-01-01'
GROUP BY category
HAVING (SUM(sale_price) - SUM(cost_price)) > 1000
-- Customer lifetime value with fallback for missing data
SELECT
customer_id,
COUNT(*) AS total_orders,
SUM(IFMISSING(order_total, 0)) AS lifetime_value,
AVG(IFMISSING(order_total, 0)) AS avg_order_value,
MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id
HAVING SUM(IFMISSING(order_total, 0)) > 500
-- Inventory health metrics
SELECT
warehouse_id,
COUNT(*) AS total_items,
SUM(CASE WHEN stock_level < reorder_point THEN 1 ELSE 0 END) AS items_needing_reorder,
SUM(CASE WHEN stock_level = 0 THEN 1 ELSE 0 END) AS out_of_stock_items,
AVG(IFMISSING(stock_level, 0)) AS avg_stock_level
FROM inventory
GROUP BY warehouse_id
```
**Filtering Within Aggregates:**
```sql DQL theme={null}
-- Count only items matching specific conditions
SELECT
department,
COUNT(*) AS total_employees,
COUNT(CASE WHEN salary > 100000 THEN 1 END) AS high_earners,
AVG(CASE WHEN years_experience > 5 THEN salary END) AS avg_senior_salary
FROM employees
GROUP BY department
-- Calculate percentages within groups
SELECT
region,
COUNT(*) AS total_sales,
SUM(CASE WHEN product_type = 'premium' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS premium_pct
FROM sales
GROUP BY region
```
**Handling NULL and MISSING Gracefully:**
```sql DQL theme={null}
-- Coalesce with multiple fallbacks
SELECT
product_id,
COUNT(*) AS review_count,
AVG(COALESCE(rating, average_category_rating, 3.0)) AS effective_rating
FROM reviews
GROUP BY product_id
-- Conditional counting with null handling
SELECT
status,
COUNT(*) AS total,
COUNT(completed_date) AS completed_with_date,
COUNT(*) - COUNT(completed_date) AS missing_dates
FROM tasks
GROUP BY status
-- Sum with NULL protection
SELECT
order_id,
SUM(IFNULL(item_price * quantity, 0)) AS order_total,
COUNT(*) AS item_count,
SUM(IFNULL(discount_amount, 0)) AS total_discount
FROM order_items
GROUP BY order_id
```
**Multi-level Aggregations:**
```sql DQL theme={null}
-- Nested calculations with aggregates
SELECT
category,
COUNT(*) AS product_count,
SUM(price * inventory_count) AS total_inventory_value,
SUM(price * inventory_count) / COUNT(*) AS avg_value_per_product,
MAX(price) - MIN(price) AS price_range
FROM products
GROUP BY category
HAVING COUNT(*) >= 5
-- Time-based aggregations with conditionals
SELECT
DATE_FORMAT(created_at,'YYYY-MM-DD') AS order_date,
COUNT(*) AS daily_orders,
SUM(IFMISSING(total, 0)) AS daily_revenue,
AVG(CASE WHEN total > 0 THEN total END) AS avg_order_value_excluding_zero
FROM orders
WHERE created_at >= DATE_CAST('2024-01-01','YYYY-MM-DD')
GROUP BY DATE_FORMAT(created_at,'YYYY-MM-DD')
ORDER BY order_date DESC
```
**Working with Arrays and Complex Fields:**
```sql DQL theme={null}
-- Aggregate with array transformations
SELECT
seller_id,
COUNT(*) AS total_products,
AVG(array_length(tags)) AS avg_tags_per_product,
SUM(CASE WHEN array_length(tags) > 5 THEN 1 ELSE 0 END) AS well_tagged_products
FROM products
GROUP BY seller_id
-- Conditional aggregation on nested fields
SELECT
category,
COUNT(*) AS total_items,
AVG(IFMISSING(details.weight, 0)) AS avg_weight,
SUM(CASE WHEN details.fragile = true THEN 1 ELSE 0 END) AS fragile_count
FROM inventory
GROUP BY category
```
## Joins
Joins are available in SDK version 5.1 and later and are supported only on the Ditto SDK (small peer). They are **not** supported in Big Peer (cloud/server) environments. See [Restrictions](#restrictions) for details.
DQL joins let you correlate documents across two or more collections within a single `SELECT` statement. Rather than issuing separate queries and stitching results together in application code, you express the relationship directly in DQL and let the query engine handle it efficiently.
A join appends fields from an additional collection to each row produced by the preceding collection (or join result), based on a condition you supply in the `ON` clause. The engine iterates over the outer (driving) collection (or join result) and, for each outer row, probes the inner (joined) collection using an appropriate access path in a strategy known as a **nested-loop join** (`nlJoin` in `EXPLAIN` output).
**Why use joins?**
* **Avoid data duplication.** Store related data in separate collections and compose views on demand rather than embedding copies everywhere.
* **Cross-collection queries.** Filter, sort, and project fields from multiple collections in one round trip to the store.
* **Richer aggregations.** Group and aggregate across collections — for example, sum order amounts per customer, or count products per order.
**Practical example.** Suppose you maintain a `customers` collection and an `orders` collection. Without joins you would fetch all orders, then issue a second query per customer to look up the name. With a join you express this directly:
```sql DQL theme={null}
SELECT c.name, o.amount, o.status
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
WHERE o.status = 'paid'
ORDER BY c.name, o.amount
```
### Syntax
Each term (collection or prior join) in the [FROM](#from) clause may be followed by a JOIN.
#### Join types
| Keyword | Behaviour |
| ---------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `JOIN` or `INNER JOIN` | Only rows where the `ON` condition evaluates to `true` appear in the result. |
| `LEFT JOIN` or `LEFT OUTER JOIN` | All rows from the left (outer) collection appear; rows with no matching document in the right collection are padded with `MISSING` for the right-side fields. |
| `RIGHT JOIN` or `RIGHT OUTER JOIN` | Supported only as the **first** join term. Internally rewritten to `LEFT OUTER JOIN` with the tables swapped. |
#### ON condition
The `ON` expression may reference any collection alias that has already been introduced in the `FROM` clause — that is, the driving collection and any collections joined before the current term. It may be any valid DQL expression, including compound conditions with `AND` or `OR`.
#### Collection aliases
Aliases are strongly recommended when joining so that field references are unambiguous. Aliases must be unique across all collections in the statement.
```sql DQL theme={null}
-- Recommended: explicit aliases on every collection
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
```
#### USE IDS, USE INDEX, and USE DIRECTIVES
Each collection term in a join may include `USE IDS`, `USE INDEX`, or `USE DIRECTIVES` sub-clauses to control how that collection is accessed. These apply per-collection and are particularly useful for the inner leg of a join.
```sql DQL theme={null}
-- Force a collection scan on the inner leg (see Overriding the index requirement below)
SELECT c.name, o.amount
FROM customers c
JOIN orders o USE INDEX "" ON c.cust_id = o.cust_id
```
### Index requirements
By default, every inner collection in a join **must** be accessed via an index scan (or combine index scans) or an ID scan. Attempting to join without an appropriate access path will produce an error:
```
Query failed: Joining to "orders" disallowed without appropriate index support.
Please run ADVISE for recommendations.
```
You can override this on a per-collection basis by adding `USE INDEX ""` to the join collection term, which explicitly permits a collection scan. See [Overriding the index requirement](#overriding-the-index-requirement) for details.
#### Creating a join index
The join key on the inner (right-hand) collection must be indexed. Create the index before executing a join query:
```sql DQL theme={null}
-- Create an index on the join key in the inner collection
CREATE INDEX ix_orders_cust_id ON orders (cust_id INCLUDE MISSING)
```
The `INCLUDE MISSING` option ensures documents that lack the `cust_id` field are covered by the index and will not be silently omitted from results.
#### Getting index recommendations with ADVISE
If you are unsure which indexes a join requires, run `ADVISE` on the statement. The engine analyses the query and suggests the optimal indexes, including composite covering indexes where beneficial:
```sql DQL theme={null}
ADVISE SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
```
Example output:
```json theme={null}
{
"advice": {
"statement": "SELECT c.name, o.amount FROM customers c JOIN orders o ON c.cust_id = o.cust_id\n",
"suggestedIndexes": [
{
"collection": "orders",
"reason": "equality predicates on `cust_id`; covering index; supports join",
"statement": "CREATE INDEX IF NOT EXISTS adv_orders_cov_cust_id_amount ON default:`orders` (`cust_id` ASC, `amount` ASC)"
}
]
}
}
```
Once all required indexes exist, `ADVISE` confirms this:
```json theme={null}
{
"advice": {
"existingIndexes": [
{
"collection": "orders",
"statement": "CREATE INDEX IF NOT EXISTS ix_orders_cust_id ON default:`orders` (`cust_id` ASC)"
}
],
"outcome": "optimal indexes already exist",
"statement": "..."
}
}
```
### Examples
The examples below use these two collections:
* **`customers`** — `_id`, `cust_id` (integer), `name`, `tier`
* **`orders`** — `_id`, `order_id` (integer), `cust_id` (integer), `amount`, `status`
Assume the following index exists for all examples:
```sql DQL theme={null}
CREATE INDEX ix_orders_cust_id ON orders (cust_id INCLUDE MISSING)
```
#### INNER JOIN
Returns only rows where the ON condition is satisfied in both collections. Customers with no orders and orders with no matching customer are excluded.
```sql DQL theme={null}
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
ORDER BY c.name, o.amount
```
#### LEFT OUTER JOIN
All customers are returned. Where no matching order exists, the order-side fields (`o.amount`) are `MISSING` in the result document.
```sql DQL theme={null}
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
ORDER BY c.name, o.amount
```
You can identify unmatched outer rows by testing for `MISSING`:
```sql DQL theme={null}
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
WHERE o.amount IS MISSING
ORDER BY c.name
```
#### RIGHT OUTER JOIN
Supported only as the first join in the statement, and is internally rewritten to a `LEFT OUTER JOIN` with the tables swapped. All orders are returned; customers with no matching order have `MISSING` name fields.
```sql DQL theme={null}
SELECT c.name, o.amount
FROM customers c
RIGHT JOIN orders o ON c.cust_id = o.cust_id
ORDER BY o.amount
```
#### Post-join WHERE filter
`WHERE` predicates are evaluated after the join. Predicates that reference only one collection are pushed down into the join condition where possible.
```sql DQL theme={null}
SELECT c.name, o.order_id
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
WHERE o.status = 'paid'
ORDER BY c.name, o.order_id
```
#### Multi-expression ON clause
The `ON` clause may contain compound expressions. Conditions that can be evaluated against the outer row alone are filtered early; the remainder form the join condition.
```sql DQL theme={null}
-- Only gold-tier customers join with their orders
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id AND c.tier = 'gold'
ORDER BY c.name, o.amount
```
#### Wildcard projection across joined collections
Use `*` to include all collections, each under its alias:
```sql DQL theme={null}
SELECT *
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
```
Or expand a single collection's fields:
```sql DQL theme={null}
SELECT c.*, o.amount, o.status
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
```
#### Multi-collection chains
You may chain any number of join terms (See [Directives](#directives)). Each subsequent join may reference all previously introduced aliases in its `ON` clause.
```sql DQL theme={null}
-- Three-way join: customers -> orders -> products
SELECT c.name, o.order_id, p.pname
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
JOIN products p ON o.order_id = p.order_id
ORDER BY c.name, o.order_id, p.pname
```
```sql DQL theme={null}
-- Four-way join: customers -> orders -> products -> reviews
-- LEFT JOIN on reviews so products without reviews still appear
SELECT c.name, o.order_id, p.pname, r.rating
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
JOIN products p ON o.order_id = p.order_id
LEFT JOIN reviews r ON p.product_id = r.product_id
ORDER BY c.name, o.order_id, p.pname, r.rating
```
#### Joining on document ID
When the `ON` condition equates a field from the outer collection to `_id` of the inner collection, the planner uses an efficient `idScan` rather than a regular index scan — no additional index is required on the inner collection.
```sql DQL theme={null}
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON o._id = c.latest_order_id
```
### Directives
All [query directives](/dql/directives) are available when using joins. A number are particularly relevant.
#### Limiting the number of join terms
The `#max_joins` global directive limits how many join terms a statement may contain. The default is `10`. Set it to `0` to prohibit joins entirely in a given query.
```sql DQL theme={null}
/*+ {"#max_joins": 2} */
SELECT c.name, o.amount, p.pname
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
JOIN products p ON o.order_id = p.order_id
```
#### Controlling access paths per collection
Use the `USE INDEX` or `USE DIRECTIVES` sub-clause on any collection term to influence the access path for that specific leg of the join.
```sql DQL theme={null}
-- Force the inner leg to use a specific index
SELECT c.name, o.amount
FROM customers c
JOIN orders o USE INDEX 'ix_orders_cust_id' ON c.cust_id = o.cust_id
-- Force an intersect scan by specifying candidate indexes
/*+ {"o": {"#index": ["ix_orders_cust_id", "ix_orders_status"]}} */
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
WHERE o.status = 'paid'
```
The `USE INDEX` and `USE DIRECTIVES` sub-clauses on join collections are collection-level directives and apply to that collection instance only. They follow the same precedence rules as described in [Query Directives](/dql/directives).
#### Overriding the index requirement
You can explicitly permit a collection scan on an inner join leg by supplying an empty string to `USE INDEX`. This overrides the default requirement for indexed access and should only be used when the inner collection is small or when you have a specific reason to avoid an index access path.
```sql DQL theme={null}
SELECT c.name, o.amount
FROM customers c
JOIN orders o USE INDEX "" ON c.cust_id = o.cust_id
```
The override is per-collection. If you have three join terms and supply `USE INDEX ""` on only one, the other two must still have index access paths.
#### Prefer-order with joins
The `#prefer_order` directive applies to the driving collection. It is rarely necessary for joins — the join order is fixed by the statement — but can influence index selection on the outer leg when `ORDER BY` is present.
### Understanding query plans with EXPLAIN
Prefix any join `SELECT` with `EXPLAIN` to inspect the plan without executing the query. The plan reveals which access path the planner chose for each leg of the join — index scan, covering index scan, intersect scan, or ID scan — and confirms whether `LEFT OUTER` semantics are in effect.
See [EXPLAIN — Join plans](/dql/explain#join-plans) for a full description of the `nlJoin` operator, dynamic span bounds, covering and intersect scans, and how `LEFT OUTER`, `RIGHT OUTER`, and multi-collection joins appear in the plan.
### Best practices
1. **Index every join key on the inner collection.** Use `ADVISE` to confirm which indexes are needed.
2. **Use composite covering indexes** that include both the join key and any projected or filtered fields from the inner collection. This eliminates the `fetch` step and reduces document reads.
3. **Order join terms thoughtfully.** Place the most selective (smallest) collection first as the outer (driving) leg to reduce the number of inner-leg probes.
4. **Qualify all field references with aliases.** Unqualified field names in multi-collection queries can be ambiguous.
5. **Use `EXPLAIN` during development** to confirm the planner has chosen index scans rather than collection scans for inner legs.
6. **Use `ADVISE` before deploying** new join queries to ensure optimal indexes are in place.
### Restrictions
* **Not supported in Big Peer environments.** Joins are a Ditto SDK (small peer) feature only.
* **Each inner collection must have an indexed access path** — either an index scan, a covering index scan, an intersect/union scan, or an ID scan. A collection scan on an inner leg is rejected unless explicitly permitted via `USE INDEX ""`.
* **`RIGHT OUTER JOIN`** is only supported as the first join term. Subsequent join terms must use `INNER` or `LEFT OUTER`. The right join is rewritten internally; the rewrite is transparent to the result.
* **The join count limit** defaults to 10 join terms per statement. Adjust with `/*+ {"#max_joins": N} */`.
* **Using joins in `registerSubscription`** will result in an unsupported query error. Joins are supported in `execute` and `registerObserver`.
## Related topics
- [INSERT](/dql/insert.md)
- [Data Browser and Editor](/cloud/portal/data-browser-and-editor.md)
- [MongoDB Aggregation Pipeline to DQL](/dql/mongodb-aggregation-comparison.md)