Joins

Select#join(...) attaches join clauses built from one of the join element constructors — InnerJoin, LeftJoin, LeftOuterJoin, RightJoin, RightOuterJoin, OuterJoin, FullOuterJoin, CrossJoin — or the generic Join class combined with the JoinType enum.

import { Select, InnerJoin, Eq, Field } from '@sqb/builder';

const query = Select()
  .from('customers c')
  .join(InnerJoin('orders o').on(Eq('o.customer_id', Field('c.id'))));

query.generate().sql;
// select * from customers c inner join orders o on o.customer_id = c.id

Field('c.id') on the right-hand side marks it as a column reference rather than a literal value — without it, Eq() would treat a bare string as a value to compare against (and quote it as a string literal), not as another column. See Operators and Conditions.

.join() throws a TypeError if any argument is not a Join instance:

Select().from('customers').join('orders');
// TypeError: Join statement required

Join type constructors

Each constructor is a thin, dual-callable subclass of Join that hard-codes its JoinType. Full reference: API Reference → Classes.

Constructor	SQL keyword	JoinType
InnerJoin(table)	inner join	JoinType.INNER
LeftJoin(table)	left join	JoinType.LEFT
LeftOuterJoin(table)	left outer join	JoinType.LEFT_OUTER
RightJoin(table)	right join	JoinType.RIGHT
RightOuterJoin(table)	right outer join	JoinType.RIGHT_OUTER
OuterJoin(table)	outer join	JoinType.OUTER
FullOuterJoin(table)	full outer join	JoinType.FULL_OUTER
CrossJoin(table)	cross join	JoinType.CROSS

table accepts a table-name string, a TableName instance, a Raw fragment, or a sub-Select (which must carry an alias via .as()).

Select().from('t1').join(LeftJoin('t2'));
// ... left join t2

Select().from('t1').join(CrossJoin('t2'));
// ... cross join t2

You can also build any join type with the generic constructor and an explicit JoinType:

import { Join, JoinType } from '@sqb/builder';

Select().from('t1').join(Join(JoinType.LEFT, 't2'));
// equivalent to LeftJoin('t2')

on()

.on(...conditions) attaches join conditions, combined with an implicit And — same semantics as .where(). It accepts operator instances, Raw fragments, or object-literal shorthand (see Operators and Conditions). Omitting .on() entirely — or calling it with no arguments — produces a join with no ON clause.

Select().from('t1').join(InnerJoin('t2').on(Eq('t2.id', Field('t1.id'))));
// ... inner join t2 on t2.id = t1.id

Select().from('t1').join(InnerJoin('t2').on());
// ... inner join t2   (no "on" clause)

Joining a sub-select

A sub-Select used as a join's table must have an alias, or .generate() throws:

Select()
  .from('t1')
  .join(InnerJoin(Select().from('t2').as('t2')));
// ... inner join (select * from t2) t2

Select()
  .from('t1')
  .join(InnerJoin(Select().from('t2'))) // no .as()
  .generate();
// Error: Alias required for sub-select in Join

Multiple joins

Call .join() once with multiple arguments, or multiple times — each call appends to the join list rather than replacing it:

Select()
  .from('customers c')
  .join(
    InnerJoin('orders o').on(Eq('o.customer_id', Field('c.id'))),
    LeftJoin('addresses a').on(Eq('a.customer_id', Field('c.id'))),
  );