join - 🌲 Sonamu

join and leftJoin are methods for connecting multiple tables to query relational data. They support type-safe table joins and complex join conditions.

Basic Usage

INNER JOIN

const posts = await puri.table("posts").join("users", "posts.user_id", "users.id").select({
  post_id: "posts.id",
  title: "posts.title",
  author_name: "users.name",
});

// SELECT posts.id as post_id, posts.title, users.name as author_name
// FROM posts
// INNER JOIN users ON posts.user_id = users.id

LEFT JOIN

const posts = await puri.table("posts").leftJoin("users", "posts.user_id", "users.id").select({
  post_id: "posts.id",
  title: "posts.title",
  author_name: "users.name", // nullable
});

// SELECT posts.id as post_id, posts.title, users.name as author_name
// FROM posts
// LEFT JOIN users ON posts.user_id = users.id

INNER JOIN: Returns only matching records from both tables LEFT JOIN: Returns all records from the left table (right side can be null)

Table Aliases

Using Aliases

const posts = await puri.table("posts").join({ u: "users" }, "posts.user_id", "u.id").select({
  post_id: "posts.id",
  author_name: "u.name", // Using alias
});

// FROM posts
// INNER JOIN users AS u ON posts.user_id = u.id

Joining Same Table Multiple Times

const posts = await puri
  .table("posts")
  .join({ author: "users" }, "posts.author_id", "author.id")
  .leftJoin({ editor: "users" }, "posts.editor_id", "editor.id")
  .select({
    post_id: "posts.id",
    author_name: "author.name",
    editor_name: "editor.name", // nullable
  });

// FROM posts
// INNER JOIN users AS author ON posts.author_id = author.id
// LEFT JOIN users AS editor ON posts.editor_id = editor.id

Complex Join Conditions

Using Callback Function

You can combine multiple conditions.

const result = await puri
  .table("orders")
  .join("users", (j) => {
    j.on("orders.user_id", "users.id").on("orders.country", "users.country");
  })
  .select({
    order_id: "orders.id",
    user_name: "users.name",
  });

// FROM orders
// INNER JOIN users ON orders.user_id = users.id
//                  AND orders.country = users.country

OR Conditions

const result = await puri
  .table("products")
  .leftJoin("categories", (j) => {
    j.on("products.category_id", "categories.id").orOn(
      "products.parent_category_id",
      "categories.id",
    );
  })
  .select({
    product_name: "products.name",
    category_name: "categories.name",
  });

// LEFT JOIN categories ON products.category_id = categories.id
//                      OR products.parent_category_id = categories.id

Comparison with Values

Use onVal() instead of on() when comparing a column with a literal value. on() treats its arguments as column references, so onVal() is required to bind values as parameters.

const result = await puri
  .table("orders")
  .join("users", (j) => {
    j.on("orders.user_id", "users.id")
      .onVal("users.status", "=", "active") // Bind value as parameter
      .onVal("orders.created_at", ">", new Date("2024-01-01"));
  })
  .selectAll();

Value Binding (onVal)

onVal safely compares a column with a literal value using parameter binding in join conditions. While on is used for column-to-column comparisons, onVal is used for column-to-value comparisons.

const result = await puri
  .table("orders")
  .join("users", (j) => {
    j.on("orders.user_id", "users.id").onVal("users.status", "active"); // Parameter binding
  })
  .select({
    order_id: "orders.id",
    user_name: "users.name",
  });

// INNER JOIN users ON orders.user_id = users.id
//                  AND users.status = ?    -- 'active' bound as parameter

You can also specify an operator.

const result = await puri
  .table("orders")
  .join("shipments", (j) => {
    j.on("orders.id", "shipments.order_id").onVal("shipments.status", "!=", "cancelled");
  })
  .selectAll();

// INNER JOIN shipments ON orders.id = shipments.order_id
//                       AND shipments.status != ?   -- 'cancelled'

andOnVal / orOnVal

andOnVal is an explicit AND version of onVal, and orOnVal compares values with OR conditions.

const result = await puri
  .table("products")
  .leftJoin("promotions", (j) => {
    j.on("products.id", "promotions.product_id")
      .onVal("promotions.active", true)
      .orOnVal("promotions.featured", true);
  })
  .select({
    product_name: "products.name",
    promo_name: "promotions.name",
  });

// LEFT JOIN promotions ON products.id = promotions.product_id
//                       AND promotions.active = ?      -- true
//                       OR promotions.featured = ?     -- true

on vs onVal: on("col1", "col2") compares two columns. onVal("col", value) compares a column with a value, binding the value as a SQL parameter.

Subquery Joins

You can join subqueries as tables.

// Create subquery
const recentOrders = puri
  .table("orders")
  .where("orders.created_at", ">", new Date("2024-01-01"))
  .select({
    user_id: "orders.user_id",
    order_count: Puri.count(),
  })
  .groupBy("orders.user_id");

