> ## Documentation Index
> Fetch the complete documentation index at: https://sonamu.cartanova.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# join

> Joining Tables

`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

```typescript theme={null}
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

```typescript theme={null}
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
```

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

## Table Aliases

### Using Aliases

```typescript theme={null}
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

```typescript theme={null}
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.

```typescript theme={null}
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

```typescript theme={null}
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.

```typescript theme={null}
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.

```typescript theme={null}
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.

```typescript theme={null}
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.

```typescript theme={null}
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
```

<Info>
  **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.
</Info>

## Subquery Joins

You can join subqueries as tables.

```typescript theme={null}
// 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.

```typescript theme={null}
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

<Tabs>
  <Tab title="Posts + Authors" icon="file-pen">
    ```typescript theme={null}
    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);
    }
    ```
  </Tab>

  <Tab title="Orders + Users + Products" icon="cart-shopping">
    ```typescript theme={null}
    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"
          }
        });
    }
    ```
  </Tab>

  <Tab title="Statistics Query" icon="chart-bar">
    ```typescript theme={null}
    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");
    }
    ```
  </Tab>

  <Tab title="Self Join" icon="users">
    ```typescript theme={null}
    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");
    }
    ```
  </Tab>

  <Tab title="Subquery Join" icon="layer-group">
    ```typescript theme={null}
    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);
    }
    ```
  </Tab>
</Tabs>

## Join Condition Grouping

You can group complex join conditions.

```typescript theme={null}
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.

```typescript theme={null}
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

```typescript theme={null}
// ❌ 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

```typescript theme={null}
// ✅ 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

```typescript theme={null}
// 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.

```typescript theme={null}
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)

```typescript theme={null}
// ❌ Error: Subquery needs alias
.join(subquery, ...)

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

### 2. Check Join Condition Tables

```typescript theme={null}
// ❌ 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

```typescript theme={null}
// ⚠️ 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

```typescript theme={null}
// ❌ 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)

```typescript theme={null}
// ✅ Suitable: 1:1 or N:1
.table("posts")
.join("users", "posts.user_id", "users.id")
```

### Loader (1:N Relationship)

```typescript theme={null}
// ✅ Suitable: 1:N or N:M
// Handle with Subset Loader (separate query)
```

<Info>JOIN multiplies rows, so use **Loader for 1:N relationships**.</Info>

## Next Steps

<CardGroup cols={2}>
  <Card title="where" icon="filter" href="/en/api-reference/puri-methods/where">
    Filter conditions
  </Card>

  <Card title="select" icon="list-check" href="/en/api-reference/puri-methods/select">
    Select fields to retrieve
  </Card>

  <Card title="order-by" icon="arrow-down-1-9" href="/en/api-reference/puri-methods/order-by">
    Sort results
  </Card>

  <Card title="Subset" icon="layer-group" href="/en/database/subsets/what-are-subsets">
    Load relational data with Subsets
  </Card>
</CardGroup>