Oracle Collection Operations

Collection Key Words:

1 , UNION: Set, all contents are queried, repeated display once , Sort Ascending By default ;

2 , UnionAll: The collection, all the content is displayed, including the duplicate , there is no sort of display content ;

3 , INTERSECT: intersection, show only same in multiple queries of the element Part ;

4 , minus: difference set, displays elements in the first query that are not in the second query

Example:

Under the Scott user, create the table EMP2, which contains only the information for the 20-door employees in the EMP:

Code: CREATE TABLE EMP2 as SELECT * Fromemp where deptno=20;

First look at the difference between the EMP and EMP2 two tables:

[EMP table structure and contents]

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/53/83/wKioL1RptCGTxVVwAAGbOsZ6q4U864.jpg "title=" 1.jpg " alt= "Wkiol1rptcgtxvvwaagbosz6q4u864.jpg"/>

[emp2 table structure and contents]

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/53/85/wKiom1Rps7zysIUxAACmMB1gtx0867.jpg "title=" 2.jpg " alt= "Wkiom1rps7zysiuxaacmmb1gtx0867.jpg"/>

~ Verify Union and Union All

UNION : SELECT * FROM emp UNION SELECT * FROM emp2;/* Use this statement, duplicate content no longer appears * /

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/53/83/wKioL1RptD_i7PSEAAGPXwMstbw510.jpg "title=" 3.jpg " alt= "Wkiol1rptd_i7pseaagpxwmstbw510.jpg"/>

UNION All : SELECT * from emp UNION all SELECT * FROM emp2;/* Use this statement, duplicate content still appears */

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/53/85/wKiom1Rps9viioxnAAHsPZ9ngd4260.jpg "title=" 4.jpg " alt= "Wkiom1rps9viioxnaahspz9ngd4260.jpg"/>

~ Verify intersect

INTERSECT : SELECT * FROM emp INTERSECT SELECT * FROM emp2;/* Use this statement to display only records that are duplicated in two tables */

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/53/83/wKioL1Rpt4SicIsZAAD5RkFPH6U749.jpg "title=" 5.jpg " alt= "Wkiol1rpt4siciszaad5rkfph6u749.jpg"/>

~ Verification minus

minus : SELECT * from emp minus SELECT * FROM emp2;/* Use this statement to return a record showing the difference */

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/53/85/wKiom1RptxzQjt_3AAF_Oy5YO7c318.jpg "title=" 6.jpg " alt= "Wkiom1rptxzqjt_3aaf_oy5yo7c318.jpg"/>

Summarize:

1. The number of elements in the two queries for the collection operation needs to be the same, with the same or approximate data type (recommended is the same);

2. Union the difference between union and all is whether duplicate elements are displayed and sorted;

3. if the element column name is not the same in two queries, the result displays the column name as the first query, whichever is the collection operation;

4. for the collection operation of the query, at the end can choose whether to add the order by keyword, custom sorting;

5. If there are null elements in all two queries, two queries will only display a NULL element row when the union merge operation occurs. Although Null<>null, Oracle performs implicit function processing internally when performing collection operations: sys_op_map_nonnull (NULL)

Function Explanation Official extension:

In Oracle, NULL does not equal null:
This function makes it possible to has NULL =null:

Translation:

In the Oracle database, NULL is not equal to NULL

This function makes Null=null possible.

This article is from the "Oralce Learning path" blog, make sure to keep this source http://dushuai.blog.51cto.com/9461011/1577579

Oracle Collection Operations