Joins

SELECT u.*, p.id AS post_id, p.title
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
WHERE u.id = 1;

# Prefetch related posts for a user
user = User.objects.prefetch_related("posts").get(id=1)
for post in user.posts.all():
    print(post.title)

# Select related author for posts (ForeignKey)
posts = Post.objects.select_related("author").all()

from sqlalchemy.orm import selectinload, joinedload
from sqlalchemy import select

with Session(engine) as session:
    # Eager load posts for a user (separate SELECT)
    user = session.scalar(
        select(User)
        .options(selectinload(User.posts))
        .where(User.id == 1)
    )

    # Eager load author for posts (JOIN)
    posts = session.scalars(
        select(Post).options(joinedload(Post.author))
    ).unique().all()

from sqlalchemy.orm import joinedload, selectinload
from sqlmodel import select

with Session(engine) as session:
    # Prefetch related posts for a user
    user = session.exec(
        select(User)
        .options(selectinload(User.posts))
        .where(User.id == 1)
    ).one()
    for post in user.posts:
        print(post.title)

    # Select related author for posts (ForeignKey)
    posts = session.exec(
        select(Post).options(joinedload(Post.author))
    ).all()

# Prefetch related posts for a user
user = await User.get(id=1).prefetch_related("posts")
for post in user.posts:
    print(post.title)

# Eager-load author (single query with JOIN)
posts = await Post.all().select_related("author")

from peewee import prefetch

# Prefetch related posts for a user
user = prefetch(
    User.select().where(User.id == 1),
    Post.select(),
)[0]
for post in user.posts:
    print(post.title)

# Select related author for posts (ForeignKey)
posts = (
    Post.select(Post, User)
    .join(User, on=(Post.author == User.id))
)

// Include related posts
const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: { posts: true },
});

// Include author on posts
const posts = await prisma.post.findMany({
  include: { author: true },
});

// Using relational queries (requires relations defined)
const user = await db.query.users.findFirst({
  where: eq(users.id, 1),
  with: { posts: true },
});

// Manual join
const result = await db.select()
  .from(posts)
  .leftJoin(users, eq(posts.authorId, users.id));

// Kysely doesn't hydrate relation graphs; fetch separately.
const user = await db
  .selectFrom('users')
  .selectAll()
  .where('id', '=', 1)
  .executeTakeFirst()

const posts = await db
  .selectFrom('posts')
  .selectAll()
  .where('author_id', '=', 1)
  .execute()

// Fetch posts with author in one query (JOIN)
const postsWithAuthor = await db
  .selectFrom('posts as p')
  .innerJoin('users as u', 'u.id', 'p.author_id')
  .select(['p.title', 'u.name as author_name'])
  .execute()

// Find with relations
const user = await userRepository.findOne({
  where: { id: 1 },
  relations: { posts: true },
});

// Find posts with author
const posts = await postRepository.find({
  relations: { author: true },
});

const em = orm.em.fork();

// Prefetch related posts for a user
const user = await em.findOne(User, { id: 1 }, {
  populate: ["posts"],
});
for (const post of user?.posts ?? []) {
  console.log(post.title);
}

// Select related author for posts (ForeignKey)
const posts = await em.find(Post, {}, {
  populate: ["author"],
});

// Prefetch related posts for a user
const user = await User.findByPk(1, {
  include: [{ model: Post, as: "posts" }],
});
for (const post of user?.posts ?? []) {
  console.log(post.title);
}

// Select related author for posts (ForeignKey)
const posts = await Post.findAll({
  include: [{ model: User, as: "author" }],
});

-- Posts by a specific author name
SELECT p.* FROM posts p
JOIN users u ON u.id = p.author_id
WHERE u.name = 'Alice';

-- Users who have at least one post
SELECT DISTINCT u.* FROM users u
JOIN posts p ON p.author_id = u.id;

# Posts by author name (spanning relationships)
posts = Post.objects.filter(author__name="Alice")

# Users who have at least one post
users = User.objects.filter(posts__isnull=False).distinct()

from sqlalchemy import select

