Joins

Join clauses range from simple to complex including joining complete subqueries on multiple conditions. qb has your back with all of these use cases.

Table of Contents
join	joinRaw	joinSub	joinWhere
leftJoin	leftJoinRaw	leftJoinSub	newJoin
rightJoin	rightJoinRaw	rightJoinSub	JoinClause
crossJoin	crossJoinRaw	crossJoinSub

join

Name	Type	Required	Default	Description
table	string \| Expression \| JoinClause	`true`		The name of the table or a `Expression` object from which the query is based. Alternatively, a configured `JoinClause` instance can be passed.
first	string \| Expression \| Function	`false`		The first column or `Expression` to join the table on. Alternatively, a function can be passed to configure complex join statements.
operator	string	`false`	`"="`	The boolean operator for the join clause.
second	string \| Expression	`false`		The second column or `Expression` to join the table on.
type	string	`false`	`"inner"`	The type of the join. Passing this as an argument is discouraged for readability. Use the dedicated methods like `leftJoin` and `rightJoin` where possible.
where	boolean	`false`	`false`	Sets if the value of `second` should be interpreted as a column or a value. Passing this as an argument is discouraged. Use the dedicated `joinWhere` or a join closure where possible.

Applies a join to the query. The simplest join is to a table based on two columns:

{% code title="QueryBuilder" %}

query.from( "users" )
    .join( "posts", "users.id", "=", "posts.author_id" );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

{% endcode %}

When doing a simple join using = as the operator, you can omit it and pass just the column names:

{% code title="QueryBuilder" %}

query.from( "users" )
    .join( "posts", "users.id", "posts.author_id" );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

{% endcode %}

``Expressions are also supported as the table argument (though you may prefer the readability of the joinRaw method):

{% code title="QueryBuilder" %}

query.from( "users" )
    .join( query.raw( "posts (nolock)" ), "users.id", "=", "posts.author_id" );

{% endcode %}

{% code title="SQL Server" %}

SELECT *
FROM [users]
JOIN posts (nolock)
  ON [users].[id] = [posts].[author_id]

{% endcode %}

{% hint style="warning" %} Using raw will most likely tie your code to a specific database, so think carefully before using the raw method if you want your project to be database agnostic. {% endhint %}

When you need to specify more clauses to join, you can pass a function as the second argument:

{% code title="QueryBuilder" %}

query.from( "users" )
    .join( "posts", function( j ) {
        j.on( "users.id", "=", "posts.author_id" );
        j.on( "users.prefix", "=", "posts.prefix" );
    } );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
  AND `users`.`prefix` = `posts`.`prefix`

{% endcode %}

You can specify where clauses in your joins as well.

{% code title="QueryBuilder" %}

query.from( "users" )
    .join( "posts", function( j ) {
        j.on( "users.id", "=", "posts.author_id" );
        j.whereNotNull( "posts.published_date" );
    } );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
  AND `posts`.`published_date` IS NOT NULL

{% endcode %}

Conditions inside a join clause can be grouped using a function.

{% code title="QueryBuilder" %}

query.from( "users" )
    .join( "posts", function( j ) {
        j.on( function( j1 ) {
            j1.on( "users.id", "posts.author_id" )
                .orOn( "users.id", "posts.reviewer_id" );
        } );
        j.whereNotNull( "posts.published_date" );
    } );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `users`
JOIN `posts`
  ON (
      `users`.`id` = `posts`.`author_id`
      OR `users`.`id` = `posts`.`reviewer_id`
  )
  AND `posts`.`published_date` IS NOT NULL

{% endcode %}

A preconfigured JoinClause can also be passed to the join function. This allows you to extract shared pieces of code out to different functions.

{% code title="QueryBuilder" %}

var j = query.newJoin( "contacts" )
    .on( "users.id", "posts.author_id" );

query.from( "users" ).join( j );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

{% endcode %}

joinWhere

