The use of Oracle collection for it Ninja Turtles

Each group of queries can get its result set, and if you want to combine multiple query results into a single result set, you can use the set operation to implement.

such as the Union, set, and do not remove duplicate rows (unoin all), intersection (INTERSECT), difference set (minus).

The purpose of the collection command is to merge the results of two (including the above) SQL statements. As a result of the need to set two (including the above) data collection and

Therefore, the number of fields must be equal and the type must be compatible. If the number of fields cannot be the same, you can replace the insufficient part with a null value,

So that the number of its fields meets the requirements.

Create a new two table and insert the data for testing:

[SQL]View Plaincopy

2. Create Table test1 (
4. ID int primary key,
6. name varchar2 ($)
8. );
10. Create Table test2 (
12. ID int primary key,
14. name varchar2 ($)
16. );
20. Insert   into test1 values(1,' Zhangsan ');
22. Insert   into test1 values(2,' Lisi ');
24. Insert   into test1 values(3,' Wangwu ');
26. Insert   into test1 values(4,' Zhaoliu ');
30. Insert   into test2 values(3,' Wangwu ');
32. Insert   into test2 values(4,' Zhaoliu ');
34. Insert   into test2 values(5,' Sunqi ');

1. Union

UNION

[SQL]View Plaincopy

1. Select ID, name from test1
2. Union   
3. Select ID, name   from test2;

The query results are:

UNION All

[SQL]View Plaincopy

1. Select ID, name from test1
2. Union all
3. Select ID, name   from test2;

The query results are:

This test shows that the difference between the Union and union ALL commands is whether to remove duplicate rows of data.

The Union command removes the duplicated data, which conceptually merges the data first, then executes the distinct command (automatically handled by the database system), in order to reach the

In addition to the purpose of repeating data You need to think about the need to remove duplicate data before using the Union command, especially when processing large amounts of data,

outside of the memory, even waste of IO system resources If you do not need to remove duplicate data, use the union ALL command.

UNION duplicate data is only shown once to delete duplicate data (equivalent to executing distinct)

UNION all data displays, with better execution efficiency, regardless of whether duplicate data is not deduplicated

2. Intersection (INTERSECT)

Oracle provides INTERSECT commands similar to union, and also handles the results of two SQL statements. The difference is that the union command is conceptually an OR operator.

Done, data is selected (and set) whenever there is any data set. And intersect is conceptually and, only data that exists simultaneously in a data set

will be selected (intersection).

INTERSECT

[SQL]View Plaincopy

1. Select ID, name from test1
2. intersect   
3. Select ID, name   from test2;

The query results are:

3. Difference set (minus)

SQL Server versions above provide the except command, similar to the Oracle's minus command, taking the first dataset and the second data set as a note,

Only values that are present in the first data set but not in the second dataset are returned (subtracted).

Minus

[SQL]View Plaincopy

1. Select ID, name from test1
2. Minus
3. Select ID, name   from test2;

The query results are:

The set operation is to operate on two datasets, and for the above 4 types of collection commands, you need to be aware of the constraints:

The number of fields must be consistent

The field type needs to be compatible

Field names are based on the first dataset

The use of Oracle collection for it Ninja Turtles