UNION OperatorUse the UNION operator to combine the results of two or more SELECT statements. The joined statements must have the same number of columns and the same data types for corresponding columns.
SQL for CRM Analytics supports UNION ALL only. This clause returns all results—it doesn’t remove duplicate values. Let’s see what happens when we combine two SELECT statements that retrieve the Cities field.
1SELECT City From "Superstore" UNION ALL SELECT City from "Superstore";The query returns all City results in an unspecified order.
| City |
|---|
| Henderson |
| Henderson |
| Los Angeles |
| Fort Lauderdale |
| Fort Lauderdale |
| Los Angeles |
| Los Angeles |
| Los Angeles |
| Los Angeles |
| Los Angeles |
To include an ORDER BY or LIMIT clause when using the UNION operator, you must enclose the first SELECT statement in parentheses so that the SQL parser can identify the UNION operator.
In this example, ORDER BY City DESC applies to the results of the full query, not only the second SELECT clause. The results display in reverse alphabetical order for the combined set of the City field.
1(SELECT City
2FROM "Superstore"
3ORDER BY City ASC)
4UNION ALL
5SELECT City
6FROM "Superstore"
7ORDER BY City DESC;| City |
|---|
| Yuma |
| Yuma |
| Yuma |
| Yuma |
| Yuma |
| Yuma |
| Yuma |
| Yuma |
| Yucaipa |
| Yucaipa |
| York |
If you include parentheses around the second SELECT statement, the query returns results from the first SELECT statement in ascending order and the second in descending order.
1(SELECT City
2FROM "Superstore"
3ORDER BY City ASC)
4UNION ALL
5(SELECT City
6FROM "Superstore"
7ORDER BY City DESC);The first ten results show values from the first SELECT statement in ascending order.
| City |
|---|
| Aberdeen |
| Abilene |
| Akron |
| Akron |
| Akron |
| Akron |
| Akron |
| Akron |
| Akron |
| Akron |
j