Name	Type	Required	Default	Description
table	string	`true`		The raw SQL string to use as the table.
first	string \| Expression \| Function	`false`		The first column or `Expression` to join the table on. Alternatively, a function can be passed to configure complex join statements.
operator	string	`false`	`"="`	The boolean operator for the join clause.
second	string \| Expression	`false`		The second column or `Expression` to join the table on.
type	string	`false`	`"inner"`	The type of the join. Passing this as an argument is discouraged for readability. Use the dedicated methods like `leftJoin` and `rightJoin` with a join function where possible.

Adds a join to another table based on a WHERE clause instead of an ON clause. WHERE clauses introduce parameters and parameter bindings whereas on clauses join between columns and don't need parameter bindings.

For simple joins, this specifies a column on which to join the two tables:

{% code title="QueryBuilder" %}

query.from( "users" )
    .joinWhere( "contacts", "contacts.balance", "<", 100 );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `users`
JOIN `contacts`
  WHERE `contacts`.`balance` < ?

{% endcode %}

For complex joins, a function can be passed to first. This allows multiple on and where conditions to be applied to the join. See the documentation for join for more information.

joinRaw

Name	Type	Required	Default	Description
table	string	`true`		The raw SQL string to use as the table.
first	string \| Expression \| Function	`false`		The first column or `Expression` to join the table on. Alternatively, a function can be passed to configure complex join statements.
operator	string	`false`	`"="`	The boolean operator for the join clause.
second	string \| Expression	`false`		The second column or `Expression` to join the table on.
type	string	`false`	`"inner"`	The type of the join. Passing this as an argument is discouraged for readability. Use the dedicated methods like `leftJoinRaw` and `rightJoinRaw` where possible.
where	boolean	`false`	`false`	Sets if the value of `second` should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

Uses the raw SQL provided to as the table for the join clause. All the other functionality of joinRaw matches the join method. Additionally, there are leftJoinRaw, rightJoinRaw, and crossJoinRaw methods available.

{% code title="QueryBuilder" %}

query.from( "users" )
    .joinRaw( "posts (nolock)", "users.id", "posts.author_id" );

{% endcode %}

{% code title="SQL Server" %}

SELECT *
FROM [users]
JOIN posts (nolock)
  ON [users].[id] = [posts].[author_id]

{% endcode %}

{% hint style="warning" %} Using joinRaw will most likely tie your code to a specific database, so think carefully before using the joinRaw method if you want your project to be database agnostic. {% endhint %}

joinSub

Name	Type	Required	Default	Description
alias	string	`true`		The alias for the derived table.
input	Function \| QueryBuilder	`true`		Either a `QueryBuilder` instance or a function to define the derived query.
first	string \| Expression \| Function	`true`		The first column or `Expression` to join the table on. Alternatively, a function can be passed to configure complex join statements.
operator	string	`false`	`"="`	The boolean operator for the join clause.
second	string \| Expression	`false`		The second column or `Expression` to join the table on.
type	string	`false`	`"inner"`	The type of the join. Passing this as an argument is discouraged for readability. Use the dedicated methods like `leftJoinSub` and `rightJoinSub` where possible.
where	boolean	`false`	`false`	Sets if the value of `second` should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

Adds a join to a derived table. All the functionality of the join method applies to constrain the query. The derived table can be defined using a QueryBuilder instance:

{% code title="QueryBuilder" %}

var sub = query.newQuery()
    .select( "id" )
    .from( "contacts" )
    .whereNotIn( "id", [ 1, 2, 3 ] );

query.from( "users as u" )
    .joinSub( "c", sub, "u.id", "=", "c.id" );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `users` AS `u`
JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`

{% endcode %}

Alternatively, a function may be used to define the derived table:

{% code title="QueryBuilder" %}

query.from( "users as u" )
    .joinSub( "c", function ( q ) {
        q.select( "id" )
            .from( "contacts" )
            .whereNotIn( "id", [ 1, 2, 3 ] );
    }, "u.id", "=", "c.id" );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `users` AS `u`
JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`

{% endcode %}

Complex join conditions are also possible by passing a function as the third parameter:

{% code title="QueryBuilder" %}

