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 | | | |
|
Name | Type | Required | Default | Description |
table | 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 | 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 | 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 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:
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`
``
Expressions
are also supported as the table
argument (though you may prefer the readability of the joinRaw
method):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]
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.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
A preconfigured
JoinClause
can also be passed to the join function. This allows you to extract shared pieces of code out to different functions.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`
Name | Type | Required | Default | Description |
table | string | true | | The raw SQL string to use as the table. |
first | 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 | 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` < ?
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.Name | Type | Required | Default | Description |
table | string | true | | The raw SQL string to use as the table. |
first | 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 | false | | ||
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.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]
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.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 | | 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 | false | | ||
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: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`
Name | Type | Required | Default | Description |
table | 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 | 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`
Name | Type | Required | Default | Description |
table | string | true | | The raw SQL string to use as the table. |
first | 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 | 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. |
Uses the raw SQL provided to as the table for the left join clause. All the other functionality of
leftJoinRaw
matches the join
method.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]
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.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 | | 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 | 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. |
Adds a left join to a derived table. All the functionality of the
joinSub
method applies to define and constrain the query.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`
Name | Type | Required | Default | Description |
table | 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 | 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 | 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`
Name | Type | Required | Default | Description |
table | string | true | | The raw SQL string to use as the table. |
first | 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 | 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. |
Uses the raw SQL provided to as the table for the right join clause. All the other functionality of
rightJoinRaw
matches the join
method.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]
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.