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:
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`
You can specify where clauses in your joins as well.
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`
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:
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.
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.
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.
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:
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
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.
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
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.
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.
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.
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
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.
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
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.
QueryBuilder
query.from( "users" )
    .rightJoinRaw( "posts (nolock)", "users.id", "posts.author_id" );
SQL Server
SELECT *
Table of Contents
join	joinRaw	joinSub	joinWhere
leftJoin	leftJoinRaw	leftJoinSub	newJoin
rightJoin	rightJoinRaw	rightJoinSub	JoinClause
crossJoin	crossJoinRaw	crossJoinSub
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.