Oracle Learning Note 14--collection operations and advanced subqueries

Oracel provides three types of collection operations: Each is and (UNION), intersection (INTERSECT), poor (minus)

Union: Merges the results of multiple operations into a single query result, returning the union of query results and proactively removing the repeated portions.

UNION all: Merges the results of multiple operations into a single query result, but retains repeated content.

INTERSECT: Returns the same part of multiple operation results.

Minus: Returns the difference set of two query results, removing the repeated parts.

The basic syntax format is:

SELECT * FROM table_name 1

[Union, UNION All, intersect, minus]

SELECT * from Table_name2

Note: You can only have an order by statement, and the statement is placed at the end.

Statement expression to use the column name of the first select, alias or bit

number. the column names and expressions in the select list also correspond to the number and data types.

Advanced Sub-query

Subquery: As mentioned earlier, the SQL statement is nested inside a SELECT statement. Internal queries are preceded by an outside query. The internal query returns the

Results are used for external queries.

Dolez Query

Sub-query in note 7. are based on a single-column query, and now we're learning multiple columns of subqueries. The main query is to be compared to multiple columns returned by the subquery

More. is not a column.

The 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 following the where statement in the outer query are enclosed in parentheses.

Related sub-query

A column in a subquery that uses the main query. The data for each row of the main query runs one subquery at a time.

An example of an EMP table where wages are greater than the average wage

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 1981/4/2 2975.00 20
7698 BLAKE MANAGER 7839 1981/5/1 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 1981/12/3 2000.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20

6 Rows selected

exists operator

The EXISTS operator checks if there is a row in the subquery that satisfies the condition, if any. Do not continue looking in the subquery. Returns true, such as not saving in the. returns FALSE. Continue looking in the subquery. The Not EXISTS operator checks if there are no satisfies in the subquery The line of the condition .

With clause

with the WITH clause, it is possible to avoid repeating the same block of statements in a SELECT statement with a clause that runs the statement block in the clause once and stores it in the user's temporal tablespace using the WITH clause to improve query efficiency

Nested subqueries

Example: Query employee information that is higher than the department's salary

Select Deptno, Ename,sal, avgsal
From EMP E, (select AVG (SAL) avgsal from EMP Group by DEPTNO)
where Sal > Avgsal

DEPTNO ename SAL Avgsal
------ ---------- --------- ----------
JAMES 2000.00 1741.66666
CLARK 2450.00 1741.66666
BLAKE 2850.00 1741.66666
JONES 2975.00 1741.66666
SCOTT 3000.00 1741.66666
FORD 3000.00 1741.66666
Ten KING 5000.00 1741.66666
CLARK 2450.00 2175
BLAKE 2850.00 2175
JONES 2975.00 2175
SCOTT 3000.00 2175
FORD 3000.00 2175
Ten KING 5000.00 2175
JONES 2975.00 2916.66666
SCOTT 3000.00 2916.66666
FORD 3000.00 2916.66666
Ten KING 5000.00 2916.66666

-Rows selected

In the FROM statement, the sub-query is used to query the average salary of the department, thus realizing the comparison between the employee's salary and the department's average salary.

Oracle Learning Note 14--collection operations and advanced subqueries