Use of Oracle Collections ____oracle

Each group of queries can get its result set, if the need to combine multiple query results into a result set, can be implemented by the set operation.

such as union, and set, and do not remove duplicate rows (unoin all), intersections (INTERSECT), Difference sets (minus).

The purpose of the collection command is to merge the results of two (including above) SQL statements. Because you need to set up two (or more) data collections and

Therefore, the number of fields must be equal and the type must be compatible.

If the number of fields cannot be the same, the insufficient part can be replaced with a null value so that its field quantity meets the requirements.

Create two tables:

Sql> CREATE TABLE Test1 (
2 ID int primary KEY,
3 name VARCHAR2 (200)
4);
Sql> CREATE TABLE Test2 (
2 ID int primary KEY,
3 name VARCHAR2 (200)
4);
sql> INSERT INTO test1 values (1, ' Zhangsan ');
sql> INSERT INTO test1 values (2, ' Lisi ');
sql> INSERT into test1 values (3, ' Wangwu ');
sql> INSERT INTO test1 values (4, ' Zhaoliu ');

sql> INSERT into test2 values (3, ' Wangwu ');
sql> INSERT into test2 values (4, ' Zhaoliu ');
sql> INSERT into test2 values (5, ' Sunqi ');

1, Union

Sql> Select Id,name from Test1
2 Union
3 Select Id,name from Test2;


ID NAME
---------- -----------------------
1 Zhangsan
2 Lisi
3 Wangwu
4 Zhaoliu
5 Sunqi

UNION All

Sql> Select Id,name from Test1
2 UNION ALL
3 Select Id,name from Test2;


ID NAME
---------- ----------------------
1 Zhangsan
2 Lisi
3 Wangwu
4 Zhaoliu
3 Wangwu
4 Zhaoliu
5 Sunqi

Union summary: Unions, unions, and do not remove duplicate rows (Unoin All)

1, the difference between the orders:

From the above test the difference between Union and UNION ALL command is to remove duplicate data rows.

2, the implementation process:
The union command removes duplicate data, which conceptually merges the data before executing the DISTINCT command (automatically handled by the database system), which requires a sort action to achieve the goal of eliminating duplicate data, and the need to remove duplicate data before using the Union command, In particular, when processing large amounts of data, it will cause additional memory, or even IO system resource waste If there is no need to remove duplicate data, use the union ALL command.

UNION duplicate data display only once will delete duplicate data (equivalent to executing distinct)
UNION all data shows a better execution efficiency, regardless of whether duplicate data is repeated or not deleted

2. Intersection (INTERSECT)

Oracle provides INTERSECT commands similar to the Union and handles the results from two SQL statements. The difference is that the union command is conceptually an OR.
, the data will be selected (and set) as long as there is any data set. And intersect is conceptually and manipulated, only data that exists in a dataset
will be chosen (the intersection).

INTERSECT
Sql> Select Id,name from Test1
2 intersect
3 SELECT * from Test2;


ID NAME
---------- ---------------------
3 Wangwu
4 Zhaoliu

3, Difference set (minus)
The above version of SQL Server provides the except command, which, like Oracle's minus command, makes notes of the first dataset and the second data set.
Only values that are present in the first dataset, but not in the second dataset, are returned (subtracted).
Minus

Sql> Select Id,name from Test1
2 minus
3 Select Id,name from Test2;


ID NAME
---------- -----------------------------
1 Zhangsan
2 Lisi

Attention:

A set operation is an operation on two datasets, and for the above 4 types of collection commands, you need to be aware of the restrictions when you use them:

number of fields must be consistent

field types need to be compatible

field names are based on the first data set