with Session(engine) as session:
    # Posts by author name
    posts = session.scalars(
        select(Post).join(Post.author).where(User.name == "Alice")
    ).all()

    # Users who have at least one post
    users = session.scalars(
        select(User).where(User.posts.any())
    ).all()

from sqlmodel import select

with Session(engine) as session:
    # Posts by author name (spanning relationships)
    posts = session.exec(
        select(Post)
        .join(User, Post.author_id == User.id)
        .where(User.name == "Alice")
    ).all()

    # Users who have at least one post
    users = session.exec(
        select(User).where(User.posts.any())
    ).all()

# Posts by author name (spanning relationships)
posts = await Post.filter(author__name="Alice")

# Users who have at least one post
users = await User.filter(
    posts__isnull=False
).distinct()

# Posts by author name (spanning relationships)
posts = (
    Post.select()
    .join(User, on=(Post.author == User.id))
    .where(User.name == "Alice")
)

# Users who have at least one post
users = (
    User.select()
    .join(Post, on=(Post.author == User.id))
    .distinct()
)

// Posts by author name
const posts = await prisma.post.findMany({
  where: { author: { name: "Alice" } },
});

// Users who have at least one post
const users = await prisma.user.findMany({
  where: { posts: { some: {} } },
});

// Posts by author name
const result = await db.select()
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(eq(users.name, "Alice"));

// Users who have at least one post
const result2 = await db.selectDistinct({ id: users.id, name: users.name })
  .from(users)
  .innerJoin(posts, eq(posts.authorId, users.id));

// Posts by author name (spanning relationships)
const posts = await db
  .selectFrom('posts as p')
  .innerJoin('users as u', 'u.id', 'p.author_id')
  .selectAll('p')
  .where('u.name', '=', 'Alice')
  .execute()

// Users who have at least one post
const users = await db
  .selectFrom('users as u')
  .innerJoin('posts as p', 'p.author_id', 'u.id')
  .selectAll('u')
  .distinct()
  .execute()

// Posts by author name
const posts = await postRepository
  .createQueryBuilder("post")
  .innerJoinAndSelect("post.author", "author")
  .where("author.name = :name", { name: "Alice" })
  .getMany();

// Users who have at least one post
const users = await userRepository
  .createQueryBuilder("user")
  .innerJoin("user.posts", "post")
  .getMany();

const em = orm.em.fork();

// Posts by author name (spanning relationships)
const posts = await em.find(Post, {
  author: { name: "Alice" },
}, {
  populate: ["author"],
});

// Users who have at least one post
const users = await em
  .createQueryBuilder(User, "u")
  .select("u.*")
  .join("u.posts", "p")
  .groupBy("u.id")
  .getResultList();

// Posts by author name (spanning relationships)
const posts = await Post.findAll({
  include: [
    {
      model: User,
      as: "author",
      where: { name: "Alice" },
    },
  ],
});

// Users who have at least one post
const users = await User.findAll({
  include: [{ model: Post, as: "posts", required: true }],
  distinct: true,
});

SELECT u.name, p.title
FROM users u
INNER JOIN posts p ON p.author_id = u.id;

# Django uses implicit joins via lookups
posts = Post.objects.select_related("author").values(
    "author__name", "title"
)

from sqlalchemy import select

with Session(engine) as session:
    rows = session.execute(
        select(User.name, Post.title)
        .join(Post, User.id == Post.author_id)
    ).all()

from sqlmodel import select

with Session(engine) as session:
    # SQLModel uses explicit join expressions
    posts = session.exec(
        select(User.name, Post.title)
        .join(Post, User.id == Post.author_id)
    ).all()

# Tortoise uses implicit joins via lookups
posts = await Post.all().select_related(
    "author"
).values("author__name", "title")

# Peewee uses explicit join expressions
posts = (
    Post.select(User.name.alias("author_name"), Post.title)
    .join(User, on=(Post.author == User.id))
)

// Prisma doesn't expose raw joins;
// use include to fetch related data
const posts = await prisma.post.findMany({
  include: { author: true },
});

// Access joined data
posts.forEach((p) => console.log(p.author.name, p.title));

const result = await db
  .select({ name: users.name, title: posts.title })
  .from(users)
  .innerJoin(posts, eq(posts.authorId, users.id));

