UNION Operator


 

 

SQLite: UNION Operator

This SQLite tutorial explains how to use the SQLite UNION operator with syntax and examples.

 

Description:

  • The SQLite UNION operator is used to combine the result sets of 2 or more SELECT statements. It removes duplicate rows between the various SELECT statements.
  • The SQLite UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It returns all rows from the query (even if the row exists in more than one of the SELECT statements).

 

Note: Each SELECT statement within the SQLite UNION and UNION ALL operator must have the same number of fields in the result sets with similar data types.

 

Syntax:

UNION Operator Syntax:

 

Parameters or Arguments:

expression1, expression2, … expression_n

The columns or calculations that you wish to retrieve.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

conditions

The conditions that must be met for the records to be selected.

 

Note: There must be same number of expressions in both SELECT statements. Since the UNION operator by default removes all duplicate rows from the result set, providing the UNION DISTINCT modifier has no effect on the results. The column names from the first SELECT statement in the UNION operator are used as the column names for the result set.

 

UNION ALL Operator Syntax:

 

Parameters or Arguments:

expression1, expression2, … expression_n

The columns or calculations that you wish to retrieve.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

conditions

The conditions that must be met for the records to be selected.

 

Note: There must be same number of expressions in both SELECT statements. The column names from the first SELECT statement are used as the column names for the result set.

 

Examples:

  • The following SQLite statement selects all the different id (only distinct values) from the “Employee” and the “Account” tables: (Since id is primary key, all values will be distinct only. In your case, this might be different if you use UNION with different column.)

    id
    1
    2
    3
    4
    5
    6
    7
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109