조인 - 🌲 Sonamu

Puri는 여러 테이블을 조인하여 관련 데이터를 함께 조회할 수 있습니다. 이 문서는 INNER JOIN, LEFT JOIN, 그리고 복잡한 조인 패턴을 설명합니다.

조인 개요

INNER JOIN

양쪽 테이블에 모두 존재하는 데이터 join()

LEFT JOIN

왼쪽 테이블 기준, 오른쪽은 Optional leftJoin()

Self JOIN

같은 테이블을 조인 alias 사용

M:N JOIN

다대다 관계 조인 중간 테이블 활용

INNER JOIN

양쪽 테이블에 모두 존재하는 데이터만 조회합니다.

기본 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

INNER JOIN 사용 시기: - 관계가 필수인 경우 (NOT NULL 외래 키) - 양쪽에 데이터가 있어야 하는 경우 - 예: 직원 → 사용자 (모든 직원은 반드시 사용자 계정이 있음)

여러 테이블 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 (필수)
// employees → departments (필수)
// departments → companies (필수)

LEFT JOIN

왼쪽 테이블의 모든 데이터를 포함하고, 오른쪽 테이블은 있으면 포함합니다.

기본 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", // null 가능
  });

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

LEFT JOIN 사용 시기: - 관계가 선택적인 경우 (NULLABLE 외래 키) - 왼쪽 테이블의 모든 데이터가 필요한 경우 - 예: 직원 → 부서 (일부 직원은 부서가 없을 수 있음)

INNER JOIN vs LEFT JOIN 비교

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

// 부서가 배정된 직원만 조회 (부서 없는 직원 제외)

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

// 모든 직원 조회 (부서 없는 직원도 포함, department_name은 null)

복합 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", // null 가능
    companyName: "companies.name", // null 가능
  });

// employees (필수)
// → departments (선택)
// → companies (선택)

INNER + LEFT JOIN 혼합

필수 관계는 INNER JOIN, 선택 관계는 LEFT JOIN을 사용합니다.

const results = await db
  .table("employees")
  .join("users", "employees.user_id", "users.id") // 필수: 모든 직원은 사용자 있음
  .leftJoin("departments", "employees.department_id", "departments.id") // 선택: 부서 없을 수 있음
  .select({
    employeeName: "users.username", // 항상 존재
    departmentName: "departments.name", // null 가능
  });

조인 타입 선택 가이드: 1. 데이터베이스 스키마 확인 (NOT NULL vs NULLABLE) 2. NOT NULL 외래 키 → INNER JOIN 3. NULLABLE 외래 키 → LEFT JOIN 4. 비즈니스 요구사항 고려

기존 JOIN 재사용

Subset 쿼리가 추가했을 수 있는 JOIN을 Model에서 다시 사용할 때는 ensureJoin() 또는 ensureLeftJoin()을 사용합니다.

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는 alias를 기준으로 JOIN을 비교합니다. alias, 테이블, JOIN 타입, 왼쪽 컬럼, 오른쪽 컬럼이 모두 같으면 기존 JOIN을 재사용하고, alias가 없으면 새 JOIN을 추가합니다. 같은 alias에 다른 정의를 사용하면 SQL 실행 전에 오류가 발생합니다. 같은 물리 테이블도 alias가 다르면 각각 JOIN할 수 있습니다.

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()과 ensureLeftJoin()은 테이블의 단순 컬럼 동등 JOIN만 지원합니다. callback 또는 서브쿼리 JOIN은 기존처럼 join()과 leftJoin()을 사용하며 재사용 대상으로 판정하지 않습니다.

Self JOIN - 자기 참조

같은 테이블을 조인할 때는 **별칭(alias)**을 사용합니다.

부서 계층구조 예제

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", // null 가능 (최상위 부서)
  });

// departments (자식) LEFT JOIN departments (부모)
// child.parent_id = parent.id

결과 예시:

childId	childName	parentId	parentName
1	본부	null	null
2	개발팀	1	본부
3	디자인팀	1	본부
4	백엔드팀	2	개발팀

사용자 추천인 예제

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", // null 가능
  })
  .where("user.role", "normal");

// 일반 사용자 + 추천인 정보

Many-to-Many 조인

다대다(M:N) 관계는 중간 테이블을 통해 조인합니다.

프로젝트 ↔ 직원 예제

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

결과 특징:

프로젝트 1개에 직원 N명 → N개 행
직원이 없는 프로젝트 → 1개 행 (employee 필드는 null)

