> hypequery
Query builder

Joins

Combine data from multiple tables

Joins

Joins combine data from multiple tables based on related columns. hypequery provides type-safe joins with full TypeScript support for joined columns.

Overview

Use joins to:

  • Combine related data from multiple tables
  • Enrich queries with additional context
  • Avoid N+1 query problems
  • Maintain type safety across tables

Query builder syntax

These examples use a typed standalone db client so the query builder stays the focus.

Join Types

Join TypeMethodDescription
INNER JOINinnerJoin()Returns rows when both tables have matches
LEFT JOINleftJoin()Returns all rows from left table, matched rows from right
RIGHT JOINrightJoin()Returns all rows from right table, matched rows from left
FULL JOINfullJoin()Returns all rows when there's a match in either table
ARRAY JOINarrayJoin()Expands array values into multiple rows
LEFT ARRAY JOINleftArrayJoin()Expands array values while preserving rows with empty arrays

Inner Join

Returns only rows where both tables have matching values:

const results = await db.table('orders')
  .innerJoin('users', 'user_id', 'users.id')
  .select([
    'orders.id',
    'orders.total',
    'users.name AS customer_name',
    'users.email AS customer_email',
  ])
  .execute();

Use Inner Join when

  • You only want records with matches in both tables
  • The relationship is required
  • You want to filter out unmatched records

Left Join

Returns all rows from the left table, and matched rows from the right table (NULL if no match):

const results = await db.table('users') .leftJoin('orders', 'id', 'orders.user_id') .select([ 'users.id', 'users.name', 'orders.id AS last_order_id', 'orders.total AS last_order_total', ]) .execute();

Use Left Join when

  • You want all records from the primary table
  • The relationship is optional
  • You need to preserve unmatched records

Right Join

Returns all rows from the right table, and matched rows from the left table:

const results = await db.table('orders')
  .rightJoin('users', 'user_id', 'users.id')
  .select([
    'orders.id',
    'users.name',
    'users.email',
  ])
  .execute();

Full Join

Returns all rows when there's a match in either table:

const results = await db.table('employees')
  .fullJoin('departments', 'dept_id', 'departments.id')
  .select([
    'employees.name AS employee',
    'departments.name AS department',
  ])
  .execute();

Array Join

Use arrayJoin() when a ClickHouse array column should expand into one row per element.

const results = await db.table('events')
  .select(['id', 'tags'])
  .arrayJoin('tags')
  .execute();

This maps directly to ClickHouse ARRAY JOIN.

Array-typed columns only

arrayJoin() and leftArrayJoin() are intended for array-valued columns. The type system now enforces that more strictly, including for joined and aliased columns.

Left Array Join

Use leftArrayJoin() when you want array expansion but still need rows with empty arrays to stay in the result.

const results = await db.table('events')
  .select(['id', 'tags'])
  .leftArrayJoin('tags')
  .execute();

This maps directly to ClickHouse LEFT ARRAY JOIN.

Join Syntax

Basic Join

db.table('table_name')
  .joinType('other_table', 'left_column', 'right_table.right_column')
  • joinType: One of innerJoin, leftJoin, leftAnyJoin, rightJoin, fullJoin
  • other_table: Name of the table to join
  • left_column: Column from the current table
  • right_column: Column from the joined table (format: 'table.column')

With Table Alias

await db.table('orders')
  .innerJoin('users', 'user_id', 'users.id', 'u')
  .select([
    'orders.id',
    'u.name AS customer_name',
  ])
  .execute();

Type Safety with Joins

Important

TypeScript only exposes columns from a joined table after you register the join. Call leftJoin('users', ...) before referencing users.email in select, where, etc.

Incorrect Order

// ❌ Error - users table not joined yet
await db.table('orders')
  .select(['orders.id', 'users.name']) // TypeScript error
  .leftJoin('users', 'user_id', 'users.id')
  .execute();

Correct Order

// ✅ Correct - join first, then select
await db.table('orders')
  .leftJoin('users', 'user_id', 'users.id')
  .select(['orders.id', 'users.name']) // TypeScript OK
  .execute();

