Oracle learning subquery

1. subqueries nest another SQL statement in one SQL statement into subqueries. When a problem cannot be solved through one clause, subqueries can be considered. A subquery that appears in the FROM clause of the SELECT statement is called "embedded View "; A subquery in the where clause of a SELECT clause is called an "embedded subquery". A subquery can contain another subquery. In a top-level query, the Oracle database does not have a limit on the number of nested layers in the FROM clause. You can nest a 255-level subquery in a where clause.
Subqueries can include: 1. single-row subqueries (only one row is returned for subqueries). The following operators can be used for single-row subqueries:> greater than <less than = equal to> = greater than or equal to <= less than or equal to <> not equal to 2. multi-row subqueries (multiple rows returned by subqueries) use the following operators for multi-row subqueries: IN equals to ANY value returned by ANY of the list and ANY value returned by the clause compare ALL and ALL values returned by the clause compare the subquery syntax IN the WHERE clause SQL code SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table ); query the information of employees with higher salaries than scott in the emp table under the scott user (only one row of records is returned for the subquery in this operation) SQL code SQL> select * from emp 2 where sal> (3 select sal from emp where ename = 'Scott' ); Empno ename job mgr hiredate sal comm deptno ---------- -------------- ---------------- ---------- 7839 king president 17-11 month-81 5000 10 query information of all managers in the emp table under the scott user (this operation the subquery returns multiple rows of records) SQL code SQL> select * from emp 2 where empno in (3 select mgr from emp); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO -------------------------------------------------------------- ----------- 7566 jones manager 7839 month-81 2975 20 7698 blake manager 7839 month-81 2850 30 7782 clark manager 7839 month-81 2450 10 7788 scott analyst 7566 month- 87 3000 20 7839 king president 17-11 month-81 5000 10 7902 ford analyst 7566 03-12 month-81 3000 20 6 rows selected. Subquery syntax in The FROM clause SQL code SELECT select_list FROM (SELECT select_list FROM table) WHERE expr; use SQL code SQL> select * 2 FROM (select empno, ename, job, sal from emp); empno ename job sal ---------- ----------- ---------- 7369 smith clerk 880 7499 allen salesman 1600 7521 ward salesman 1250 7566 jones manager 2975 7654 martin salesman 1250 7698 blke MANAGER 2850 7782 clark manager 2450 7788 scott analyst 3000 7839 king president 5000 7844 turner salesman 1500 7876 adams clerk 1100 7900 james clerk 950 7902 ford analyst 3000 7934 miller clerk 1300 has selected 14 rows. The subquery can also be displayed in the SELECT list, but the subquery can only be a single-row subquery SQL code select (select job from emp where empno = 7369) from emp; subqueries can also be found in the HAVING clause SQL code SQL> select empno, ename, sal, deptno 2 from emp 3 group by deptno, empno, ename, sal 4 having deptno in (5 select deptno from emp 6 where deptno = 10 or deptno = 20) 7 order by deptno, sal; empno ename sal deptno ---------- 7934 MILLER 1300 10 7782 CLARK 2450 10 7839 KING 5000 10 7369 SMITH 880 20 7876 ADAMS 1100 20 7566 JONES 2975 20 7902 FORD 3000 20 7788 SCOTT 3000 20 has selected 8 rows. If the subquery returns a null value, the main query will not find any results SQL code> select * from emp 2 where empno = (3 select empno from emp 4 where deptno = 40 ); note: 1> subqueries must be enclosed in parentheses. 2> subqueries can be found in the WHERE clause, FROM clause, and SELECT list (only one single row subquery can be used here), HAVING Clause 3>. subqueries cannot appear in the group by statement of the primary query 4>. subqueries and primary queries can use tables differently, the result returned BY the subquery can be used BY the primary query. 5> generally, the order by statement is not used in the subquery, however, order by Statement 6> must be used in TOP-N analysis. When a row subquery can only use a single row operator, multi-row subquery can only use multi-row operator 7>, use reasonable indentation and line breaks to raise the readability of SQL statements 8>, author dong_dong of null values in subquery