# Choosing the Best Join Strategy in Prisma ORM: join vs query URL: https://www.prisma.io/blog/prisma-orm-now-lets-you-choose-the-best-join-strategy-preview Published: 2024-02-21 Updated: 2026-07-06 Authors: Nikolas Burk Tags: orm, education Description: Prisma ORM loads relations with database-level joins (a single SQL query with JSON aggregation) or application-level joins (one query per table). Learn how both work and when to use which. Prisma ORM supports two strategies for loading related data. With the `join` strategy, the database merges the relations itself and returns nested data from a single SQL query, using JSON aggregation and lateral joins on PostgreSQL. With the `query` strategy, Prisma sends one query per table and merges the results in the application. You pick the strategy per query with the `relationLoadStrategy` option, available on PostgreSQL, CockroachDB, and MySQL behind the `relationJoins` preview feature flag; once the flag is on, `join` is the default. This post explains how both strategies work under the hood and when to use which. ## Contents [#contents] * [The two join strategies in Prisma ORM](#the-two-join-strategies-in-prisma-orm) * [What actually goes over the wire](#what-actually-goes-over-the-wire) * [`join` vs `query`: when to use which?](#join-vs-query-when-to-use-which) * [Understanding relations in SQL databases](#understanding-relations-in-sql-databases) * [What's happening under the hood?](#whats-happening-under-the-hood) * [Joins in Prisma 8](#joins-in-prisma-8) * [Try it out and share your feedback](#try-it-out-and-share-your-feedback) ## The two join strategies in Prisma ORM [#the-two-join-strategies-in-prisma-orm] [Support for database-level joins](https://github.com/prisma/orm/issues/5184) was one of the most requested features in Prisma ORM, and it shipped as the `relationLoadStrategy` option. ![](https://www.prisma.io/blog/prisma-orm-now-lets-you-choose-the-best-join-strategy-preview/imgs/7273f2beaf20bbf771e47bb7b542b3c21c31e19c-800x653.svg) For any relation query with `include` (or `select`), the top-level `relationLoadStrategy` option accepts one of two values: * `join` (default): Uses the database-level join strategy to merge the data in the database. * `query`: Uses the application-level join strategy by sending multiple queries to individual tables and merging the data in the application layer. To enable `relationLoadStrategy`, add the `relationJoins` preview feature flag to the `generator` block of your Prisma schema: ```prisma // prisma/schema.prisma generator client { provider = "prisma-client" output = "../src/generated/prisma" previewFeatures = ["relationJoins"] } datasource db { provider = "postgresql" } ``` > **Note**: `relationLoadStrategy` is available for PostgreSQL, CockroachDB, and MySQL databases. Without the flag, Prisma Client always uses the application-level strategy, and the `relationLoadStrategy` option is rejected as an unknown argument. You'll need a PostgreSQL database to follow along. The fastest way to get one is [Prisma Postgres](https://www.prisma.io/postgres): running `npm create db` provisions a database and gives you the connection string to put in your `.env` file. After adding the flag, re-run `npx prisma generate` for the change to take effect. Here is the data model we'll use, along with the client setup and an example query using the `join` strategy: ```prisma model User { id Int @id @default(autoincrement()) name String? posts Post[] } model Post { id Int @id @default(autoincrement()) title String author User @relation(fields: [authorId], references: [id]) authorId Int } ``` ```ts import { PrismaClient } from "./generated/prisma/client"; import { PrismaPg } from "@prisma/adapter-pg"; const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL }); const prisma = new PrismaClient({ adapter }); const usersWithPosts = await prisma.user.findMany({ relationLoadStrategy: "join", // or "query" include: { posts: true, }, }); ``` Because `"join"` is the default once the preview flag is enabled, the `relationLoadStrategy` option could technically also be omitted in the snippet above. We show it here for illustration purposes. ## What actually goes over the wire [#what-actually-goes-over-the-wire] The difference between the two strategies is easy to see in the SQL that Prisma Client generates. We ran the query above on Prisma ORM 7.8 against a PostgreSQL database and logged the statements. With the `query` strategy (or without the preview flag), fetching users with their posts sends two queries, one per table: ```sql SELECT "public"."User"."id", "public"."User"."name" FROM "public"."User" WHERE 1=1 OFFSET $1 SELECT "public"."Post"."id", "public"."Post"."title", "public"."Post"."authorId" FROM "public"."Post" WHERE "public"."Post"."authorId" IN ($1,$2) OFFSET $3 ``` Prisma Client then merges the two result sets in memory to build the nested objects. With the `join` strategy, the same Prisma Client query sends exactly one statement, in which the database performs a lateral join and builds the nested JSON structures itself: ```sql SELECT "t0"."id", "t0"."name", "User_posts"."__prisma_data__" AS "posts" FROM "public"."User" AS "t0" LEFT JOIN LATERAL ( SELECT COALESCE(JSONB_AGG("__prisma_data__"), '[]') AS "__prisma_data__" FROM (...) -- posts of the current user, shaped as JSON ) AS "User_posts" ON TRUE ``` One round trip, no in-memory merging, and the JSON aggregation happens where the data lives. ## `join` vs `query`: when to use which? [#join-vs-query-when-to-use-which] With two query strategies available, you'll wonder: when to use which? Because of the lateral, aggregated JOINs that Prisma ORM uses on PostgreSQL and the correlated subqueries on MySQL, the `join` strategy is likely to be more efficient in the majority of cases (a [later section](#whats-happening-under-the-hood) has more details on this). Database engines are very powerful and great at optimizing query plans. The `join` relation load strategy pays tribute to that. However, there may be cases where you still want to use the `query` strategy to perform one query per table and merge data at the application level. Depending on the dataset and the indexes that are configured in the schema, sending multiple queries could be more performant. Profiling and benchmarking your queries is crucial to identify these situations. Another consideration could be the database load that's incurred by a complex join query. If, for some reason, resources on the database server are scarce, you may want to move the heavy compute that's required by a complex join query with filters and pagination to your application servers, which may be easier to scale. TLDR: * The `join` strategy will be more efficient in most scenarios. * There may be edge cases where `query` could be more performant depending on the characteristics of the dataset and query. We recommend that you profile your database queries to identify these scenarios. * Use `query` if you want to save resources on the database server and do the heavy lifting of merging and transforming data in the application server, which might be easier to scale. ## Understanding relations in SQL databases [#understanding-relations-in-sql-databases] Now that we learned about Prisma ORM's join strategies, let's review how relation queries generally work in SQL databases. ### Flat vs nested data structures for relations [#flat-vs-nested-data-structures-for-relations] SQL databases store data in *flat* (i.e. [*normalized*](https://en.wikipedia.org/wiki/Database_normalization)) ways. Relations between entities are represented via *foreign keys* that specify references across tables. On the other hand, application developers are typically used to working with *nested* data, i.e. objects that can nest other objects arbitrarily deep. This is a huge difference, not only in the way how data is *physically* laid out on disk and in memory, but also when it comes to the *mental model* and reasoning about the data. ![](https://www.prisma.io/blog/prisma-orm-now-lets-you-choose-the-best-join-strategy-preview/imgs/9956ed7492bd6dc77e75c9f2df3db0f2aae902cd-3200x1256.png) ### Relational data needs to be "merged" for application developers [#relational-data-needs-to-be-merged-for-application-developers] Since related data is stored physically separately in the database, it needs to be *merged* somewhere to become the nested structure an application developer is familiar with. This merge is also called "join". There are two places where this join can happen: * On the **database-level**: A single SQL query is sent to the database. The query uses the `JOIN` keyword or a correlated subquery to let the database perform the join across multiple tables and returns the nested structures. * On the **application-level**: Multiple queries are sent to the database. Each query only accesses a single table and the query results are then merged in-memory in the application layer. This was the only query strategy that Prisma Client supported before v5.9.0, and it remains the default behavior when the `relationJoins` preview flag is not enabled. Which approach is more desirable depends on the database that's used, the size and characteristics of the dataset, and the complexity of the query. Read on to learn when it's recommended to use which strategy. ## What's happening under the hood? [#whats-happening-under-the-hood] Prisma ORM implements the `join` relation load strategy using `LATERAL` joins and DB-level JSON aggregation (e.g. via `json_agg`) in PostgreSQL and correlated subqueries on MySQL. In the following sections, we'll investigate why the `LATERAL` joins and DB-level JSON aggregation approach on PostgreSQL is more efficient than plain, traditional JOINs. ### Preventing redundancy in query results with JSON aggregation [#preventing-redundancy-in-query-results-with-json-aggregation] When using database-level `JOIN`s, there are several options for constructing a SQL query. Let's consider the SQL table definition for the Prisma schema from above: ```sql -- CreateTable CREATE TABLE "User" ( "id" SERIAL NOT NULL, "name" TEXT, CONSTRAINT "User_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "Post" ( "id" SERIAL NOT NULL, "title" TEXT NOT NULL, "authorId" INTEGER NOT NULL, CONSTRAINT "Post_pkey" PRIMARY KEY ("id") ); -- AddForeignKey ALTER TABLE "Post" ADD CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE; ``` To retrieve all users with their posts, you can use a simple `LEFT JOIN` query: ```sql SELECT u.id AS user_id, u.name AS user_name, p.id AS post_id, p.title AS post_title FROM "user" u LEFT JOIN post p ON u.id = p.author_id ORDER BY u.id, p.id; ``` This is what the result could look like with some sample data: ![](https://www.prisma.io/blog/prisma-orm-now-lets-you-choose-the-best-join-strategy-preview/imgs/0d275322775fddbf52e1e3e95cfabaaa9bafd778-874x798.png) Notice the redundancy in the `user_name` column in this case. This redundancy is only going to get worse the more tables are being joined. For example, assume there's another `Comment` table, where each comment has a `postId` foreign key that points to a record in the `Post` table. Here's a SQL query to represent that: ```sql SELECT u.id AS user_id, u.name AS user_name, p.id AS post_id, p.title AS post_title, c.id AS comment_id, c.body AS comment_body FROM "user" u LEFT JOIN post p ON u.id = p.author_id LEFT JOIN comment c ON p.id = c.post_id ORDER BY u.id, p.id, c.id; ``` Now, assume the first post had multiple comments: ![](https://www.prisma.io/blog/prisma-orm-now-lets-you-choose-the-best-join-strategy-preview/imgs/c6bf66d1ff9bac1bc0543020819db31da69ba26a-946x324.png) The size of the result set in this case grows exponentially with the number of tables that are being joined. Since this data goes over the wire from the database to the application server, this can become very expensive. The `join` strategy implemented by Prisma with JSON aggregation on the database-level solves this problem. Here is an example for PostgreSQL that uses `json_agg` and `json_build_object` to solve the redundancy problem and return the posts per user in JSON format: ```sql SELECT u.id AS user_id, u.name AS user_name, jsonb_agg( jsonb_build_object( 'post_id', p.id, 'post_title', p.title ) ) AS posts FROM "user" u LEFT JOIN post p ON u.id = p.author_id GROUP BY u.id, u.name ORDER BY u.id; ``` The result set this time doesn't contain redundant data. Additionally, the data structure conveniently already has the shape that's returned by Prisma Client, which saves the extra work of transforming results in the client: ![](https://www.prisma.io/blog/prisma-orm-now-lets-you-choose-the-best-join-strategy-preview/imgs/c39d9673195c2414f9ca2be1cedcb0229fa58188-1154x652.png) ### Lateral JOINs for more efficient queries with pagination and filters [#lateral-joins-for-more-efficient-queries-with-pagination-and-filters] Relation queries (like most other ones) almost never fetch the *entire* data from a table, but come with additional result set constraints like filters and pagination. Specifically pagination can become very complex with traditional JOINs, let's look at another example. Consider this Prisma Client query that fetches 10 users and 5 posts per user: ```ts await prisma.user.findMany({ take: 10, include: { posts: { take: 5, }, }, }); ``` When writing this in raw SQL, you might be tempted to use a `LIMIT` clause inside the sub-query, e.g.: ```sql SELECT "user".id, name, title FROM ("user" LEFT JOIN (SELECT * FROM post LIMIT 5) AS p ON (p.author_id = "user".id)) LIMIT 10; ``` However, this won't work because the inner `SELECT` doesn't actually return five posts *per user*. Instead, it returns at most five posts *in total*, which is of course not at all the desired outcome. Using a traditional JOIN, this could be resolved by using the `row_number()` function to assign incrementing integers to the records in the result set with which the computation of the pagination could be performed manually. This approach becomes very complex very fast though and thus isn't ideal for building paginated relation queries. ```sql SELECT "user".id, name, title, rn FROM ("user" LEFT JOIN (SELECT *, row_number() OVER (PARTITION BY author_id) AS rn FROM post) AS p ON (p.author_id = "user".id)) WHERE p.rn >= 1 AND p.rn <= 5 OR p.rn IS NULL LIMIT 10; ``` Maintaining, scaling and debugging these kinds of SQL queries is daunting and can consume hours of development time. Thankfully, newer database versions solve this with a new kind of query: the *lateral JOIN*. The above query can be simplified by using the `LATERAL` keyword: ```sql SELECT "user".id, name, title FROM ("user" LEFT JOIN LATERAL (SELECT * FROM post WHERE post.author_id = "user".id LIMIT 5) AS p ON TRUE) LIMIT 10; ``` This not only makes the query more readable, but the database engine also likely is more capable of optimizing the query because it can understand more about the *intent* of the query. ### Conclusion [#conclusion] Let's review the different options for joining data from relation queries with Prisma. With the application-level join strategy (`query`, and the behavior before v5.9.0), Prisma Client sends multiple queries to the database and does all the work of merging and transforming the results into the expected JavaScript object structures inside the client: ![](https://www.prisma.io/blog/prisma-orm-now-lets-you-choose-the-best-join-strategy-preview/imgs/c7adac832f583577880d8f6d308c7446c9d5f27c-3200x2280.png) Using plain, traditional JOINs, the merging of the data would be delegated to the database. However, as explained above, there are problems with data redundancy (the result sets grow exponentially with the number of tables in the relation query) and the complexity of queries that contain filters and pagination: ![](https://www.prisma.io/blog/prisma-orm-now-lets-you-choose-the-best-join-strategy-preview/imgs/de503bc689a9489f6d97c0151adda8f98fd910bb-3200x2390.png) To work around these issues, Prisma ORM implements modern, lateral JOINs accompanied with JSON aggregation on the database-level. That way, all the heavy lifting that's needed to resolve the query and bring the data into the expected JavaScript object structures is done on the database-level: ![](https://www.prisma.io/blog/prisma-orm-now-lets-you-choose-the-best-join-strategy-preview/imgs/87f24086f41106526cd3e6178ef56e71682d5855-1600x1140.svg) ## Joins in Prisma 8 [#joins-in-prisma-8] [Prisma 8](https://www.prisma.io/blog/prisma-next-early-access-write-your-contract-prompt-your-agent-ship-your-app), the next generation of Prisma ORM, takes the idea in this post one step further: you no longer choose a join strategy at all. In Prisma 8, the ORM picks the join strategy based on what your database supports. We verified this on an Early Access build: `db.orm.public.User.include("posts").all()` sends a single SQL query in which PostgreSQL builds the nested JSON with `json_agg` and `json_build_object`, with no preview flag and no per-query option. On targets without JSON aggregation support, Prisma 8 falls back to multiple queries with in-application merging, so the same application code picks the best available strategy per database. Prisma 8 is also fast in general: in [our published benchmark](https://www.prisma.io/blog/prisma-next-performance-benchmark), a fork of the open-source drizzle-benchmarks suite, it reaches roughly 90% of the raw `pg` driver's speed, holds p95 latency around 4 ms at 6,000 to 7,000 requests/second, and ships a client of about 148.5 KB gzipped. And when you need full control over a join, it includes a type-safe SQL query builder with explicit `leftJoin` support, kept in sync with your schema. Like Prisma ORM, Prisma 8 pairs with [Prisma Postgres](https://www.prisma.io/postgres) out of the box. For new projects, especially ones built with AI coding agents, start with Prisma 8. ## Try it out and share your feedback [#try-it-out-and-share-your-feedback] We'd love for you to try out the `join` relation load strategy. If you don't have a database at hand, `npm create db` gives you a free Prisma Postgres database to experiment with. Let us know what you think and [share your feedback with us](https://github.com/prisma/orm/discussions/22288)!