M:N 조인 주의사항: 1. 결과가 중복됨 (프로젝트당 직원 수만큼 행 생성) 2. 집계 시 주의 (COUNT(DISTINCT ...) 사용) 3. 페이지네이션 복잡해짐

M:N 직원별로 그룹화

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);

// 직원별 참여 프로젝트 수

서브쿼리 조인

서브쿼리를 테이블처럼 조인할 수 있습니다.

부서별 통계 조인

// 부서별 직원 수 서브쿼리
const empStats = db
  .table("employees")
  .select({
    department_id: "department_id",
    emp_count: Puri.count("id"),
    avg_salary: Puri.avg("salary"),
  })
  .groupBy("department_id");

// 부서 + 통계 조인
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",
  });

조인 + WHERE 조건

조인 전 필터링

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 필터
  .where("employees.salary", ">=", 50000) // employees 필터
  .where("departments.name", "like", "%개발%"); // departments 필터

조인 후 집계

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");

실전 예제

사용자 프로필 조회

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;
}

프로젝트 상세 + 참여자 목록

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");
  }

  // 참여자 목록
  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,
  };
}

부서 계층구조 조회

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;
}

타입 안전성

Puri의 조인은 타입 안전합니다.

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)
  });

// 타입이 자동으로 추론됨
const first = results[0];
first.employeeId; // number
first.userName; // string
first.departmentName; // string | null

LEFT JOIN과 타입: - LEFT JOIN된 테이블의 컬럼은 T | null 타입 - INNER JOIN된 테이블의 컬럼은 T 타입 - 타입스크립트가 자동으로 null 체크 강제

성능 최적화

인덱스 활용

-- 조인 키에 인덱스 생성
CREATE INDEX idx_employees_user_id ON employees(user_id);
CREATE INDEX idx_employees_department_id ON employees(department_id);

필요한 컬럼만 선택

// ❌ 나쁨: 모든 컬럼 조회
await db.table("employees").join("users", "employees.user_id", "users.id").selectAll();

// ✅ 좋음: 필요한 컬럼만 선택
await db.table("employees").join("users", "employees.user_id", "users.id").select({
  id: "employees.id",
  name: "users.username",
});

JOIN 순서 최적화

// 작은 테이블 → 큰 테이블 순서
await db
  .table("departments")        // 10개
  .join("employees", ...)      // 100개
  .join("projects__employees", ...); // 1000개

다음 단계

Aggregations

집계 함수로 데이터 분석하기

Advanced Patterns

서브쿼리와 복잡한 패턴

Type Safety

조인 시 타입 안전성

Basic Queries

기본 쿼리로 돌아가기

​조인 개요

INNER JOIN

LEFT JOIN

Self JOIN

M:N JOIN

​INNER JOIN

​기본 JOIN

​여러 테이블 JOIN

​LEFT JOIN

​기본 LEFT JOIN

​INNER JOIN vs LEFT JOIN 비교

​복합 LEFT JOIN

​INNER + LEFT JOIN 혼합

​기존 JOIN 재사용

​Self JOIN - 자기 참조

​부서 계층구조 예제

​사용자 추천인 예제

​Many-to-Many 조인

​프로젝트 ↔ 직원 예제

​M:N 직원별로 그룹화

​서브쿼리 조인

​부서별 통계 조인

​최신 로그인 조인

​조인 + WHERE 조건

​조인 전 필터링

​조인 후 집계

​실전 예제

​사용자 프로필 조회

​프로젝트 상세 + 참여자 목록

​부서 계층구조 조회

​타입 안전성

​성능 최적화

​인덱스 활용

​필요한 컬럼만 선택

​JOIN 순서 최적화

​다음 단계

Aggregations

Advanced Patterns

Type Safety

Basic Queries

조인 개요

INNER JOIN

기본 JOIN

여러 테이블 JOIN

LEFT JOIN

기본 LEFT JOIN

INNER JOIN vs LEFT JOIN 비교

복합 LEFT JOIN

INNER + LEFT JOIN 혼합

기존 JOIN 재사용

Self JOIN - 자기 참조

부서 계층구조 예제

사용자 추천인 예제

Many-to-Many 조인

프로젝트 ↔ 직원 예제

M:N 직원별로 그룹화

서브쿼리 조인

부서별 통계 조인

최신 로그인 조인

조인 + WHERE 조건

조인 전 필터링

조인 후 집계

실전 예제

사용자 프로필 조회

프로젝트 상세 + 참여자 목록

부서 계층구조 조회

타입 안전성

성능 최적화

인덱스 활용

필요한 컬럼만 선택

JOIN 순서 최적화

다음 단계