Oracle Study Notes 14-set operations and advanced subqueries

Cancel provides three types of Set Operations: UNION, INTERSECT, and MINUS)

UNION: merges the results of multiple operations into one query result, returns the UNION of query results, and removes duplicates automatically.

Union all: Merge the results of multiple operations into one query result, but repeat the content.

INTERSECT: returns the same part of multiple operation results.

MINUS: returns the difference set of two query results, removing duplicates.

The basic syntax format is:

SELECT * FROM table_name 1

[Union, union all, intersect, minus]

SELECT * FROM table_name2

Note:There can be only one Order by statement, and it should be placed at the end. The statement expression must use the column name, alias, or bit of the first select statement.

Set the number. The column names and expressions in the select list also correspond to the quantity and data type.

Advanced subquery

Subquery: As mentioned earlier, another select statement is nested in an SQL statement. Internal queries must be performed before external queries. Internal queries return

The results are used by external queries.

Multi-column subquery

Subqueries in note 7 are all based on single-column queries. Now we are learning multi-column subqueries. The primary query must be compared with multiple columns returned by the subquery.

Is not a column.

Basic Syntax:

SELECT * FROM table_name

Where (column1, column2) = | in (

SELECT column1, column2 from table_name2 where...

)

The returned result is not a single column, but multiple columns. Multiple column names after the where statement in the outer query must be enclosed in parentheses.

Related subqueries

The primary query column is used in the subquery. Each row of data in the primary query performs a subquery.

For example, find the information of the employees whose salaries are greater than the average wage in the emp table.

Select * from emp e
Where sal> (
Select avg (sal) from emp
Where deptno = e. deptno
)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----------------------------------------------------------------
7566 jones manager 7839 2975.00 20
7698 blake manager 7839 2850.00 30
7788 scott analyst 7566 1987/4/19 3000.00 20
7839 king president 1981/11/17 5000.00 10
7900 james clerk 7698 2000.00 30
7902 ford analyst 7566 3000.00 20

6 rows selected

EXISTS Operator

The EXISTS operator checks whether any row that meets the condition EXISTS in the subquery. If the row EXISTS, it is not found in the subquery. true is returned. If the row does not exist, false is returned. The not exists operator checks whether any row that meets the condition EXISTS in the subquery.

With clause

Use the WITH clause, you can avoid repeatedly writing the same statement block in the SELECT statement. The WITH clause executes the statement blocks in the clause once and stores them in the user's temporary tablespace. Using the WITH clause can improve the query efficiency.

Nested subquery

Example: Query Information about employees with higher salaries than the Department

Select deptno, ename, sal, avgsal
From emp e, (select avg (sal) avgsal from emp group by deptno)
Where sal> avgsal

DEPTNO ENAME SAL AVGSAL
-----------------------------------
30 JAMES 2000.00 1741.66666
10 Clarks 2450.00 1741.66666
30 BLAKE 2850.00 1741.66666
20 JONES 2975.00 1741.66666
20 SCOTT 3000.00 1741.66666
20 FORD 3000.00 1741.66666
10 KING 5000.00 1741.66666
10 Clarks 2450.00 2175
30 BLAKE 2850.00 2175
20 JONES 2975.00 2175
20 SCOTT 3000.00 2175
20 FORD 3000.00 2175
10 KING 5000.00 2175
20 JONES 2975.00 2916.66666
20 SCOTT 3000.00 2916.66666
20 FORD 3000.00 2916.66666
10 KING 5000.00 2916.66666

17 rows selected

In the from statement, a subquery is used to query the average salary of a department, so as to compare the average salary of an employee with that of a department.