query.from( "users as u" )
    .joinSub( "c", function ( q ) {
        q.select( "id" )
            .from( "contacts" )
            .whereNotIn( "id", [ 1, 2, 3 ] );
    }, function( j ) {
        j.on( "u.id", "c.id" );
        j.on( "u.type", "c.type" );
    } );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `users` AS `u`
JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`
  AND `u`.`type` = `c`.`type`

{% endcode %}

leftJoin

Name	Type	Required	Default	Description

table

string | Expression | JoinClause

true

The name of the table or a Expression object from which the query is based. Alternatively, a configured JoinClause instance can be passed.

(Note: a JoinClause instance may have a different join type than a left join. The JoinClause instance's join type will be used.)

first	string \| Expression \| Function	`false`		The first column or `Expression` to join the table on. Alternatively, a function can be passed to configure complex join statements.

operator	string	`false`	`"="`	The boolean operator for the join clause.

second	string \| Expression	`false`		The second column or `Expression` to join the table on.

where	boolean	`false`	`false`	Sets if the value of `second` should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

{% code title="QueryBuilder" %}

query.from( "posts" )
    .leftJoin( "users", "users.id", "posts.author_id" );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `posts`
LEFT JOIN `users`
  ON `users`.`id` = `posts`.`author_id`

{% endcode %}

leftJoinRaw

Name	Type	Required	Default	Description
table	string	`true`		The raw SQL string to use as the table.
first	string \| Expression \| Function	`false`		The first column or `Expression` to join the table on. Alternatively, a function can be passed to configure complex join statements.
operator	string	`false`	`"="`	The boolean operator for the join clause.
second	string \| Expression	`false`		The second column or `Expression` to join the table on.
where	boolean	`false`	`false`	Sets if the value of `second` should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

Uses the raw SQL provided to as the table for the left join clause. All the other functionality of leftJoinRaw matches the join method.

{% code title="QueryBuilder" %}

query.from( "posts" )
    .leftJoinRaw( "users (nolock)", "users.id", "posts.author_id" );

{% endcode %}

{% code title="SQL Server" %}

SELECT *
FROM [posts]
LEFT JOIN users (nolock)
  ON [users].[id] = [posts].[author_id]

{% endcode %}

{% hint style="warning" %} Using leftJoinRaw will most likely tie your code to a specific database, so think carefully before using the leftJoinRaw method if you want your project to be database agnostic. {% endhint %}

leftJoinSub

Name	Type	Required	Default	Description
alias	string	`true`		The alias for the derived table.
input	Function \| QueryBuilder	`true`		Either a `QueryBuilder` instance or a function to define the derived query.
first	string \| Expression \| Function	`true`		The first column or `Expression` to join the table on. Alternatively, a function can be passed to configure complex join statements.
operator	string	`false`	`"="`	The boolean operator for the join clause.
second	string \| Expression	`false`		The second column or `Expression` to join the table on.
where	boolean	`false`	`false`	Sets if the value of `second` should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

Adds a left join to a derived table. All the functionality of the joinSub method applies to define and constrain the query.

{% code title="QueryBuilder" %}

var sub = query.newQuery()
    .select( "id" )
    .from( "contacts" )
    .whereNotIn( "id", [ 1, 2, 3 ] );

query.from( "users as u" )
    .leftJoinSub( "c", sub, "u.id", "=", "c.id" );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `users` AS `u`
LEFT JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`

{% endcode %}

rightJoin

Name	Type	Required	Default	Description

table

string | Expression | JoinClause

true

The name of the table or a Expression object from which the query is based. Alternatively, a configured JoinClause instance can be passed.