TypeScript Knows Joined Columns

const results = await db.table('orders')
  .leftJoin('users', 'user_id', 'users.id')
  .select([
    'orders.id',
    'orders.total',
    'users.name',
    'users.email',
  ])
  .execute();

// TypeScript knows result type includes:
// - orders.id (number)
// - orders.total (number)
// - users.name (string)
// - users.email (string)

Multiple Joins

Chain multiple joins to combine data from several tables:

const results = await db.table('orders')
  .innerJoin('users', 'user_id', 'users.id')
  .leftJoin('products', 'product_id', 'products.id')
  .leftJoin('categories', 'category_id', 'categories.id')
  .select([
    'orders.id AS order_id',
    'users.name AS customer_name',
    'products.name AS product_name',
    'categories.name AS category_name',
  ])
  .execute();

Filtering with Joins

Where on Joined Columns

const results = await db.table('orders')
  .innerJoin('users', 'user_id', 'users.id')
  .where('users.country', 'eq', 'US')
  .where('orders.total', 'gte', 100)
  .select([
    'orders.id',
    'users.name',
    'orders.total',
  ])
  .execute();

Where Groups with Joins

const results = await db.table('orders')
  .leftJoin('users', 'user_id', 'users.id')
  .whereGroup((builder) => {
    builder
      .where('users.country', 'eq', 'US')
      .orWhere('users.country', 'eq', 'CA');
  })
  .where('orders.status', 'eq', 'completed')
  .select([
    'orders.id',
    'users.name',
    'users.country',
  ])
  .execute();

Joining on Multiple Columns

Pass a non-empty array of column pairs to match a composite key. All five join methods support this form, and TypeScript checks each left column against the base row and each right column against the join target.

const results = await db.table('entities')
  .withCTE('children_cte', `
    SELECT tenant_id AS parent_tenant_id, entity_id AS parent_entity_id,
      sub_id AS parent_sub_id, toJSONString(groupArray(child_id)) AS children_json
    FROM children
    GROUP BY tenant_id, entity_id, sub_id
  `, {
    parent_tenant_id: 'UInt64',
    parent_entity_id: 'UInt64',
    parent_sub_id: 'UInt64',
    children_json: 'String',
  })
  .leftAnyJoin('children_cte', [
    ['tenant_id', 'children_cte.parent_tenant_id'],
    ['entity_id', 'children_cte.parent_entity_id'],
    ['sub_id', 'children_cte.parent_sub_id'],
  ])
  .select(['tenant_id', 'entity_id', 'sub_id', 'children_cte.children_json'])
  .execute();

The keys render as ON tenant_id = children_cte.parent_tenant_id AND entity_id = children_cte.parent_entity_id AND sub_id = children_cte.parent_sub_id. The CTE column types must match the columns exposed by your SQL.

For schema tables, pass an optional alias after the pairs. Right columns use the table name in the input; the builder rewrites every right qualifier to the alias:

db.table('entities').leftJoin('children', [
  ['tenant_id', 'children.parent_tenant_id'],
  ['entity_id', 'children.parent_entity_id'],
], 'c');

CTE targets use their declared name and currently cannot take a separate join alias. Stored arrays can use as const to preserve column names.

Additional ON Filters

leftJoin and leftAnyJoin accept literal filters after the optional alias:

db.table('entities').leftAnyJoin('children', [
  ['tenant_id', 'children.parent_tenant_id'],
  ['entity_id', 'children.parent_entity_id'],
], 'c', [{ column: 'c.status', operator: 'eq', value: 'active' }]);

These filter values are bound literals. A string such as 'children.entity_id' does not become a column reference; use a column pair for column-to-column equality. Existing single-pair calls remain supported.

ClickHouse supports multiple conditions in ON. Conditions in ON control matching while preserving unmatched rows according to the join type; placing the same conditions in WHERE filters the result and can remove unmatched outer-join rows. See the ClickHouse JOIN documentation.

