SQL оператор UNION
В этом учебном материале вы узнаете, как использовать SQL оператор UNION с синтаксисом и примерами.
Описание
SQL оператор UNION используется для объединения результирующих наборов из 2 или более операторов SELECT. Он удаляет повторяющиеся строки между различными запросами SELECT.
Каждый оператор SELECT в UNION должен иметь одинаковое количество полей в наборах результатов с одинаковыми типами данных.
В чем разница между UNION и UNION ALL
- UNION удаляет повторяющиеся строки
- Оператор UNION ALLне удаляет повторяющиеся строки
Синтаксис
Синтаксис для оператора UNION в SQL.
Параметры или аргумент
Примечание
- В обоих операторах SELECT должно быть одинаковое количество выражений
- Соответствующие выражения должны иметь одинаковый тип данных в запросах SELECT. Например: expression1 должен иметь одинаковый тип данных как в первом, так и во втором операторе SELECT
- Смотрите также оператор UNION ALL
Пример — одиночное поле с тем же именем
Давайте посмотрим, как использовать SQL оператор UNION, который возвращает одно поле. В этом простом примере поле в обоих операторах SELECT будет иметь одинаковое имя и тип данных.
Например.
В этом SQL примере оператора UNION, если supplier_id появилось в таблицах suppliers и orders , оно будет один раз в вашем наборе результатов. Оператор UNION удаляет дубликаты. Если вы не хотите удалить дубликаты, попробуйте использовать оператор UNION ALL.
Теперь давайте рассмотрим этот пример, далее приведем некоторые данные.
Если у вас была таблица suppliers , заполненная следующими записями.
| supplier_id | supplier_name |
|---|---|
| 1000 | Yandex |
| 2000 | |
| 3000 | Oracle |
| 4000 | Bing |
И таблица orders заполнена следующими записями.
| order_id | order_date | supplier_id |
|---|---|---|
| 2019-07-01 | 2000 | |
| 2019-07-01 | 6000 | |
| 2019-07-02 | 7000 | |
| 2019-07-03 | 8000 |
И вы выполнили следующий запрос UNION.
Вы получите следующие результаты.
| supplier_id |
|---|
| 1000 |
| 2000 |
| 3000 |
| 4000 |
| 6000 |
| 7000 |
| 8000 |
Как видно из этого примера, UNION взял все значения supplier_id из таблицы suppliers , а также из таблицы orders и возвратил комбинированный набор результатов. Поскольку оператор UNION удалил дубликаты между результирующими наборами, поле supplier_id 2000 отображается только один раз, даже если оно находится в таблицах suppliers и orders . Если вы не хотите удалять дубликаты, попробуйте вместо этого использовать оператор UNION ALL.
Пример — разные имена полей
Нет необходимости, чтобы соответствующие столбцы в каждом операторе SELECT имели одинаковые имена, но они должны быть с одинаковыми, соответствующими типами данных.
Если у вас нет одинаковых имен столбцов между операторами SELECT, это становится немного сложнее, особенно если вы хотите упорядочить результаты запроса, используя оператор ORDER BY.
Давайте посмотрим, как использовать оператор UNION с разными именами столбцов и упорядочиванием результатов запроса.
Например.
Что делает union в sql
You can combine multiple queries using the set operators UNION , UNION ALL , INTERSECT , INTERSECT ALL , EXCEPT , EXCEPT ALL , MINUS , and MINUS ALL . All set operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.
The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same data type group (such as numeric or character).
If component queries select character data, then the data type of the return values are determined as follows:
If both queries select values of data type CHAR of equal length, then the returned values have data type CHAR of that length. If the queries select values of CHAR with different lengths, then the returned value is VARCHAR2 with the length of the larger CHAR value.
If either or both of the queries select values of data type VARCHAR2 , then the returned values have data type VARCHAR2 .
If component queries select numeric data, then the data type of the return values is determined by numeric precedence:
If any query selects values of type BINARY_DOUBLE , then the returned values have data type BINARY_DOUBLE .
If no query selects values of type BINARY_DOUBLE but any query selects values of type BINARY_FLOAT , then the returned values have data type BINARY_FLOAT .
If all queries select values of type NUMBER , then the returned values have data type NUMBER .
In queries using set operators, Oracle does not perform implicit conversion across data type groups. Therefore, if the corresponding expressions of component queries resolve to both character data and numeric data, Oracle returns an error.
The INTERSECT operator with the keyword ALL returns the result of two or more SELECT statements in which rows appear in all result sets. Null values that are common across the component queries of INTERSECT ALL are returned at the end of the result set.
The MINUS operator with the keyword ALL returns the result of two SELECT statements in which rows appear in the first result set but not in the second result set.
If the first query has x nulls and the second query has y nulls, and x is greater than y , then x minus y NULLS are returned at the end of the result query set. MINUS ALL returns no rows if the result set returned by the first SELECT statement is a subset of the result set returned by the second SELECT .
The EXCEPT operator is a synonym for MINUS and has the exact same semantics. EXCEPT ALL returns rows that are present in the first result set but not in the second. However, duplicates may be present in the final result.
EXCEPT ALL , MINUS ALL INTERSECT ALL return equivalent instead of the original value, when NLS_SORT=BINARY_CI[AI] is acceptable for the SQL standard.
Table 2-8 for more information on implicit conversion and «Numeric Precedence» for information on numeric precedence
Examples for Valid and Invalid Data Type Conversions for Set Operators
The following query is valid:
This is implicitly converted to the following compound query:
The following query returns an error:
Restrictions on the Set Operators
The set operators are subject to the following restrictions:
The set operators are not valid on columns of type BLOB , CLOB , BFILE , VARRAY , or nested table.
The UNION , INTERSECT , EXCEPT , and MINUS operators are not valid on LONG columns.
If the select list preceding the set operator contains an expression, then you must provide a column alias for the expression in order to refer to it in the order_by_clause .
You cannot also specify the for_update_clause with the set operators.
You cannot specify the order_by_clause in the subquery of these operators.
You cannot use these operators in SELECT statements containing TABLE collection expressions.
To comply with emerging SQL standards, a future release of Oracle will give the INTERSECT operator greater precedence than the other set operators. Therefore, you should use parentheses to specify order of evaluation in queries that use the INTERSECT operator with other set operators.
The following statement combines the results of two queries with the UNION operator, which eliminates duplicate selected rows. This statement shows that you must match data type (using the TO_CHAR function) when columns do not exist in one or the other table:
UNION ALL Example
The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows:
A location_id value that appears multiple times in either or both queries (such as ‘ 1700 ‘) is returned only once by the UNION operator, but multiple times by the UNION ALL operator.
The following statement combines the results with the INTERSECT operator, which returns only those unique rows returned by both queries:
The following statement combines results with the MINUS operator, which returns only unique rows returned by the first query but not by the second:
You can use EXCEPT or MINUS when you want to exclude a result set from the final result set. In this example, the result of the second query is ignored.
The following statement combines results with the EXCEPT operator, which returns only unique rows returned by the first query but not by the second:
SQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements.
- Every SELECT statement within UNION must have the same number of columns
- The columns must also have similar data types
- The columns in every SELECT statement must also be in the same order
UNION Syntax
UNION ALL Syntax
The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL :
Note: The column names in the result-set are usually equal to the column names in the first SELECT statement.
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the «Customers» table:
And a selection from the «Suppliers» table:
| SupplierID | SupplierName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Exotic Liquid | Charlotte Cooper | 49 Gilbert St. | London | EC1 4SD | UK |
| 2 | New Orleans Cajun Delights | Shelley Burke | P.O. Box 78934 | New Orleans | 70117 | USA |
| 3 | Grandma Kelly’s Homestead | Regina Murphy | 707 Oxford Rd. | Ann Arbor | 48104 | USA |
SQL UNION Example
The following SQL statement returns the cities (only distinct values) from both the «Customers» and the «Suppliers» table:
Example
Note: If some customers or suppliers have the same city, each city will only be listed once, because UNION selects only distinct values. Use UNION ALL to also select duplicate values!
SQL UNION ALL Example
The following SQL statement returns the cities (duplicate values also) from both the «Customers» and the «Suppliers» table:
Example
SQL UNION With WHERE
The following SQL statement returns the German cities (only distinct values) from both the «Customers» and the «Suppliers» table:
Example
SQL UNION ALL With WHERE
The following SQL statement returns the German cities (duplicate values also) from both the «Customers» and the «Suppliers» table:
Example
Another UNION Example
The following SQL statement lists all customers and suppliers:
Example
Notice the «AS Type» above — it is an alias. SQL Aliases are used to give a table or a column a temporary name. An alias only exists for the duration of the query. So, here we have created a temporary column named «Type», that list whether the contact person is a «Customer» or a «Supplier».
Name already in use
sql-docs / docs / t-sql / language-elements / set-operators-union-transact-sql.md
- Go to file T
- Go to line L
- Copy path
- Copy permalink
- Open with Desktop
- View raw
- Copy raw contents Copy raw contents
Copy raw contents
Copy raw contents
Set Operators — UNION (Transact-SQL)
Concatenates the results of two queries into a single result set. You control whether the result set includes duplicate rows:
- UNION ALL — Includes duplicates.
- UNION — Excludes duplicates.
A UNION operation is different from a JOIN:
- A UNION concatenates result sets from two queries. But a UNION does not create individual rows from columns gathered from two tables.
- A JOIN compares columns from two tables, to create result rows composed of columns from two tables.
The following are basic rules for combining the result sets of two queries by using UNION:
The number and the order of the columns must be the same in all queries.
The data types must be compatible.
. image type=»icon» source=»../../includes/media/topic-link-icon.svg» border=»false». Transact-SQL syntax conventions
<query_specification> | ( <query_expression> ) Is a query specification or query expression that returns data to be combined with the data from another query specification or query expression. The definitions of the columns that are part of a UNION operation don’t have to be the same, but they must be compatible through implicit conversion. When data types differ, the resulting data type is determined based on the rules for data type precedence. When the types are the same but differ in precision, scale, or length, the result is based on the same rules for combining expressions. For more information, see Precision, Scale, and Length (Transact-SQL).
Columns of the xml data type must be equal. All columns must be either typed to an XML schema or untyped. If typed, they must be typed to the same XML schema collection.
UNION
Specifies that multiple result sets are to be combined and returned as a single result set.
ALL
Incorporates all rows into the results, including duplicates. If not specified, duplicate rows are removed.
A. Using a simple UNION
In the following example, the result set includes the contents of the ProductModelID and Name columns of both the ProductModel and Gloves tables.
B. Using SELECT INTO with UNION
In the following example, the INTO clause in the second SELECT statement specifies that the table named ProductResults holds the final result set of the union of the selected columns of the ProductModel and Gloves tables. The Gloves table is created in the first SELECT statement.
C. Using UNION of two SELECT statements with ORDER BY
The order of certain parameters used with the UNION clause is important. The following example shows the incorrect and correct use of UNION in two SELECT statements in which a column is to be renamed in the output.
D. Using UNION of three SELECT statements to show the effects of ALL and parentheses
The following examples use UNION to combine the results of three tables that all have the same 5 rows of data. The first example uses UNION ALL to show the duplicated records, and returns all 15 rows. The second example uses UNION without ALL to eliminate the duplicate rows from the combined results of the three SELECT statements, and returns 5 rows.
The third example uses ALL with the first UNION and parentheses enclose the second UNION that isn’t using ALL . The second UNION is processed first because it’s in parentheses, and returns 5 rows because the ALL option isn’t used and the duplicates are removed. These 5 rows are combined with the results of the first SELECT by using the UNION ALL keywords. This example doesn’t remove the duplicates between the two sets of five rows. The final result has 10 rows.
E. Using a simple UNION
In the following example, the result set includes the contents of the CustomerKey columns of both the FactInternetSales and DimCustomer tables. Since the ALL keyword isn’t used, duplicates are excluded from the results.
F. Using UNION of two SELECT statements with ORDER BY
When any SELECT statement in a UNION statement includes an ORDER BY clause, that clause should be placed after all SELECT statements. The following example shows the incorrect and correct use of UNION in two SELECT statements in which a column is ordered with ORDER BY.
G. Using UNION of two SELECT statements with WHERE and ORDER BY
The following example shows the incorrect and correct use of UNION in two SELECT statements where WHERE and ORDER BY are needed.
H. Using UNION of three SELECT statements to show effects of ALL and parentheses
The following examples use UNION to combine the results of the same table to demonstrate the effects of ALL and parentheses when using UNION .
The first example uses UNION ALL to show duplicated records and returns each row in the source table three times. The second example uses UNION without ALL to eliminate the duplicate rows from the combined results of the three SELECT statements and returns only the unduplicated rows from the source table.
The third example uses ALL with the first UNION and parentheses enclosing the second UNION that isn’t using ALL . The second UNION is processed first because it is in parentheses. It returns only the unduplicated rows from the table because the ALL option isn’t used and duplicates are removed. These rows are combined with the results of the first SELECT by using the UNION ALL keywords. This example doesn’t remove the duplicates between the two sets.