const posts = await db
  .selectFrom('posts as p')
  .innerJoin('users as u', 'u.id', 'p.author_id')
  .select(['u.name as author_name', 'p.title'])
  .execute()

const result = await userRepository
  .createQueryBuilder("user")
  .innerJoinAndSelect("user.posts", "post")
  .select(["user.name", "post.title"])
  .getRawMany();

// MikroORM uses explicit joins via QueryBuilder
const posts = await orm.em
  .createQueryBuilder(Post, "p")
  .select(["a.name as author_name", "p.title"])
  .join("p.author", "a")
  .execute();

// Sequelize uses include + required for INNER JOIN
const posts = await Post.findAll({
  attributes: ["title"],
  include: [
    {
      model: User,
      as: "author",
      attributes: ["name"],
      required: true,
    },
  ],
});

-- All users, even those without posts
SELECT u.name, p.title
FROM users u
LEFT JOIN posts p ON p.author_id = u.id;

# prefetch_related uses a separate query (like LEFT JOIN behavior)
users = User.objects.prefetch_related("posts").all()
for user in users:
    posts = user.posts.all()  # empty list if no posts

from sqlalchemy import select

with Session(engine) as session:
    rows = session.execute(
        select(User.name, Post.title)
        .outerjoin(Post, User.id == Post.author_id)
    ).all()

from sqlmodel import select

with Session(engine) as session:
    # Outer join keeps users with no posts
    rows = session.exec(
        select(User.name, Post.title)
        .outerjoin(Post, User.id == Post.author_id)
    ).all()

# prefetch_related uses separate query (LEFT JOIN behavior)
users = await User.all().prefetch_related("posts")
for user in users:
    posts = user.posts  # empty list if no posts

from peewee import JOIN

# LEFT JOIN includes users with no posts
users = (
    User.select(User, Post)
    .join(Post, JOIN.LEFT_OUTER, on=(Post.author == User.id))
)

// include acts like a left join — returns users even without posts
const users = await prisma.user.findMany({
  include: { posts: true },
});

// users without posts have posts: []

const result = await db
  .select({ name: users.name, title: posts.title })
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id));

const users = await db
  .selectFrom('users as u')
  .leftJoin('posts as p', 'p.author_id', 'u.id')
  .select(['u.name', 'p.title'])
  .execute()

const result = await userRepository
  .createQueryBuilder("user")
  .leftJoinAndSelect("user.posts", "post")
  .getMany();

// users without posts have posts: []

// populate uses separate query (LEFT JOIN behavior)
const users = await orm.em.find(User, {}, {
  populate: ["posts"],
});
for (const user of users) {
  const posts = user.posts; // empty collection if no posts
}

// include with required=false behaves like LEFT JOIN
const users = await User.findAll({
  include: [{ model: Post, as: "posts", required: false }],
});
for (const user of users) {
  const posts = user.posts ?? []; // empty array if no posts
}

-- Posts with author and tags (two joins)
SELECT p.title, u.name AS author, t.name AS tag
FROM posts p
JOIN users u ON u.id = p.author_id
LEFT JOIN post_tags pt ON pt.post_id = p.id
LEFT JOIN tags t ON t.id = pt.tag_id;

# Fetch posts with author and tags
posts = Post.objects.select_related("author").prefetch_related("tags").all()
for post in posts:
    print(post.author.name, [t.name for t in post.tags.all()])

from sqlalchemy.orm import selectinload, joinedload
from sqlalchemy import select

with Session(engine) as session:
    posts = session.scalars(
        select(Post)
        .options(
            joinedload(Post.author),
            selectinload(Post.tags),
        )
    ).unique().all()

from sqlalchemy.orm import joinedload, selectinload
from sqlmodel import select

with Session(engine) as session:
    # Fetch posts with author and tags
    posts = session.exec(
        select(Post).options(
            joinedload(Post.author),
            selectinload(Post.tags),
        )
    ).all()
    for post in posts:
        print(post.author.name, [t.name for t in post.tags])

