. SQL Foundation--Subquery

One, sub-query

A subquery is a query that is inside a select, UPDATE, or DELETE statement

Ii. classification of sub-queries

A single-row subquery returns 0 rows or rows of multiline subqueries that return    one or more rows of multi-column subqueries that    return multiple-column-related subqueries    referencing a column in an external SQL statement or multiple-column nested    subqueries in other subqueries

　　

Third, sub-query syntax

Select Select_listfrom tablewhere expr operator   (select select_list from    table);    

Iv. rules for sub-queries

Five, single-line subquery

Returns only one row of table operators using a single line: =, >=,,<, <=,<> using subqueries in the WHERE clause sql> select Ename,job from emp where empno = (select empno from emp where mgr = 7902);      ename JOB-------------------SMITH clerk--sub-query using grouping functions sql> select Ename,job,sal from emp where Sal > (select AVG (SAL) from EMP); ename JOB SAL-----------------------------JONES manager 2975BLAKE Manager 28         50CLARK MANAGER 2450SCOTT analyst 3000KING President 5000FORD ANALYST 3000--using subqueries in the HAVING clause sql> select Deptno,min (SAL) 2 from EMP 3 GROUP by Deptno 4 have min (sal) > 5 (SEL   ect min (SAL) 6 from emp 7 where deptno = 20); DEPTNO MIN (SAL)--------------------30 950 10 1300--use subqueries in the FROM clause sql> Select Empno,e    Name 2 from 3 (select Empno,ename 4 from emp 5 where deptno = 20); EMPNO ename--------------------7369 SMITH 7566 JONES 7788 SCOTT 7876 ADAMS 7902 ford--A common error in a one-line subquery--the result of a subquery returns more than one row sql> Selec  T Empno,ename 2 from emp 3 WHERE Sal = 4 (select Sal 5 from EMP 6 where Deptno       = 20); (Select Sal * ERROR at line 4:ora-01427:single-row subquery returns more than one row--sub-query cannot contain order by child     Sentence sql> Select Empno,ename 2 from emp 3 where sal > 4 (select AVG (SAL) 5 from EMP        6 order by Empno); ORDER by Empno) * ERROR @ 6:ora-00907:missing Right Parenthesis--there is no return row inside the subquery, the following statement can be executed correctly, but no data is returned S Ql> Select Ename,job 2 from emp 3 WHERE empno = 4 (select Empno 5 from EMP 6 whe   Re mgr = 8000); No rows selected

　　

Six, multi-row sub-query

return multiple rows

Use the multiline comparison operator in, any, all

Using the in operator in multiline subqueries */

idle> Select Empno,ename,jobfrom empwhere sal in    (select Max (SAL) from the     emp     Group by DEPTNO);  2    3    4    5    6       EMPNO ename      JOB-----------------------------      7698 BLAKE      MANAGER      7788 SCOTT      Analyst      7839 KING       President      7902 FORD       ANALYST

　　

--Using the any operator in multiline subqueries

idle> Select Empno,ename,jobfrom empwhere sal < any    (select AVG (SAL) from     emp     Group by DEPTNO);  2    3    4    5    6       EMPNO ename      JOB-----------------------------      7369 SMITH      Clerk      7900 JAMES      Clerk      7876 ADAMS      clerk      7521 WARD       salesman      7654 MARTIN     salesman      7934 MILLER     Clerk      7844 TURNER     salesman      7499 ALLEN      salesman      7782 CLARK      MANAGER      7698 BLAKE      MANAGER10 rows selected.

　　

--Using the all operator in multiline subqueries

idle> Select Empno,ename,jobfrom empwhere sal > All    (select AVG (SAL) from     emp     Group by DEPTNO); 
   2    3    4    5    6       EMPNO ename      JOB-----------------------------      7566 JONES      MANAGER      7788 SCOTT      analyst      7902 FORD       analyst      7839 KING       president

　　

/*

Vii. related sub-queries

Some of the fields in the main query are used in the subquery, and the primary query executes the subquery once per line of the scan */

--Query The department number, name, and salary of employees whose wages are higher than the same department

