Unions

Search…
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 the UNION 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. The union clause only returns unique rows.
unionAll() — This builds a SQL statement using the UNION ALL clause. This is the same as union 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 OrderByNotAllowedexception. To order the results, add an orderBy() call to the parent source Query Builder instance.
union
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
1
query.from( "users" )
2
    .select( "name" )
3
    .where( "id", 1 )
4
    .union( function ( q ) {
5
        q.from( "users" )
6
            .select( "name" )
7
            .where( "id", 2 );
8
    } );
Copied!
MySQL
1
SELECT `name`
2
FROM `users`
3
WHERE `id` = ?
4
UNION
5
SELECT `name`
6
FROM `users`
7
WHERE `id` = ?
Copied!
Adding multiple union statements will append it to the query.
QueryBuilder
1
query.from( "users" )
2
    .select( "name" )
3
    .where( "id", 1 )
4
    .union( function ( q ) {
5
        q.from( "users" )
6
            .select( "name" )
7
            .where( "id", 2 );
8
    } )
9
    .union( function ( q ) {
10
        q.from( "users" )
11
            .select("name")
12
            .where( "id", 3 );
13
    } );
Copied!
MySQL
1
SELECT `name`
2
FROM `users`
3
WHERE `id` = ?
4
UNION
5
SELECT `name`
6
FROM `users`
7
WHERE `id` = ?
8
UNION
9
SELECT `name`
10
FROM `users`
11
WHERE `id` = ?
Copied!
It can also add union queries as QueryBuilder instances.
QueryBuilder
1
var q1 = query.newQuery()
2
    .from( "users" )
3
    .select( "name" )
4
    .where( "id", 2 );
5
    
6
var q2 = query.newQuery()
7
    .from( "users" )
8
    .select( "name" )
9
    .where( "id", 3 );
10
​
11
query.from( "users" )
12
    .select( "name" )
13
    .where( "id", 1 )
14
    .union( q1 )
15
    .union( q2 );
Copied!
MySQL
1
SELECT `name`
2
FROM `users`
3
WHERE `id` = ?
4
UNION
5
SELECT `name`
6
FROM `users`
7
WHERE `id` = ?
8
UNION
9
SELECT `name`
10
FROM `users`
11
WHERE `id` = ?
Copied!
unionAll
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
1
query.from( "users" )
2
    .select( "name" )
3
    .where( "id", 1 )
4
    .unionAll( function( q ) {
5
        q.from( "users" )
6
            .select( "name" )
7
            .where( "id", 2 );
8
     } );
Copied!
MySQL
1
SELECT `name`
2
FROM `users`
3
WHERE `id` = ?
4
UNION ALL
5
SELECT `name`
6
FROM `users`
7
WHERE `id` = ?
Copied!
Adding multiple unionAll statements will append it to the query.
QueryBuilder
1
query.from( "users" )
2
    .select( "name" )
3
    .where( "id", 1 )
4
    .unionAll( function( q ) {
5
        q.from( "users" )
6
            .select( "name" )
7
            .where( "id", 2 );
8
     } )
9
    .unionAll( function( q ) {
10
        q.from( "users" )
11
            .select( "name" )
12
            .where( "id", 3 );
13
     } );
Copied!
MySQL
1
SELECT `name`
2
FROM `users`
3
WHERE `id` = ?
4
UNION ALL
5
SELECT `name`
6
FROM `users`
7
WHERE `id` = ?
8
UNION ALL
9
SELECT `name`
10
FROM `users`
11
WHERE `id` = ?
Copied!
It can also add union queries as QueryBuilder instances.
QueryBuilder
1
var q1 = query.newQuery()
2
    .from( "users" )
3
    .select( "name" )
4
    .where( "id", 2 );
5
    
6
var q2 = query.newQuery()
7
    .from( "users" )
8
    .select( "name" )
9
    .where( "id", 3 );
10
​
11
query.from( "users" )
12
    .select( "name" )
13
    .where( "id", 1 )
14
    .unionAll( q1 )
15
    .unionAll( q2 );
Copied!
MySQL
1
SELECT `name`
2
FROM `users`
3
WHERE `id` = ?
4
UNION ALL
5
SELECT `name`
6
FROM `users`
7
WHERE `id` = ?
8
UNION ALL
9
SELECT `name`
10
FROM `users`
11
WHERE `id` = ?
Copied!
​
Locks
Common Table Expressions (i.e. CTEs)
Last modified 2yr ago
Copy link
Edit on GitHub
Contents
union
unionAll