Joins

The join methods allow you to combine data from multiple tables with type-safe column references and predefined relationships.

Basic Usage

import { createQueryBuilder } from '@hypequery/clickhouse';

const db = createQueryBuilder<Schema>();

// Basic join
const query = db
  .table('orders')
  .leftJoin('users', 'orders.user_id', 'users.id')
  .select(['orders.id', 'users.name'])
  .toSQL();
// Result: SELECT orders.id, users.name FROM orders LEFT JOIN users ON orders.user_id = users.id

Join Methods

innerJoin

Performs an INNER JOIN, returning only rows that match in both tables.

innerJoin<TableName extends keyof Schema>(
  table: TableName,
  leftColumn: keyof OriginalT,
  rightColumn: `${TableName & string}.${keyof Schema[TableName] & string}`,
  alias?: string
): this

leftJoin

Performs a LEFT JOIN, returning all rows from the left table and matching rows from the right table.

leftJoin<TableName extends keyof Schema>(
  table: TableName,
  leftColumn: keyof OriginalT,
  rightColumn: `${TableName & string}.${keyof Schema[TableName] & string}`,
  alias?: string
): this

rightJoin

Performs a RIGHT JOIN, returning all rows from the right table and matching rows from the left table.

rightJoin<TableName extends keyof Schema>(
  table: TableName,
  leftColumn: keyof OriginalT,
  rightColumn: `${TableName & string}.${keyof Schema[TableName] & string}`,
  alias?: string
): this

fullJoin

Performs a FULL JOIN, returning all rows from both tables.

fullJoin<TableName extends keyof Schema>(
  table: TableName,
  leftColumn: keyof OriginalT,
  rightColumn: `${TableName & string}.${keyof Schema[TableName] & string}`,
  alias?: string
): this

Examples

Basic Join

// Join orders with users
const query = db
  .table('orders')
  .leftJoin('users', 'orders.user_id', 'users.id')
  .select(['orders.id', 'users.name', 'orders.total'])
  .toSQL();
// Result: SELECT orders.id, users.name, orders.total FROM orders LEFT JOIN users ON orders.user_id = users.id

Using Predefined Relationships

// Use predefined relationship
const query = db
  .table('orders')
  .withRelation('orders.user')
  .select(['orders.id', 'users.name', 'orders.total'])
  .toSQL();
// Result: SELECT orders.id, users.name, orders.total FROM orders LEFT JOIN users ON orders.user_id = users.id

Type Safety

The join methods provide full TypeScript support:

interface Schema {
  orders: {
    id: 'UInt32';
    user_id: 'UInt32';
    total: 'Float64';
  };
  users: {
    id: 'UInt32';
    name: 'String';
    email: 'String';
  };
}

const db = createQueryBuilder<Schema>();

// ✅ Type-safe column references
const query = db
  .table('orders')
  .leftJoin('users', 'orders.user_id', 'users.id') // TypeScript validates all columns
  .select(['orders.id', 'users.name'])
  .toSQL();

// ❌ TypeScript error for invalid table
const query2 = db
  .table('orders')
  .leftJoin('invalid_table', 'orders.user_id', 'invalid_table.id') // TypeScript error
  .toSQL();