Idle> Select Deptno,ename,salfrom emp outerwhere sal >    (select AVG (SAL) from     emp Inner     where Inner.deptno = Outer.deptno);  2    3    4    5    6      DEPTNO ename     SAL------------------------------ALLEN    160020 JONES    297530 BLAKE    285020 SCOTT    300010 KING     500020 FORD     30006 rows selected.

--Query the employee records responsible for managing other employees (using exists)

Idle> Select Empno,enamefrom emp outerwhere exists   (select empno from    emp inner    where inner.mgr = Outer.em pno);  2    3    4    5    6       EMPNO ename--------------------      7566 JONES      7698 BLAKE      7782 CLARK      7788 SCOTT      7839 KING      7902 FORD6 rows selected.

　　

--query staff who do not manage other employees (not exists)

Idle> Select Empno,enamefrom emp outerwhere NOT EXISTS (select Empnofrom emp innerwhere inner.mgr = outer.empno);  2    3    4    5    6       EMPNO ename--------------------      7369 SMITH      7499 ALLEN      7521 WARD      7654 MARTIN      7844 TURNER      7876 ADAMS      7900 JAMES      7934 MILLER8 rows selected.

　

Comparison of EXISTS and not EXISTS with in and not

The difference between exists and in:

exists only checks the existence of a row, in order to check the existence of the actual value (generally exists performance is higher than in)

Not EXISTS and not in

In the case where the value list contains null values, not EXISTS returns True, and not in returns false.

--Look at the following query, the department number is not in the EMP table appears in the Department name and location

Idle> Select Deptno,dname,locfrom Dept dwhere NOT EXISTS    (select 1 from     emp e     where e.deptno = D.deptno); 
   2    3    4    5    6      DEPTNO dname  LOC--------------------------------------OPERATIONS  BOSTON

　　

--in and Null values

Idle> SELECT *From EMP Ewhere e.empno No in (SELECT7369From dual UNION all SELECT NULL from dual); 2    3    4    5    6    7No rows Selectedidle>Idle> SELECT *From EMP e WHERE e.empno in ('7369', NULL); 2    3EMPNO ename JOB MGR hiredate SAL COMM DEPTNO---------- ---------- --------- ---------- ---------- ---------- ---------- ----------7369SMITH Clerk7902 1980- A- -          -             -

Note: Subqueries are included in parentheses

Subqueries are generally placed on the right side of the comparison criteria

Do not use ORDER by in a subquery unless you perform a top-n analysis. */

/*

Eight, Dolez query 1, paired comparison

Query the highest record of wages for the department

Idle> SELECT * from Scott.empwhere (sal,job)        in (select Max (SAL), the job from the Scott.emp group by job);  2    3       EMPNO ename      JOB       MGR hiredate     SAL       commdeptno----------------------------------------- --------------------------------------      7934 MILLER     clerk      7782 1982-01-23    1300      7499 ALLEN      salesman      7698 1981-02-20    1600300    7839      KING       President    1981-11-17      7566 JONES      MANAGER      7839 1981-04-02    2975      7902 FORD       ANALYST      7566 1981-12-03      7788 SCOTT      ANALYST      7566 1987-04-19    206 rows selected.

　　

2, non-paired comparison, to achieve a similar function with the above

Idle> select * from Scott.empwhere sal in (select Max (SAL) from Scott.emp Group by job) and job in (select DISTINCT job from scott.emp);  2    3       EMPNO ename      JOB       MGR hiredate     SAL       commdeptno----------------------------------------- --------------------------------------      7934 MILLER     clerk      7782 1982-01-23    1300      7499 ALLEN      salesman      7698 1981-02-20    1600300      7566 JONES      MANAGER      7839 1981-04-02    2975      7788 SCOTT      ANALYST      7566 1987-04-19      7902 FORD       ANALYST      7566 1981-12-03      7839 KING       President    1981-11-17    10

　　

Nine, nested subqueries

That is, a subquery inside a subquery, with a nesting layer up to the level. However, you should avoid using nested subqueries as much as possible, and query performance with table joins will be higher

Idle> Select Deptno,num_empfrom (select Deptno,count (empno) as num_emp from EMP Group by DEPTNO) Dwhere num_emp > 3;  2    3      DEPTNO    num_emp--------------------    620    5

Note: Sub-query handling of NULL values
Null values are ignored except for COUNT (*)　　

. SQL Foundation--Subquery