// Join subquery
const result = await puri
  .table("users")
  .join(
    { ro: recentOrders }, // Alias required
    "users.id",
    "ro.user_id",
  )
  .select({
    user_id: "users.id",
    user_name: "users.name",
    order_count: "ro.order_count",
  });

// FROM users
// INNER JOIN (
//   SELECT user_id, COUNT(*) as order_count
//   FROM orders
//   WHERE created_at > '2024-01-01'
//   GROUP BY user_id
// ) AS ro ON users.id = ro.user_id

Multiple Table Joins

You can sequentially join multiple tables.

const posts = await puri
  .table("posts")
  .join("users", "posts.user_id", "users.id")
  .leftJoin("categories", "posts.category_id", "categories.id")
  .leftJoin("tags", "posts.id", "tags.post_id")
  .select({
    // posts
    post_id: "posts.id",
    title: "posts.title",

    // users
    author_name: "users.name",
    author_email: "users.email",

    // categories (nullable)
    category_name: "categories.name",

    // tags (nullable)
    tag_name: "tags.name",
  });

// FROM posts
// INNER JOIN users ON posts.user_id = users.id
// LEFT JOIN categories ON posts.category_id = categories.id
// LEFT JOIN tags ON posts.id = tags.post_id

Real-World Examples

Posts + Authors
Orders + Users + Products
Statistics Query
Self Join
Subquery Join

async function getPostsWithAuthors() {
  return puri.table("posts")
    .join("users", "posts.user_id", "users.id")
    .where("posts.published", true)
    .select({
      // Post information
      id: "posts.id",
      title: "posts.title",
      content: "posts.content",
      created_at: "posts.created_at",

      // Author information
      author: {
        id: "users.id",
        name: "users.name",
        email: "users.email"
      }
    })
    .orderBy("posts.created_at", "desc")
    .limit(20);
}

async function getOrderDetails(orderId: number) {
  return puri.table("orders")
    .join("users", "orders.user_id", "users.id")
    .join("order_items", "orders.id", "order_items.order_id")
    .join("products", "order_items.product_id", "products.id")
    .where("orders.id", orderId)
    .select({
      // Order information
      order_id: "orders.id",
      order_status: "orders.status",
      order_date: "orders.created_at",

      // User information
      customer: {
        name: "users.name",
        email: "users.email",
        phone: "users.phone"
      },

      // Product information
      items: {
        product_id: "products.id",
        product_name: "products.name",
        quantity: "order_items.quantity",
        price: "order_items.price"
      }
    });
}

async function getUserStatistics() {
  return puri.table("users")
    .leftJoin("posts", "users.id", "posts.user_id")
    .leftJoin("comments", "users.id", "comments.user_id")
    .select({
      user_id: "users.id",
      user_name: "users.name",

      // Post count
      post_count: Puri.rawNumber(
        "COUNT(DISTINCT posts.id)"
      ),

      // Comment count
      comment_count: Puri.rawNumber(
        "COUNT(DISTINCT comments.id)"
      ),

      // Last activity
      last_activity: Puri.rawDate(
        "GREATEST(MAX(posts.created_at), MAX(comments.created_at))"
      )
    })
    .groupBy("users.id", "users.name")
    .having("post_count", ">", 0)
    .orderBy("post_count", "desc");
}

async function getUsersWithManagers() {
  return puri.table({ emp: "users" })
    .leftJoin(
      { mgr: "users" },
      "emp.manager_id",
      "mgr.id"
    )
    .select({
      // Employee information
      employee_id: "emp.id",
      employee_name: "emp.name",
      employee_email: "emp.email",

      // Manager information (nullable)
      manager: {
        id: "mgr.id",
        name: "mgr.name",
        email: "mgr.email"
      }
    })
    .orderBy("emp.name", "asc");
}

async function getTopContributors(limit: number = 10) {
  // Calculate post count per user (subquery)
  const postCounts = puri.table("posts")
    .where("posts.published", true)
    .select({
      user_id: "posts.user_id",
      post_count: Puri.count()
    })
    .groupBy("posts.user_id");

  // Join with user information
  return puri.table("users")
    .join(
      { pc: postCounts },
      "users.id",
      "pc.user_id"
    )
    .select({
      id: "users.id",
      name: "users.name",
      email: "users.email",
      post_count: "pc.post_count"
    })
    .orderBy("pc.post_count", "desc")
    .limit(limit);
}

Join Condition Grouping

You can group complex join conditions.

const result = await puri
  .table("products")
  .leftJoin("discounts", (j) => {
    j.on("products.id", "discounts.product_id").on((nested) => {
      nested
        .onVal("discounts.start_date", "<=", new Date())
        .onVal("discounts.end_date", ">=", new Date());
    });
  })
  .select({
    product_id: "products.id",
    product_name: "products.name",
    discount_rate: "discounts.rate",
  });

// LEFT JOIN discounts ON products.id = discounts.product_id
//   AND (
//     discounts.start_date <= NOW()
//     AND discounts.end_date >= NOW()
//   )

NULL Handling (LEFT JOIN)

LEFT JOIN results are automatically inferred as nullable types.

