Shulou(Shulou.com)06/01 Report--

Collection keyword:

1. UNION: Union, all contents are queried, displayed repeatedly, and sorted in ascending order by default

2. UNIONALL: merge, all the contents are displayed, including duplicates, the display contents are not sorted.

3. INTERSECT: intersection, showing only the same elements in multiple queries

4. MINUS: subtraction, showing elements that are present in the first query and not in the second query

Example:

Under scott user, create the table emp2, which contains only the information for employees of 20 departments in emp:

Code: create table emp2 as select * fromemp where deptno=20

Let's take a look at the difference between emp and emp2:

[structure and content of emp]

[structure and content of emp2]

~ verify UNION and UNION ALL

UNION:select * from emp UNION select * from emp2;/* uses this statement, and duplicates are no longer displayed * /

UNION ALL:select * from emp UNION ALL select * from emp2;/* uses this statement, and the duplicate content still shows * /

~ verify INTERSECT

INTERSECT:select * from emp INTERSECT select * from emp2;/* uses this statement to display only records in two tables that repeat each other * /

~ verify MINUS

MINUS:select * from emp MINUS select * from emp2;/* uses this statement to return records showing differences * /

Summary:

1. The number of elements in the two queries for the collection operation should be the same, and the data types should be the same or similar (the recommendation is the same)

2. The difference between Union and Union all lies in whether duplicate elements are displayed and sorted.

3. If the element column names in the two queries are not the same, when performing the collection operation, the result shows that the column names are subject to the first query.

4. For queries that perform collection operations, you can choose whether or not to add the ORDER BY keyword at the end for custom sorting.

5. If there is a null element of NULL in both queries, only one row of NULL elements will be displayed in the Union merge operation. Although NULLNULL, Oracle performs implicit function processing internally when performing collection operations: sys_op_map_nonnull (null)

Function to explain the official extension:

In Oracle, NULL does not equal NULL:

This function makes it possible to have NULL = NULL:

Translation:

In Oracle database, NULL is not equal to NULL

This function makes NULL=NULL possible.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.