Unions
The query builder also lets you create union statements on your queries using either
UNION
or UNION ALL
strategies.The
union
methods take either a Query Builder instance or a closure which you use to define a new QueryBuilder instance.Union statements are added in the order in which the
union
methods are invoked, but the union
statements can be in any order in your API call stack. This means you can safely declare your union
method calls before the select
, from
and orderBy
calls on the source Query Builder instance.union()
— This method builds a SQL statement using theUNION
clause which combines two SQL queries into a single result set containing all the matching rows. The two queries must have the same defined columns and compatible data types or the SQL engine will generate an error. Theunion
clause only returns unique rows.unionAll()
— This builds a SQL statement using theUNION ALL
clause. This is the same asunion
but includes duplicate rows.
IMPORTANT: The QueryBuilder instances passed to a
union
statement cannot contain a defined order. Any use of the orderBy()
method on the unioned QueryBuilder instances will result in an OrderByNotAllowed
exception. To order the results, add an orderBy()
call to the parent source Query Builder instance.Name | Type | Required | Default | Description |
input | Function | QueryBuilder | true | | The function or QueryBuilder instance to use as the unioned query. |
all | boolean | false | false | Determines if statement should be a "UNION ALL". Passing this as an argument is discouraged. Use the dedicated unionAll where possible. |
Adds a UNION statement to the query.
QueryBuilder
query.from( "users" )
.select( "name" )
.where( "id", 1 )
.union( function ( q ) {
q.from( "users" )
.select( "name" )
.where( "id", 2 );
} );
MySQL
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION
SELECT `name`
FROM `users`
WHERE `id` = ?
Adding multiple union statements will append it to the query.
QueryBuilder
query.from( "users" )
.select( "name" )
.where( "id", 1 )
.union( function ( q ) {
q.from( "users" )
.select( "name" )
.where( "id", 2 );
} )
.union( function ( q ) {
q.from( "users" )
.select("name")
.where( "id", 3 );
} );
MySQL
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION
SELECT `name`
FROM `users`
WHERE `id` = ?
It can also add union queries as QueryBuilder instances.
QueryBuilder
var q1 = query.newQuery()
.from( "users" )
.select( "name" )
.where( "id", 2 );
var q2 = query.newQuery()
.from( "users" )
.select( "name" )
.where( "id", 3 );
query.from( "users" )
.select( "name" )
.where( "id", 1 )
.union( q1 )
.union( q2 );
MySQL
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION
SELECT `name`
FROM `users`
WHERE `id` = ?
Name | Type | Required | Default | Description |
input | Function | QueryBuilder | true | | The function or QueryBuilder instance to use as the unioned query. |
Adds a UNION ALL statement to the query.
QueryBuilder
query.from( "users" )
.select( "name" )
.where( "id", 1 )
.unionAll( function( q ) {
q.from( "users" )
.select( "name" )
.where( "id", 2 );
} );
MySQL
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION ALL
SELECT `name`
FROM `users`
WHERE `id` = ?
Adding multiple
unionAll
statements will append it to the query.QueryBuilder
query.from( "users" )
.select( "name" )
.where( "id", 1 )
.unionAll( function( q ) {
q.from( "users" )
.select( "name" )
.where( "id", 2 );
} )
.unionAll( function( q ) {
q.from( "users" )
.select( "name" )
.where( "id", 3 );
} );
MySQL
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION ALL
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION ALL
SELECT `name`
FROM `users`
WHERE `id` = ?
It can also add union queries as QueryBuilder instances.
QueryBuilder
var q1 = query.newQuery()
.from( "users" )
.select( "name" )
.where( "id", 2 );
var q2 = query.newQuery()
.from( "users" )
.select( "name" )
.where( "id", 3 );
query.from( "users" )
.select( "name" )
.where( "id", 1 )
.unionAll( q1 )
.unionAll( q2 );
MySQL
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION ALL
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION ALL
SELECT `name`
FROM `users`
WHERE `id` = ?
Last modified 11d ago