UNION

UNION is used to combine the result from multiple SELECT statements into a single result set.

Syntax

SELECT ...
UNION [DISTINCT] SELECT ...
[UNION [DISTINCT] SELECT ...]

The UNION statement is used as follows:

  1. Each SELECT statement must provide the same number of columns. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.
  1. The column names from the first SELECT statement are used as the column names for the results returned.

  2. Only the last SELECT can contain ORDER BY and LIMIT. ORDER BY and LIMIT will be applied on the entire results. The ORDER BY clause must use the column names for the first SELECT. 

  3. The default behavior for UNION is that all rows from all SELECT statements are returned. With the optional DISTINCT keyword duplicate rows are removed from the results. Mxed UNION types are treated such that a DISTINCT union overrides any ALL union to its left.

    Example: to retrieve data from Employees where ID is less than 10 and union where ID is greater than 20:

    SELECT ID, Title FROM Employees WHERE ID < 10
    UNION
    SELECT ID, Title FROM Employees WHERE ID > 20

    Example: to retrieve distinct results from two lists:

    SELECT ID, Title FROM Employees
    UNION DISTINCT
    SELECT ID, Title FROM EmployeesBackup

Code examples

Select all customers including backups

using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand(@"SELECT ID, Title FROM Customers UNION SELECT ID, Title FROM CustomersBackup ORDER BY ID ASC", connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["ID"].ToString().PadRight(30) + " : " + reader["Title"].ToString());
            }
        }
    }
}

Code result

1                              : Sven Andersson
1                              : Sven Andersson
2                              : John Smith
2                              : John Smith
3                              : Brett Wayne
3                              : Brett Wayne
4                              : Nils Larsson
4                              : Nils Larsson
5                              : Tim Sherwood
5                              : Tim Sherwood
6                              : Adam Sandler
6                              : Adam Sandler
7                              : Mary Anderson
7                              : Mary Anderson
8                              : Brian Aherne
8                              : Brian Aherne
9                              : Steve Allen
9                              : Steve Allen
10                             : John Boles
10                             : John Boles
11                             : Anders Englund
11                             : Anders Englund
12                             : King Carl XVI Gustaf
12                             : King Carl XVI Gustaf
15                             : Jerry Buss
15                             : Jerry Buss

Select all distinct customers including backups

using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand(@"SELECT ID, Title FROM Customers UNION DISTINCT SELECT ID, Title FROM CustomersBackup ORDER BY ID ASC", connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["ID"].ToString().PadRight(30) + " : " + reader["Title"].ToString());
            }
        }
    }
}

Code result

1                              : Sven Andersson
2                              : John Smith
3                              : Brett Wayne
4                              : Nils Larsson
5                              : Tim Sherwood
6                              : Adam Sandler
7                              : Mary Anderson
8                              : Brian Aherne
9                              : Steve Allen
10                             : John Boles
11                             : Anders Englund
12                             : King Carl XVI Gustaf
15                             : Jerry Buss


Skip Navigation Links.

Visa     MasterCard