Oracle---SQL subquery---detailed

A subquery actually refers to a SELECT statement that is embedded in another statement, also known as a nested query. It is worth noting that subqueries can use the ORDER BY clause when a subquery is applied in a DDL statement.　　However, in a WHERE clause in a DML statement, a subquery cannot use the ORDER BY clause in a SET clause. 　　Simple example: Query work with all employees like Smith (not including Smith) select Ename, sal from emp e where job = (select Job from emp where ename = ' Smith ') and E.ename <> ' SMITH '; sub-query categorical single-line subqueries return only a subquery of a single record, as in the example above-returns the employee and salary of a salary more than SMITH select Ename, sal from EMP W　　Here sal> (select Sal from emp where ename = ' SMITH '); Can be used when the row subquery operators have >, <, =, like, and so on multi-row subquery multi-row subquery returns a group of records, that is, a number of records, the operation of the subquery should be in, any, all,exits and so on. A direct example-------Query the employee's highest employee's information in each department select ename, deptno,sal,job from EMP e where NOT EXISTS (select Ename,deptno,job from　　EMP where deptno = E.deptno and sal > E.sal) Order by Deptno; Examples of the use of several operators of a multiline subquery: the use of the--in operator--querying the employee's occupation type information for employees in department 10 select Ename,job,sal,deptno from emp where job in (select Dis　　Tinct job from emp where deptno = 10); The all operator: the use of the--all operator--queries the employee's salary is greater than the 30th department's All employees ' information select Ename,sal,deptno from emp where sal > All (selECT sal from EMP where deptno=30); --">all" means greater than the maximum, "any operator: the use of the--any operator--as long as the employee's salary is the case:--that his salary is more than one of the 30 departments of the wages of the query out select Ename,sal,deptno from　　EMP where Sal>any (select Sal from emp where deptno = 30);　　">any" means greater than the minimum value, "Multi-column subqueries refer to multiple columns returned by a subquery statement."　　For example, an employee named Smith, but because the employee's name may be duplicated, it needs to be judged in conjunction with its work and department ID. --Multi-column subquery instance select ename, Job, Deptno from EMP where (ENAME,JOB,DEPTNO) = (select Ename, Job,deptno from EMP where ENA Me= ' SMITH ');--This is mainly to illustrate the use of Dolez queries, and does not pay attention to the actual meaning of the comparison-paired comparison select ename, Sal,comm, Deptno from EMP where (SAL,NVL (comm　　, -1)) in (select SAL,NVL (comm,-1) from emp where deptno = 30); Non-paired comparisons-non-paired comparisons elect ename, Sal,deptno,comm from EMP where Sal in (select Sal from emp where deptno =) and n　　VL (COMM,-1) in (select NVL (comm,-1) from emp where deptno = 30); Other subqueries 1, correlated subqueries Refer to subquery statements that need to refer to the main query table column.　　Correlated subqueries are implemented through exists.　　--Query the information of employees working in New York select Ename, Job, Sal, Deptno from EMP where exists (select 1 from Deptwhere Dept.deptno = Emp.deptno and Dept.loc = ' NEW YORK '); --related subquery execution process: The related subquery will refer to one or more columns of the external query, at the time of execution, each row of the outer query is passed to the subquery one line at a time, and the subquery reads each value passed by the external query sequentially, and uses it on the subquery until all the rows of the external query are exhausted.　　The query results are then returned.　　2. When a subquery in the FROM clause uses a subquery in the FROM clause, the subquery is treated as a view and needs to specify an alias for the subquery. --View employee information that is higher than the average salary of the department select Ename,job,sal from EMP, (select Deptno,avg (SAL) as avgsal from EMP Group by DEPTNO) Tmp_　　Dept where Emp.deptno=tmp_dept.deptno and Sal >tmp_dept.avgsal; 3. In a DML statement using a subquery in the--DML statement, use the subquery in the--insert INSERT into employee (id,name,title,salary) Select Emptno,ename, job,sal from　　Emp --update in Update emp set (SAL,COMM) = (select Sal,comm from Empwhere ename =smith) where job = (select Job from emp W here ename =smith); --Removal of sales Department delete from emp where Deptno = (select Deptno from dept where Dname =sales);　　--DML statement using child where ename = ' Smith ') Where job = (select Job from emp where ename = ' smith ');    --Remove the Sales department delete from emp where Deptno = (select Deptno from dept where dname = ' sales '); 4, using a subquery in a DDL statement to create a view through a select subquery, it is important to note that the use of this method requires that the Scott user is given the right to create the view before executing the following SQL statement Sql>conn/as SYSDBA Sql>gra　　NT CREATE view to Scott; --Create a view and query the view create or replace view dept10 as select Empno,ename, Job,sal,deptno from emp where deptno = ten ORDER by emp　　No SELECT * from Dept10;

　　

Oracle---SQL subquery---detailed