Joins - 🌲 Sonamu

Puri can join multiple tables to query related data together. This document explains INNER JOIN, LEFT JOIN, and complex join patterns.

Join Overview

INNER JOIN

Data existing in both tables join()

LEFT JOIN

Left table based, right is Optional leftJoin()

Self JOIN

Join same table Using alias

M:N JOIN

Many-to-many relationship join Using junction table

INNER JOIN

Queries only data that exists in both tables.

Basic JOIN

const results = await db.table("employees").join("users", "employees.user_id", "users.id").select({
  employeeId: "employees.id",
  employeeNumber: "employees.employee_number",
  userName: "users.username",
  userEmail: "users.email",
});

// SQL:
// SELECT ...
// FROM employees
// INNER JOIN users ON employees.user_id = users.id

When to use INNER JOIN: - When the relationship is required (NOT NULL foreign key) - When data must exist in both tables - Example: employees → users (all employees must have a user account)

Multiple Table JOIN

const results = await db
  .table("employees")
  .join("users", "employees.user_id", "users.id")
  .join("departments", "employees.department_id", "departments.id")
  .join("companies", "departments.company_id", "companies.id")
  .select({
    employeeName: "users.username",
    departmentName: "departments.name",
    companyName: "companies.name",
  });

// employees → users (required)
// employees → departments (required)
// departments → companies (required)

LEFT JOIN

Includes all data from the left table, and includes if available from the right table.

Basic LEFT JOIN

const results = await db
  .table("employees")
  .leftJoin("departments", "employees.department_id", "departments.id")
  .select({
    employeeId: "employees.id",
    employeeNumber: "employees.employee_number",
    departmentName: "departments.name", // Can be null
  });

// SQL:
// SELECT ...
// FROM employees
// LEFT JOIN departments ON employees.department_id = departments.id

When to use LEFT JOIN: - When the relationship is optional (NULLABLE foreign key) - When all data from the left table is needed - Example: employees → departments (some employees may not have a department)

INNER JOIN vs LEFT JOIN Comparison

const results = await db
  .table("employees")
  .join("departments", "employees.department_id", "departments.id")
  .select({
    employeeId: "employees.id",
    departmentName: "departments.name",
  });

// Only employees with departments (excludes employees without department)

const results = await db
  .table("employees")
  .leftJoin("departments", "employees.department_id", "departments.id")
  .select({
    employeeId: "employees.id",
    departmentName: "departments.name", // Can be null
  });

// All employees (includes those without department, department_name is null)

Complex LEFT JOIN

const results = await db
  .table("employees")
  .leftJoin("departments", "employees.department_id", "departments.id")
  .leftJoin("companies", "departments.company_id", "companies.id")
  .select({
    employeeId: "employees.id",
    employeeNumber: "employees.employee_number",
    departmentName: "departments.name", // Can be null
    companyName: "companies.name", // Can be null
  });

// employees (required)
// → departments (optional)
// → companies (optional)

Mixed INNER + LEFT JOIN

Use INNER JOIN for required relationships, LEFT JOIN for optional ones.

const results = await db
  .table("employees")
  .join("users", "employees.user_id", "users.id") // Required: all employees have users
  .leftJoin("departments", "employees.department_id", "departments.id") // Optional: may not have department
  .select({
    employeeName: "users.username", // Always exists
    departmentName: "departments.name", // Can be null
  });

Join type selection guide: 1. Check database schema (NOT NULL vs NULLABLE) 2. NOT NULL foreign key → INNER JOIN 3. NULLABLE foreign key → LEFT JOIN 4. Consider business requirements

Reusing an Existing JOIN

Use ensureJoin() or ensureLeftJoin() when a Model adds a JOIN that may already have been added by a Subset query.

const results = await db
  .table("patient_timeline_events")
  .ensureJoin(
    { patient: "patients" },
    "patient_timeline_events.patient_id",
    "patient.id",
  )
  .where("patient.organization_id", organizationId)
  .select({
    id: "patient_timeline_events.id",
    patientName: "patient.name",
  });

Puri compares JOINs by alias. If the alias, table, JOIN type, left column, and right column all match, it reuses the existing JOIN. If the alias is new, it adds the JOIN. Reusing an alias with a different definition throws an error before SQL execution. The same physical table can still be joined more than once under different aliases.

db.table("documents")
  .ensureJoin({ created_by: "users" }, "documents.created_by_id", "created_by.id")
  .ensureJoin({ updated_by: "users" }, "documents.updated_by_id", "updated_by.id");

ensureJoin() and ensureLeftJoin() support simple column equality JOINs against tables. Callback and subquery JOINs continue to use join() and leftJoin() and are not considered reusable.

Self JOIN - Self Reference

Use aliases when joining the same table.

Department Hierarchy Example

const results = await db
  .table({ child: "departments" })
  .leftJoin({ parent: "departments" }, "child.parent_id", "parent.id")
  .select({
    childId: "child.id",
    childName: "child.name",
    parentId: "parent.id",
    parentName: "parent.name", // Can be null (top-level department)
  });

