Use of oracle collections of IT Ninja Turtles

Use of oracle collections of IT Ninja Turtles

Each query group can obtain its result set. to combine multiple query results into one result set, you can use the set operation.

Such as UNION, UNION, unoin all, INTERSECT, and MINUS ).

The purpose of the SET command is to merge the results produced by two (or more) SQL statements. Because we need to merge two (or more) Datasets

Therefore, the number of fields must be equal and the type must be compatible. If the number of fields cannot be the same, replace the number of fields with NULL,

To make the number of fields meet the requirements.

Create two tables and insert data for testing:

[SQL]View plaincopy

1. Create table test1 (id int primary key,
2. Name varchar2 (200 ));
3. Create table test2 (id int primary key,
4. Name varchar2 (200 ));
5. Insert into test1 values (1, 'hangsan ');
6. Insert into test1 values (2, 'lisi'); insert into test1 values (3, 'wangw ');
7. Insert into test1 values (4, 'zhaoliu ');
8. Insert into test2 values (3, 'wangwu'); insert into test2 values (4, 'zhaoliu ');
9. Insert into test2 values (5, 'sunqi ');

   1. UNION)

   UNION

   [SQL]View plaincopy
   1. Select id, name from test1 union
   2. Select id, name from test2;

      The query result is:

      UNION ALL

      [SQL]View plaincopy
      1. Select id, name from test1 union all
      2. Select id, name from test2;

         The query result is:

         The above test shows that the difference between the UNION and union all commands is whether to remove duplicate data rows.

         The UNION command removes duplicate data. In terms of concept, this command First merges the data and then runs the DISTINCT command (automatically processed by the database system ).

         In addition to the purpose of repeated data, sorting is also required. before using the UNION command, you need to consider the necessity of removing repeated data, especially when processing a large amount of data

         External memory, or even IO system resource waste. If you do not need to remove duplicate data, use the union all command.

         The UNION statement only displays the duplicate data once (equivalent to executing DISTINCT)

         ALL data in union all is displayed, and the execution efficiency is better no matter whether duplicate data is deleted or not.

         2. Intersection (INTERSECT)

         The INTERSECT Command provided by Oracle is similar to the UNION command. It also processes the results of the two SQL statements. The difference is that the UNION command is an OR operation.

         As long as any data set exists, it will be selected (union ). INTERSECT is a conceptual AND operation, AND only data that exists in a dataset at the same time

         Will be selected (intersection ).

         INTERSECT

         [SQL]View plaincopy
         1. Select id, name from test1 intersect
         2. Select id, name from test2;

            The query result is:

            3. MINUS)

            SQL Server and later versions provide the mongot command, which is similar to the MINUS command of Oracle. Take notes on the first and second datasets,

            Only values that appear in the first dataset but not in the second dataset are returned (subtract ).

            MINUS

            [SQL]View plaincopy
            1. Select id, name from test1 minus
            2. Select id, name from test2;

               The query result is:

               Set operations are performed on two datasets. When using the preceding four types of set commands, you must note the following constraints:

               The number of fields must be consistent.

               Field type must be compatible

               The field name is based on the first dataset.