cake/join

Functions to build JOIN clauses of SQL queries.

Tables, views and sub-queries can be joined together.

Supported join kinds

INNER JOIN
LEFT JOIN, inclusive, same as LEFT OUTER JOIN,
RIGHT JOIN, inclusive, same as RIGHT OUTER JOIN,
FULL JOIN, inclusive, same as FULL OUTER JOIN,
CROSS JOIN

You can also build following joins using the provided query builder functions:

SELF JOIN: Use the same table, view, or sub-query with a different alias.
EXCLUSIVE LEFT JOIN: WHERE b.key IS NULL
EXCLUSIVE RIGHT JOIN: WHERE a.key IS NULL
EXCLUSIVE FULL JOIN: WHERE a.key IS NULL OR b.key IS NULL

Types

Join

</>

pub type Join =
  read_query.Join

JoinTarget

</>

pub type JoinTarget =
  read_query.JoinTarget

ReadQuery

</>

pub type ReadQuery =
  read_query.ReadQuery

Where

</>

pub type Where =
  read_query.Where

Functions

cross

</>

pub fn cross(with wth: JoinTarget, alias als: String) -> Join

Creates a CROSS JOIN.

Also called cartesian product.

cross_lateral

</>

pub fn cross_lateral(
  with wth: JoinTarget,
  alias als: String,
) -> Join

Creates a CROSS JOIN LATERAL.

⚠️⚠️⚠️

CAUTION: LATERAL joins are not optimized by the query planner, and can be very slow on large datasets, especially when the sub-query returns many rows.

⚠️⚠️⚠️

See https://www.postgresql.org/docs/9.3/sql-select.html#SQL-FROM for an explanation on how LATERAL works.

NOTICE: LATERAL is supported by 🐘PostgreSQL 9.3+ and recent 🐬MySQL versions.

full

</>

pub fn full(
  with wth: JoinTarget,
  on on: Where,
  alias als: String,
) -> Join

Creates a FULL JOIN.

Also called FULL OUTER JOIN.

Inclusive by default.

Set on to WHERE a.key IS NULL OR b.key IS NULL to make it exclusive.

inner

</>

pub fn inner(
  with wth: JoinTarget,
  on on: Where,
  alias als: String,
) -> Join

Create an INNER JOIN.

inner_lateral

</>

pub fn inner_lateral(
  with wth: JoinTarget,
  alias als: String,
) -> Join

Creates a INNER JOIN LATERAL ... ON TRUE.

⚠️⚠️⚠️

CAUTION: LATERAL joins are not optimized by the query planner, and can be very slow on large datasets, especially when the sub-query returns many rows.

⚠️⚠️⚠️

See https://www.postgresql.org/docs/9.3/sql-select.html#SQL-FROM for an explanation on how LATERAL works.

Any filtering must be done in WHERE clauses as the JOIN ON clause is always TRUE when calling this function.

NOTICE: LATERAL is supported by 🐘PostgreSQL 9.3+ and recent 🐬MySQL versions.

left

</>

pub fn left(
  with wth: JoinTarget,
  on on: Where,
  alias als: String,
) -> Join

Creates a LEFT JOIN.

Also called LEFT OUTER JOIN.

Inclusive by default.

Set on to WHERE a.key IS NULL to make it exclusive.

left_lateral

</>

pub fn left_lateral(
  with wth: JoinTarget,
  alias als: String,
) -> Join

Creates a LEFT JOIN LATERAL ... ON TRUE.

⚠️⚠️⚠️

CAUTION: LATERAL joins are not optimized by the query planner, and can be very slow on large datasets, especially when the sub-query returns many rows.

⚠️⚠️⚠️

See https://www.postgresql.org/docs/9.3/sql-select.html#SQL-FROM for an explanation on how LATERAL works.

Any filtering must be done in WHERE clauses as the JOIN ON clause is always TRUE when calling this function.

NOTICE: LATERAL is supported by 🐘PostgreSQL 9.3+ and recent 🐬MySQL versions.

right

</>

pub fn right(
  with wth: JoinTarget,
  on on: Where,
  alias als: String,
) -> Join

Creates a RIGHT JOIN.

Also called RIGHT OUTER JOIN.

Inclusive by default.

Set on to WHERE b.key IS NULL to make it exclusive.

sub_query

</>

pub fn sub_query(sub_query sq: ReadQuery) -> JoinTarget

Create a JOIN target from a sub-query.

table

</>

pub fn table(table_name tbl_nm: String) -> JoinTarget

Create a JOIN target from a table name.