// departments (child) LEFT JOIN departments (parent)
// child.parent_id = parent.id

Example Results:

childId	childName	parentId	parentName
1	Headquarters	null	null
2	Dev Team	1	Headquarters
3	Design Team	1	Headquarters
4	Backend Team	2	Dev Team

User Referrer Example

const results = await db
  .table({ user: "users" })
  .leftJoin({ referrer: "users" }, "user.referrer_id", "referrer.id")
  .select({
    userId: "user.id",
    userName: "user.username",
    referrerId: "referrer.id",
    referrerName: "referrer.username", // Can be null
  })
  .where("user.role", "normal");

// Normal users + referrer info

Many-to-Many Join

Many-to-many (M:N) relationships are joined through junction tables.

Projects ↔ Employees Example

const results = await db
  .table("projects")
  .leftJoin("projects__employees", "projects.id", "projects__employees.project_id")
  .leftJoin("employees", "projects__employees.employee_id", "employees.id")
  .leftJoin("users", "employees.user_id", "users.id")
  .select({
    projectId: "projects.id",
    projectName: "projects.name",
    projectStatus: "projects.status",
    employeeId: "employees.id",
    employeeName: "users.username",
  })
  .where("projects.status", "in_progress");

// projects ↔ projects__employees ↔ employees ↔ users

Result characteristics:

1 project with N employees → N rows
Projects without employees → 1 row (employee fields are null)

M:N join considerations: 1. Results are duplicated (rows created per employee per project) 2. Be careful with aggregations (use COUNT(DISTINCT ...)) 3. Pagination becomes complex

M:N Grouped by Employee

const results = await db
  .table("employees")
  .leftJoin("projects__employees", "employees.id", "projects__employees.employee_id")
  .leftJoin("projects", "projects__employees.project_id", "projects.id")
  .leftJoin("users", "employees.user_id", "users.id")
  .select({
    employeeId: "employees.id",
    employeeName: "users.username",
    projectCount: Puri.count("projects.id"),
  })
  .groupBy("employees.id", "users.username")
  .having("projectCount", ">", 0);

// Project count per employee

Subquery Join

Join subqueries as if they were tables.

Department Statistics Join

// Subquery for employee count per department
const empStats = db
  .table("employees")
  .select({
    department_id: "department_id",
    emp_count: Puri.count("id"),
    avg_salary: Puri.avg("salary"),
  })
  .groupBy("department_id");

// Join departments + statistics
const results = await db
  .table("departments")
  .leftJoin({ stats: empStats }, "departments.id", "stats.department_id")
  .select({
    deptId: "departments.id",
    deptName: "departments.name",
    empCount: "stats.emp_count",
    avgSalary: "stats.avg_salary",
  });

// Subquery for latest login per user
const latestLogins = db
  .table("login_logs")
  .select({
    user_id: "user_id",
    latest_login: Puri.max("logged_in_at"),
  })
  .groupBy("user_id");

// Join users + latest login
const results = await db
  .table("users")
  .leftJoin({ latest: latestLogins }, "users.id", "latest.user_id")
  .select({
    userId: "users.id",
    userName: "users.username",
    latestLogin: "latest.latest_login",
  });

Join + WHERE Conditions

Pre-join Filtering

const results = await db
  .table("employees")
  .join("users", "employees.user_id", "users.id")
  .leftJoin("departments", "employees.department_id", "departments.id")
  .select({
    employeeId: "employees.id",
    userName: "users.username",
    deptName: "departments.name",
  })
  .where("users.is_active", true) // users filter
  .where("employees.salary", ">=", 50000) // employees filter
  .where("departments.name", "like", "%dev%"); // departments filter

Post-join Aggregation

const results = await db
  .table("departments")
  .leftJoin("employees", "departments.id", "employees.department_id")
  .select({
    deptId: "departments.id",
    deptName: "departments.name",
    empCount: Puri.count("employees.id"),
  })
  .groupBy("departments.id", "departments.name")
  .having("empCount", ">", 5)
  .orderBy("empCount", "desc");

Practical Examples

User Profile Query

async getUserProfile(userId: number) {
  const profile = await this.getPuri("r")
    .table("users")
    .join("employees", "users.id", "employees.user_id")
    .leftJoin("departments", "employees.department_id", "departments.id")
    .leftJoin("companies", "departments.company_id", "companies.id")
    .select({
      userId: "users.id",
      username: "users.username",
      email: "users.email",
      employeeNumber: "employees.employee_number",
      departmentName: "departments.name",
      companyName: "companies.name",
      salary: "employees.salary",
    })
    .where("users.id", userId)
    .first();

  if (!profile) {
    throw new Error("User not found");
  }

  return profile;
}

Project Details + Member List

