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

Name

Type

Required

Default

Description

table

true

​

first

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

type

string

false

"inner"

where

boolean

false

false

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

QueryBuilder
query.from( "users" )
    .join( "posts", "users.id", "=", "posts.author_id" );
MySQL
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

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

QueryBuilder
query.from( "users" )
    .join( "posts", "users.id", "posts.author_id" );
MySQL
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
QueryBuilder
query.from( "users" )
    .join( query.raw( "posts (nolock)" ), "users.id", "=", "posts.author_id" );
SQL Server
SELECT *
FROM [users]
JOIN posts (nolock)
  ON [users].[id] = [posts].[author_id]

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

QueryBuilder
query.from( "users" )
    .join( "posts", function( j ) {
        j.on( "users.id", "=", "posts.author_id" );
        j.on( "users.prefix", "=", "posts.prefix" );
    } );
MySQL
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
  AND `users`.`prefix` = `posts`.`prefix`
QueryBuilder
query.from( "users" )
    .join( "posts", function( j ) {
        j.on( "users.id", "=", "posts.author_id" );
        j.whereNotNull( "posts.published_date" );
    } );
MySQL
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
  AND `posts`.`published_date` IS NOT NULL

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

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" );
    } );
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
QueryBuilder
var j = query.newJoin( "contacts" )
    .on( "users.id", "posts.author_id" );

query.from( "users" ).join( j );
MySQL
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

joinWhere

Name

Type

Required

Default

Description

table

string

true

​

The raw SQL string to use as the table.

first

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

type

string

false

"inner"

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:

QueryBuilder
query.from( "users" )
    .joinWhere( "contacts", "contacts.balance", "<", 100 );
MySQL
SELECT *
FROM `users`
JOIN `contacts`
  WHERE `contacts`.`balance` < ?

joinRaw

Name

Type

Required

Default

Description

table

string

true

​

The raw SQL string to use as the table.

first

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

type

string

false

"inner"

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.

QueryBuilder
query.from( "users" )
    .joinRaw( "posts (nolock)", "users.id", "posts.author_id" );
SQL Server
SELECT *
FROM [users]
JOIN posts (nolock)
  ON [users].[id] = [posts].[author_id]

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

true

operator

string

false

"="

The boolean operator for the join clause.

second

false

type

string

false

"inner"

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.

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" );
MySQL
SELECT *
FROM `users` AS `u`
JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`

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

QueryBuilder
query.from( "users as u" )
    .joinSub( "c", function ( q ) {
        q.select( "id" )
            .from( "contacts" )
            .whereNotIn( "id", [ 1, 2, 3 ] );
    }, "u.id", "=", "c.id" );
MySQL
SELECT *
FROM `users` AS `u`
JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`

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

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" );
    } );
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`

leftJoin

Name

Type

Required

Default

Description

table

true

​

first

string | Expression | Function

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

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.

QueryBuilder
query.from( "posts" )
    .leftJoin( "users", "users.id", "posts.author_id" );
MySQL
SELECT *
FROM `posts`
LEFT JOIN `users`
  ON `users`.`id` = `posts`.`author_id`

leftJoinRaw

Name

Type

Required

Default

Description

table

string

true

​

The raw SQL string to use as the table.

first

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

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.

QueryBuilder
query.from( "posts" )
    .leftJoinRaw( "users (nolock)", "users.id", "posts.author_id" );
SQL Server
SELECT *
FROM [posts]
LEFT JOIN users (nolock)
  ON [users].[id] = [posts].[author_id]

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

true

operator

string

false

"="

The boolean operator for the join clause.

second

false

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.

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" );
MySQL
SELECT *
FROM `users` AS `u`
LEFT JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`

rightJoin

Name

Type

Required

Default

Description

table

true

​

first

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

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.

QueryBuilder
query.from( "users" )
    .rightJoin( "posts", "users.id", "posts.author_id" );
MySQL
SELECT *
FROM `users`
RIGHT JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

rightJoinRaw

Name

Type

Required

Default

Description

table

string

true

​

The raw SQL string to use as the table.

first

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

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.

QueryBuilder
query.from( "users" )
    .rightJoinRaw( "posts (nolock)", "users.id", "posts.author_id" );
SQL Server
SELECT *
FROM [users]
LEFT JOIN posts (nolock)
  ON [users].[id] = [posts].[author_id]

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

true

operator

string

false

"="

The boolean operator for the join clause.

second

false

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.

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" );
MySQL
SELECT *
FROM `users` AS `u`
RIGHT JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`

crossJoin

Name

Type

Required

Default

Description

table

true

​

QueryBuilder
query.from( "users" ).crossJoin( "posts" );
MySQL
SELECT *
FROM `users`
CROSS JOIN `posts`

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.

QueryBuilder
query.from( "users" ).crossJoinRaw( "posts (nolock)" );
SQL Server
SELECT *
FROM [users]
CROSS JOIN posts (nolock)

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.

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

query.from( "users as u" ).crossJoinSub( "c", sub );
MySQL
SELECT *
FROM `users` AS `u`
CROSS JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
)

newJoin

Name

Type

Required

Default

Description

table

true

​

type

string

false

"inner"

The type of the join. Valid types are inner, left, right, or cross.

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

query.from( "users" ).join( j );
MySQL
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
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 );
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`

JoinClause

on

Name

Type

Required

Default

Description

first

false

operator

string

false

"="

The boolean operator for the condition.

second

false

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.

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

query.from( "users" ).join( j );
MySQL
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

orOn

Name

Type

Required

Default

Description

first

false

operator

string

false

"="

The boolean operator for the condition.

second

false

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

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

query.from( "users" ).join( j );
MySQL
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
  OR `users`.`id` = `posts`.`reviewer_id`

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  
    }
};

Was this helpful?