(Note: a JoinClause instance may have a different join type than a right join. The JoinClause instance's join type will be used.)

first	string \| Expression \| Function	`false`		The first column or `Expression` to join the table on. Alternatively, a function can be passed to configure complex join statements.

operator	string	`false`	`"="`	The boolean operator for the join clause.

second	string \| Expression	`false`		The second column or `Expression` to join the table on.

where	boolean	`false`	`false`	Sets if the value of `second` should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

{% code title="QueryBuilder" %}

query.from( "users" )
    .rightJoin( "posts", "users.id", "posts.author_id" );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `users`
RIGHT JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

{% endcode %}

rightJoinRaw

Name	Type	Required	Default	Description
table	string	`true`		The raw SQL string to use as the table.
first	string \| Expression \| Function	`false`		The first column or `Expression` to join the table on. Alternatively, a function can be passed to configure complex join statements.
operator	string	`false`	`"="`	The boolean operator for the join clause.
second	string \| Expression	`false`		The second column or `Expression` to join the table on.
where	boolean	`false`	`false`	Sets if the value of `second` should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

Uses the raw SQL provided to as the table for the right join clause. All the other functionality of rightJoinRaw matches the join method.

{% code title="QueryBuilder" %}

query.from( "users" )
    .rightJoinRaw( "posts (nolock)", "users.id", "posts.author_id" );

{% endcode %}

{% code title="SQL Server" %}

SELECT *
FROM [users]
LEFT JOIN posts (nolock)
  ON [users].[id] = [posts].[author_id]

{% endcode %}

{% hint style="warning" %} Using rightJoinRaw will most likely tie your code to a specific database, so think carefully before using the rightJoinRaw method if you want your project to be database agnostic. {% endhint %}

rightJoinSub

Name	Type	Required	Default	Description
alias	string	`true`		The alias for the derived table.
input	Function \| QueryBuilder	`true`		Either a `QueryBuilder` instance or a function to define the derived query.
first	string \| Expression \| Function	`true`		The first column or `Expression` to join the table on. Alternatively, a function can be passed to configure complex join statements.
operator	string	`false`	`"="`	The boolean operator for the join clause.
second	string \| Expression	`false`		The second column or `Expression` to join the table on.
where	boolean	`false`	`false`	Sets if the value of `second` should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

Adds a right join to a derived table. All the functionality of the joinSub method applies to define and constrain the query.

{% code title="QueryBuilder" %}

var sub = query.newQuery()
    .select( "id" )
    .from( "contacts" )
    .whereNotIn( "id", [ 1, 2, 3 ] );

query.from( "users as u" )
    .rightJoinSub( "c", sub, "u.id", "=", "c.id" );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `users` AS `u`
RIGHT JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`

{% endcode %}

crossJoin

Name	Type	Required	Default	Description

table

string | Expression | JoinClause

true

The name of the table or a Expression object from which the query is based. Alternatively, a configured JoinClause instance can be passed.

(Note: a JoinClause instance may have a different join type than a cross join. The JoinClause instance's join type will be used.)

{% code title="QueryBuilder" %}

query.from( "users" ).crossJoin( "posts" );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `users`
CROSS JOIN `posts`

{% endcode %}

crossJoinRaw

Name	Type	Required	Default	Description
table	string	`true`		The raw SQL string to use as the table.

Uses the raw SQL provided to as the table for the cross join clause. Cross joins cannot be further constrained with on or where clauses.

{% code title="QueryBuilder" %}

query.from( "users" ).crossJoinRaw( "posts (nolock)" );

{% endcode %}

{% code title="SQL Server" %}

SELECT *
FROM [users]
CROSS JOIN posts (nolock)

{% endcode %}

{% hint style="warning" %} Using crossJoinRaw will most likely tie your code to a specific database, so think carefully before using the crossJoinRaw method if you want your project to be database agnostic. {% endhint %}

crossJoinSub

Name	Type	Required	Default	Description
alias	string	`true`		The alias for the derived table.
input	Function \| QueryBuilder	`true`		Either a `QueryBuilder` instance or a function to define the derived query.

Adds a cross join to a derived table. The derived table can be defined using a QueryBuilder instance or a function just as with joinSub. Cross joins cannot be constrained, however.

{% code title="QueryBuilder" %}

var sub = query.newQuery()
    .select( "id" )
    .from( "contacts" )
    .whereNotIn( "id", [ 1, 2, 3 ] );

query.from( "users as u" ).crossJoinSub( "c", sub );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `users` AS `u`
CROSS JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
)

{% endcode %}

newJoin

Name	Type	Required	Default	Description
table	string \| Expression	`true`		The name of the table or a `Expression` object from which the query is based.
type	string	`false`	`"inner"`	The type of the join. Valid types are `inner`, `left`, `right`, or `cross`.

Creates a new JoinClause. A JoinClause is a specialized version of a QueryBuilder. You may call on or orOn to constrain the JoinClause. You may also call any where methods.

Creating a JoinClause directly is useful when you need to share a join between different queries. You can create and configure the JoinClause in a function and pass it to queries as needed.

{% code title="QueryBuilder" %}

var j = query.newJoin( "contacts" )
    .on( "users.id", "posts.author_id" );

query.from( "users" ).join( j );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

{% endcode %}

{% hint style="warning" %} Although a JoinClause can be passed to join, leftJoin, rightJoin, and crossJoin, the type of the JoinClause will override the type of the function. {% endhint %}

{% code title="QueryBuilder" %}

// This is still an inner join because
// the JoinClause is an inner join
var j = query.newJoin( "contacts", "inner" )
    .on( "users.id", "posts.author_id" );

query.from( "users" ).leftJoin( j );

{% endcode %}

{% code title="MySQL" %}

-- This is still an inner join because
-- the JoinClause is an inner join
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

{% endcode %}

JoinClause

A JoinClause is a specialized version of a QueryBuilder. You may call on or orOn to constrain the JoinClause. You may also call any where methods.

on

Name	Type	Required	Default	Description
first	string \| Expression \| Function	`false`		The first column or `Expression`of the condition. Alternatively, a function can be passed to nest conditions with parenthesis.
operator	string	`false`	`"="`	The boolean operator for the condition.
second	string \| Expression	`false`		The second column or `Expression` of the condition.
combinator	string	`false`	`"and"`	The boolean combinator for the clause (e.g. "and" or "or").

Applies a join condition to the JoinClause. An alias for whereColumn.

{% code title="QueryBuilder" %}

var j = query.newJoin( "contacts" )
    .on( "users.id", "posts.author_id" );

query.from( "users" ).join( j );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

{% endcode %}

orOn

Name	Type	Required	Default	Description
first	string \| Expression \| Function	`false`		The first column or `Expression`of the condition. Alternatively, a function can be passed to nest conditions with parenthesis.
operator	string	`false`	`"="`	The boolean operator for the condition.
second	string \| Expression	`false`		The second column or `Expression` of the condition.

Applies a join condition to the JoinClause using an or combinator. An alias for orWhereColumn.

{% code title="QueryBuilder" %}

var j = query.newJoin( "contacts" )
    .on( "users.id", "posts.author_id" )
    .orOn( "users.id", "posts.reviewer_id" );

query.from( "users" ).join( j );

{% endcode %}

{% code title="MySQL" %}

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
  OR `users`.`id` = `posts`.`reviewer_id`

{% endcode %}

Preventing Duplicate Joins

You can optionally configure qb to ignore duplicate joins. With this setting turned on each JoinClause is inspected and checked if it matches any existing JoinClause instances on the query. This is useful if you have a table shared between optional constraints and want to ensure it is only added once.

You can opt-in to this behavior by setting preventDuplicateJoins = true in your moduleSettings in config/ColdBox.cfc.

moduleSettings = {
    "qb": {
         "preventDuplicateJoins": true  
    }
};

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

joins.md

joins.md

Joins

join

joinWhere

joinRaw

joinSub

leftJoin

leftJoinRaw

leftJoinSub

rightJoin

rightJoinRaw

rightJoinSub

crossJoin

crossJoinRaw

crossJoinSub

newJoin

JoinClause

on

orOn

Preventing Duplicate Joins

Files

joins.md

Latest commit

History

joins.md

File metadata and controls

Joins

join

joinWhere

joinRaw

joinSub

leftJoin

leftJoinRaw

leftJoinSub

rightJoin

rightJoinRaw

rightJoinSub

crossJoin

crossJoinRaw

crossJoinSub

newJoin

JoinClause

on

orOn

Preventing Duplicate Joins