async getProjectDetails(projectId: number) {
  const project = await this.getPuri("r")
    .table("projects")
    .select({
      id: "projects.id",
      name: "projects.name",
      status: "projects.status",
      description: "projects.description",
    })
    .where("projects.id", projectId)
    .first();

  if (!project) {
    throw new Error("Project not found");
  }

  // Member list
  const members = await this.getPuri("r")
    .table("projects__employees")
    .join("employees", "projects__employees.employee_id", "employees.id")
    .join("users", "employees.user_id", "users.id")
    .leftJoin("departments", "employees.department_id", "departments.id")
    .select({
      employeeId: "employees.id",
      employeeName: "users.username",
      employeeNumber: "employees.employee_number",
      departmentName: "departments.name",
      role: "projects__employees.role",
    })
    .where("projects__employees.project_id", projectId);

  return {
    ...project,
    members,
  };
}

Department Hierarchy Query

async getDepartmentHierarchy(companyId: number) {
  const departments = await this.getPuri("r")
    .table({ dept: "departments" })
    .leftJoin(
      { parent: "departments" },
      "dept.parent_id",
      "parent.id"
    )
    .leftJoin("companies", "dept.company_id", "companies.id")
    .select({
      id: "dept.id",
      name: "dept.name",
      parentId: "parent.id",
      parentName: "parent.name",
      companyName: "companies.name",
      level: Puri.rawNumber(`
        CASE
          WHEN dept.parent_id IS NULL THEN 0
          WHEN parent.parent_id IS NULL THEN 1
          ELSE 2
        END
      `),
    })
    .where("dept.company_id", companyId)
    .orderBy("level", "asc")
    .orderBy("dept.name", "asc");

  return departments;
}

Type Safety

Puri’s joins are type-safe.

const results = await db
  .table("employees")
  .join("users", "employees.user_id", "users.id")
  .leftJoin("departments", "employees.department_id", "departments.id")
  .select({
    employeeId: "employees.id", // number
    userName: "users.username", // string
    departmentName: "departments.name", // string | null (LEFT JOIN)
  });

// Types are automatically inferred
const first = results[0];
first.employeeId; // number
first.userName; // string
first.departmentName; // string | null

LEFT JOIN and types: - Columns from LEFT JOIN tables are T | null type - Columns from INNER JOIN tables are T type - TypeScript automatically enforces null checks

Performance Optimization

Index Usage

-- Create indexes on join keys
CREATE INDEX idx_employees_user_id ON employees(user_id);
CREATE INDEX idx_employees_department_id ON employees(department_id);

Select Only Needed Columns

// ❌ Bad: Query all columns
await db.table("employees").join("users", "employees.user_id", "users.id").selectAll();

// ✅ Good: Select only needed columns
await db.table("employees").join("users", "employees.user_id", "users.id").select({
  id: "employees.id",
  name: "users.username",
});

Optimize JOIN Order

// Small table → Large table order
await db
  .table("departments")        // 10 rows
  .join("employees", ...)      // 100 rows
  .join("projects__employees", ...); // 1000 rows

Next Steps

Aggregations

Analyze data with aggregate functions

Advanced Patterns

Subqueries and complex patterns

Type Safety

Type safety in joins

Basic Queries

Back to basic queries

​Join Overview

INNER JOIN

LEFT JOIN

Self JOIN

M:N JOIN

​INNER JOIN

​Basic JOIN

​Multiple Table JOIN

​LEFT JOIN

​Basic LEFT JOIN

​INNER JOIN vs LEFT JOIN Comparison

​Complex LEFT JOIN

​Mixed INNER + LEFT JOIN

​Reusing an Existing JOIN

​Self JOIN - Self Reference

​Department Hierarchy Example

​User Referrer Example

​Many-to-Many Join

​Projects ↔ Employees Example

​M:N Grouped by Employee

​Subquery Join

​Department Statistics Join

​Latest Login Join

​Join + WHERE Conditions

​Pre-join Filtering

​Post-join Aggregation

​Practical Examples

​User Profile Query

​Project Details + Member List

​Department Hierarchy Query

​Type Safety

​Performance Optimization

​Index Usage

​Select Only Needed Columns

​Optimize JOIN Order

​Next Steps

Aggregations

Advanced Patterns

Type Safety

Basic Queries

Join Overview

INNER JOIN

Basic JOIN

Multiple Table JOIN

LEFT JOIN

Basic LEFT JOIN

INNER JOIN vs LEFT JOIN Comparison

Complex LEFT JOIN

Mixed INNER + LEFT JOIN

Reusing an Existing JOIN

Self JOIN - Self Reference

Department Hierarchy Example

User Referrer Example

Many-to-Many Join

Projects ↔ Employees Example

M:N Grouped by Employee

Subquery Join

Department Statistics Join

Latest Login Join

Join + WHERE Conditions

Pre-join Filtering

Post-join Aggregation

Practical Examples

User Profile Query

Project Details + Member List

Department Hierarchy Query

Type Safety

Performance Optimization

Index Usage

Select Only Needed Columns

Optimize JOIN Order

Next Steps