SQL subqueries for Oracle databases

The SQL subquery of the Oracle database is described in the preface, considering this situation: queries which employees have higher salaries than SMITH. To solve these problems, we need to first query the SIMTH salary and then compare the salaries of all employees. Then, subqueries are required to use the results of the first query. Subquery: A subquery is a select statement embedded in other statements. It is also called a nested query. Note that the order by clause can be used for subqueries in DDL statements. However, in the where clause and set clause in DML statements, subqueries cannot use the order by clause. Simple Example: query the information of all employees working the same way as SMITH (excluding SMITH) [SQL] select ename, sal from emp e where job = (select job from emp where ename = 'Smith ') and e. ename <> 'Smith '; subquery: a single row subquery returns only one subquery of a record, just like the above example [SQL] -- returns the select ename for employees with higher salaries than SMITH, sal from emp where sal> (select sal from emp where ename = 'Smith '); the operators used for row subqueries include>, <, =, like, and so on. Multiple rows subqueries return a record group, that is, multiple records, in this case, the subquery operations should be in, any, all, exits, and so on. A direct example is used to query the employee information with the highest salary in each department. A correct query method seems to be provided: [SQL] select max (sal) from emp group by (deptno) -- select ename, sal, job from emp where sal in (select max (sal) from emp group by deptno ); however, if you think carefully, you can understand that the above ideas are problematic, although the highest salary of each department is queried, the highest salary of each department cannot be specified in the external query. The query results must be incorrect. The following provides a correct method: [SQL] 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; in fact, this subquery method is used to query related subqueries. What are related subqueries. Examples of operators used for multi-row subqueries: in operator: [SQL] -- Use of in operator -- Query of employee information of employee career type IN department 10 select ename, job, sal, deptno from emp where job in (select distinct job from emp where deptno = 10); all operator: [SQL] -- Use of the ALL operator -- query the information of ALL employees whose salaries are greater than Department 30. select ename, sal, deptno from emp where sal> all (select sal from emp where deptno = 30); -- "> all" indicates greater than the maximum, and "<ALL" indicates less than the smallest ANY operator: [SQL] -- Use of the ANY operator -- as long as the employee's salary exists: -- That is, his salary is better than one of the 30 departments. When an individual has a high salary, select ename, sal, deptno from emp where sal> any (select sal from emp where deptno = 30) is queried. "> ANY" means that the value is greater than the minimum value, "<any" means less than the maximum value, "= ANY" means that in Multi-column subquery refers to multiple columns returned by the subquery statement. For example, for an employee named SMITH, the employee's name may be duplicated, so the employee's work and department id must be used for determination. [SQL] -- select ename, job, deptno from emp where (ename, job, deptno) = (select ename, job, deptno from emp where ename = 'Smith '); -- this mainly describes the use of multi-column subqueries, without paying attention to its practical significance ● paired comparison [SQL] -- paired comparison of 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 comparison [SQL] -- Non-paired comparison of elect ename, sal, deptno, comm from emp where sal in (select sal from em P where deptno = 30) and nvl (comm,-1) in (select nvl (comm,-1) from emp where deptno = 30 ); other subqueries 1. Related subqueries are subquery statements that reference the primary query table column. Related subqueries are implemented through exists. [SQL] -- Query Information about employees working in NEW YORK: select ename, job, sal, deptno from emp where exists (select 1 from dept where dept. deptno = emp. deptno and dept. loc = 'New YORK '); thinking: Execution Process of related subqueries: Related subqueries reference one or more columns of external queries, during execution, each row of the external query is passed to the subquery by a row. The subquery reads each value passed by the external query in sequence and uses it to the subquery, the query result is returned until all rows in the external query are used up. 2. When a subquery in the from clause uses a subquery in the from clause, the subquery is treated as a view and an alias must be specified for the subquery. [SQL] -- view the 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. Use subquery in DML statements [SQL] -- use subquery in DML statements -- insert into employee (id, name, title, salary) select emptno, ename, job, sal from emp; -- update emp set (sal, comm) = (select sal, comm from emp 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 shows how to use it. Note that before executing the following SQL statement, grant the scott user the right to create a view [SQL] SQL> conn/as sysdba SQL> grant create view to scott; [SQL] -- create view and query view create or replace view dept10 as select empno, ename, job, sal, deptno from emp where deptno = 10 order by empno; select * from dept10; here is the information about subqueries.