Common Patterns

Orders with User Details

const orders = await db.table('orders')
  .innerJoin('users', 'user_id', 'users.id')
  .select([
    'orders.id',
    'orders.total',
    'orders.created_at',
    'users.name AS customer_name',
    'users.email AS customer_email',
  ])
  .orderBy('orders.created_at', 'DESC')
  .limit(50)
  .execute();

Products with Categories

const products = await db.table('products')
  .leftJoin('categories', 'category_id', 'categories.id')
  .select([
    'products.id',
    'products.name',
    'products.price',
    'categories.name AS category_name',
  ])
  .where('products.in_stock', 'eq', true)
  .execute();

Events with Session Data

const events = await db.table('events')
  .innerJoin('sessions', 'session_id', 'sessions.id')
  .innerJoin('users', 'user_id', 'users.id')
  .select([
    'events.id',
    'events.type',
    'events.data',
    'sessions.started_at AS session_start',
    'users.name AS user_name',
  ])
  .where('events.created_at', 'gte', '2024-01-01')
  .execute();

All Users with Their Orders (or NULL)

const results = await db.table('users')
  .leftJoin('orders', 'id', 'orders.user_id')
  .select([
    'users.id',
    'users.name',
    'orders.id AS last_order_id',
    'orders.total AS last_order_total',
  ])
  .orderBy('users.id', 'ASC')
  .execute();

Performance Considerations

Join Performance

  • Inner joins are typically faster than left joins
  • Join on indexed columns when possible
  • Filter before joining when you can
  • Consider denormalizing for frequently-joined data

Filter Before Joining

// ✅ Better - filter first
const results = await db.table('orders')
  .where('orders.status', 'eq', 'completed')
  .where('orders.total', 'gte', 100)
  .innerJoin('users', 'user_id', 'users.id')
  .select(['orders.*', 'users.name'])
  .execute();

// ❌ Worse - join all then filter
const results = await db.table('orders')
  .innerJoin('users', 'user_id', 'users.id')
  .where('orders.status', 'eq', 'completed')
  .where('orders.total', 'gte', 100)
  .select(['orders.*', 'users.name'])
  .execute();

Examples

E-commerce Order Details

const orderDetails = await db.table('orders')
  .innerJoin('users', 'user_id', 'users.id')
  .innerJoin('products', 'product_id', 'products.id')
  .select([
    'orders.id AS order_id',
    'orders.quantity',
    'orders.total',
    'users.name AS customer_name',
    'users.email AS customer_email',
    'products.name AS product_name',
    'products.price AS unit_price',
  ])
  .where('orders.created_at', 'gte', '2024-01-01')
  .orderBy('orders.created_at', 'DESC')
  .execute();

Analytics with User Segments

const analytics = await db.table('events')
  .leftJoin('users', 'user_id', 'users.id')
  .leftJoin('segments', 'segment_id', 'segments.id')
  .select([
    'events.type',
    'segments.name AS user_segment',
    selectExpr('count()', 'event_count'),
  ])
  .groupBy(['events.type', 'user_segment'])
  .execute();

Where

Filter ClickHouse queries in TypeScript with hypequery's where builder. Learn operators, predicates, and reusable filtering patterns for type-safe analytics queries.

Join Relationships

Define reusable join paths with JoinRelationships and apply them with withRelation()

On this page

JoinsOverviewJoin TypesInner JoinLeft JoinRight JoinFull JoinArray JoinLeft Array JoinJoin SyntaxBasic JoinWith Table AliasType Safety with JoinsIncorrect OrderCorrect OrderTypeScript Knows Joined ColumnsMultiple JoinsFiltering with JoinsWhere on Joined ColumnsWhere Groups with JoinsJoining on Multiple ColumnsAdditional ON FiltersCommon PatternsOrders with User DetailsProducts with CategoriesEvents with Session DataAll Users with Their Orders (or NULL)Performance ConsiderationsFilter Before JoiningExamplesE-commerce Order DetailsAnalytics with User Segments