# Fetch posts with author and tags
posts = await Post.all().select_related(
    "author"
).prefetch_related("tags")
for post in posts:
    print(post.author.name, [t.name for t in post.tags])

from peewee import JOIN

# Fetch posts with author and tags
rows = (
    Post.select(Post, User, Tag)
    .join(User, on=(Post.author == User.id))
    .switch(Post)
    .join(PostTag, JOIN.LEFT_OUTER, on=(PostTag.post == Post.id))
    .join(Tag, JOIN.LEFT_OUTER, on=(PostTag.tag == Tag.id))
)

const posts = await prisma.post.findMany({
  include: {
    author: true,
    tags: true,
  },
});

const result = await db.query.posts.findMany({
  with: {
    author: true,
    tags: true,
  },
});

const rows = await db
  .selectFrom('posts as p')
  .innerJoin('users as u', 'u.id', 'p.author_id')
  .leftJoin('post_tags as pt', 'pt.post_id', 'p.id')
  .leftJoin('tags as t', 't.id', 'pt.tag_id')
  .select([
    'p.id as post_id',
    'p.title',
    'u.name as author_name',
    't.name as tag_name',
  ])
  .execute()

const posts = await postRepository.find({
  relations: {
    author: true,
    tags: true,
  },
});

// Fetch posts with author and tags
const posts = await orm.em.find(Post, {}, {
  populate: ["author", "tags"],
});
for (const post of posts) {
  console.log(
    post.author.name,
    post.tags.getItems().map((t) => t.name),
  );
}

// Fetch posts with author and tags
const posts = await Post.findAll({
  include: [
    { model: User, as: "author" },
    { model: Tag, as: "tags" },
  ],
});
for (const post of posts) {
  console.log(post.author?.name, post.tags?.map((t) => t.name));
}

-- Count posts per author
SELECT u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
GROUP BY u.id, u.name
ORDER BY post_count DESC;

from django.db.models import Count

users = (
    User.objects
    .annotate(post_count=Count("posts"))
    .order_by("-post_count")
)
for user in users:
    print(user.name, user.post_count)

from sqlalchemy import select, func

with Session(engine) as session:
    rows = session.execute(
        select(User.name, func.count(Post.id).label("post_count"))
        .outerjoin(Post, User.id == Post.author_id)
        .group_by(User.id, User.name)
        .order_by(func.count(Post.id).desc())
    ).all()

from sqlalchemy import func
from sqlmodel import select

with Session(engine) as session:
    users = session.exec(
        select(User.name, func.count(Post.id).label("post_count"))
        .outerjoin(Post, User.id == Post.author_id)
        .group_by(User.id, User.name)
        .order_by(func.count(Post.id).desc())
    ).all()

from tortoise.functions import Count

users = await User.annotate(
    post_count=Count("posts")
).order_by("-post_count").values("name", "post_count")

from peewee import JOIN, fn

users = (
    User.select(
        User.name,
        fn.COUNT(Post.id).alias("post_count"),
    )
    .join(Post, JOIN.LEFT_OUTER, on=(Post.author == User.id))
    .group_by(User.id, User.name)
    .order_by(fn.COUNT(Post.id).desc())
)

const postCounts = await prisma.post.groupBy({
  by: ["authorId"],
  _count: { id: true },
  orderBy: { _count: { id: "desc" } },
});

// Fetch user names for the results
const users = await prisma.user.findMany({
  where: { id: { in: postCounts.map((p) => p.authorId) } },
});

const result = await db
  .select({
    name: users.name,
    postCount: count(posts.id),
  })
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id))
  .groupBy(users.id, users.name)
  .orderBy(desc(count(posts.id)));

import { sql } from 'kysely'

const users = await db
  .selectFrom('users as u')
  .leftJoin('posts as p', 'p.author_id', 'u.id')
  .select([
    'u.name',
    sql<number>`count(p.id)`.as('post_count'),
  ])
  .groupBy(['u.id', 'u.name'])
  .orderBy('post_count', 'desc')
  .execute()

const result = await userRepository
  .createQueryBuilder("user")
  .leftJoin("user.posts", "post")
  .select("user.name", "name")
  .addSelect("COUNT(post.id)", "postCount")
  .groupBy("user.id")
  .addGroupBy("user.name")
  .orderBy("postCount", "DESC")
  .getRawMany();