const posts = await puri.table("posts").leftJoin("users", "posts.user_id", "users.id").select({
  post_id: "posts.id",
  title: "posts.title",
  author_name: "users.name", // string | null
});

// Type check
posts.forEach((post) => {
  if (post.author_name !== null) {
    console.log(post.author_name.toUpperCase());
  }
});

Performance Optimization

1. Select Only Needed Columns

// ❌ Bad: All columns
.join("users", ...)
.selectAll()

// ✅ Good: Only needed columns
.join("users", ...)
.select({
  post_id: "posts.id",
  author_name: "users.name"  // Only what's needed
})

2. Join Order

// ✅ Good: Join smaller tables first
.table("posts")        // Large table
.join("users", ...)    // Small table
.join("categories", ...)

// Use indexes in join conditions
.join("users", "posts.user_id", "users.id")  // users.id is PK (indexed)

3. LEFT JOIN vs INNER JOIN

// LEFT JOIN is slower than INNER JOIN
// Use INNER JOIN if relationship must exist

// ✅ If user_id is NOT NULL, use INNER JOIN
.join("users", "posts.user_id", "users.id")

// ⚠️ If user_id can be NULL, use LEFT JOIN
.leftJoin("users", "posts.user_id", "users.id")

Type Safety

Columns from joined tables support automatic type completion and validation.

const posts = await puri.table("posts").join("users", "posts.user_id", "users.id").select({
  post_id: "posts.id", // ✅ OK
  user_name: "users.name", // ✅ OK

  // ❌ Type error: Non-existent column
  unknown: "posts.unknown_field",

  // ❌ Type error: Non-joined table
  category: "categories.name",
});

Cautions

1. Alias Required (Subqueries)

// ❌ Error: Subquery needs alias
.join(subquery, ...)

// ✅ Correct
.join({ sq: subquery }, ...)

2. Check Join Condition Tables

// ❌ Wrong: Referencing non-joined table
.table("posts")
.join("users", "comments.user_id", "users.id")  // No comments table

// ✅ Correct
.table("posts")
.join("users", "posts.user_id", "users.id")

3. Duplicate Column Names

// ⚠️ Warning: Column name collision
.table("posts")
.join("users", ...)
.select({
  id: "posts.id",  // ❌ Will be overwritten
  id: "users.id"   // ❌
})

// ✅ Correct: Use aliases
.select({
  post_id: "posts.id",
  user_id: "users.id"
})

4. N+1 Problem

// ❌ Bad: N+1 queries
for (const post of posts) {
  const user = await puri.table("users").where("users.id", post.user_id).first();
}

// ✅ Good: JOIN once
const posts = await puri.table("posts").join("users", "posts.user_id", "users.id").select({
  post_id: "posts.id",
  user_name: "users.name",
});

JOIN vs Loader

Puri provides two methods for loading relational data.

JOIN (1:1 Relationship)

// ✅ Suitable: 1:1 or N:1
.table("posts")
.join("users", "posts.user_id", "users.id")

Loader (1:N Relationship)

// ✅ Suitable: 1:N or N:M
// Handle with Subset Loader (separate query)

JOIN multiplies rows, so use Loader for 1:N relationships.

Next Steps

where

Filter conditions

select

Select fields to retrieve

order-by

Sort results

Subset

Load relational data with Subsets

​Basic Usage

​INNER JOIN

​LEFT JOIN

​Table Aliases

​Using Aliases

​Joining Same Table Multiple Times

​Complex Join Conditions

​Using Callback Function

​OR Conditions

​Comparison with Values

​Value Binding (onVal)

​andOnVal / orOnVal

​Subquery Joins

​Multiple Table Joins

​Real-World Examples

​Join Condition Grouping

​NULL Handling (LEFT JOIN)

​Performance Optimization

​1. Select Only Needed Columns

​2. Join Order

​3. LEFT JOIN vs INNER JOIN

​Type Safety

​Cautions

​1. Alias Required (Subqueries)

​2. Check Join Condition Tables

​3. Duplicate Column Names

​4. N+1 Problem

​JOIN vs Loader

​JOIN (1:1 Relationship)

​Loader (1:N Relationship)

​Next Steps

where

select

order-by

Subset

Basic Usage

INNER JOIN

LEFT JOIN

Table Aliases

Using Aliases

Joining Same Table Multiple Times

Complex Join Conditions

Using Callback Function

OR Conditions

Comparison with Values

Value Binding (onVal)

andOnVal / orOnVal

Subquery Joins

Multiple Table Joins

Real-World Examples

Join Condition Grouping

NULL Handling (LEFT JOIN)

Performance Optimization

1. Select Only Needed Columns

2. Join Order

3. LEFT JOIN vs INNER JOIN

Type Safety

Cautions

1. Alias Required (Subqueries)

2. Check Join Condition Tables

3. Duplicate Column Names

4. N+1 Problem

JOIN vs Loader

JOIN (1:1 Relationship)

Loader (1:N Relationship)

Next Steps