const users = await orm.em
  .createQueryBuilder(User, "u")
  .select(["u.name", "count(p.id) as post_count"])
  .leftJoin("u.posts", "p")
  .groupBy(["u.id", "u.name"])
  .orderBy({ post_count: "desc" })
  .execute();

import { col, fn } from "sequelize";

const users = await User.findAll({
  attributes: [
    "name",
    [fn("COUNT", col("posts.id")), "postCount"],
  ],
  include: [{ model: Post, as: "posts", attributes: [], required: false }],
  group: ["User.id", "User.name"],
  order: [[fn("COUNT", col("posts.id")), "DESC"]],
});

-- Users with more than 5 posts
SELECT u.name, COUNT(p.id) AS post_count
FROM users u
JOIN posts p ON p.author_id = u.id
GROUP BY u.id, u.name
HAVING COUNT(p.id) > 5;

from django.db.models import Count

users = (
    User.objects
    .annotate(post_count=Count("posts"))
    .filter(post_count__gt=5)
)

from sqlalchemy import select, func

with Session(engine) as session:
    rows = session.execute(
        select(User.name, func.count(Post.id).label("post_count"))
        .join(Post, User.id == Post.author_id)
        .group_by(User.id, User.name)
        .having(func.count(Post.id) > 5)
    ).all()

from sqlalchemy import func
from sqlmodel import select

with Session(engine) as session:
    users = session.exec(
        select(User.name, func.count(Post.id).label("post_count"))
        .join(Post, User.id == Post.author_id)
        .group_by(User.id, User.name)
        .having(func.count(Post.id) > 5)
    ).all()

from tortoise.functions import Count

users = await User.annotate(
    post_count=Count("posts")
).filter(post_count__gt=5)

from peewee import fn

users = (
    User.select(
        User.name,
        fn.COUNT(Post.id).alias("post_count"),
    )
    .join(Post, on=(Post.author == User.id))
    .group_by(User.id, User.name)
    .having(fn.COUNT(Post.id) > 5)
)

// Prisma has no HAVING clause; use raw SQL
const active = await prisma.$queryRaw<
  { name: string; post_count: bigint }[]
>`
  SELECT u.name, COUNT(p.id) AS post_count
  FROM users u
  JOIN posts p ON p.author_id = u.id
  GROUP BY u.id, u.name
  HAVING COUNT(p.id) > 5
`;

const result = await db
  .select({
    name: users.name,
    postCount: count(posts.id),
  })
  .from(users)
  .innerJoin(posts, eq(posts.authorId, users.id))
  .groupBy(users.id, users.name)
  .having(gt(count(posts.id), 5));

import { sql } from 'kysely'

const users = await db
  .selectFrom('users as u')
  .innerJoin('posts as p', 'p.author_id', 'u.id')
  .select([
    'u.name',
    sql<number>`count(p.id)`.as('post_count'),
  ])
  .groupBy(['u.id', 'u.name'])
  .having(sql`count(p.id)`, '>', 5)
  .execute()

const result = await userRepository
  .createQueryBuilder("user")
  .innerJoin("user.posts", "post")
  .select("user.name", "name")
  .addSelect("COUNT(post.id)", "postCount")
  .groupBy("user.id")
  .addGroupBy("user.name")
  .having("COUNT(post.id) > :min", { min: 5 })
  .getRawMany();

const users = await orm.em
  .createQueryBuilder(User, "u")
  .select(["u.name", "count(p.id) as post_count"])
  .join("u.posts", "p")
  .groupBy(["u.id", "u.name"])
  .having("count(p.id) > ?", [5])
  .execute();

import { Op, col, fn, where } from "sequelize";

const users = await User.findAll({
  attributes: [
    "name",
    [fn("COUNT", col("posts.id")), "postCount"],
  ],
  include: [{ model: Post, as: "posts", attributes: [], required: true }],
  group: ["User.id", "User.name"],
  having: where(fn("COUNT", col("posts.id")), Op.gt, 5),
});