Shulou(Shulou.com)06/01 Report--

What is a subquery?

When the constraint in the query needs to be provided by another query, we can nest the two query statements and take the query statement that provides the condition as a subquery.

A subquery, also known as an internal query, is executed before the main query, and the results of the subquery are used for the main query.

Subqueries are divided into single-row subqueries, multi-row subqueries and multi-column subqueries. Single-row operators are used when single-row subqueries are used as judgment conditions, and multi-row operators are used when multi-row subqueries and multi-column subqueries are used as judgment conditions.

Single-line operators: >, =, > =, (select min (sal) from emp where deptno = 20); multiline subquery

A multi-row subquery returns one or more rows of records, concatenating using multiple-row operators

Query is the use of the manager's employee name, salary select ename,salfrom empwhere empno in (select mgr from emp) ANY

The representation is compared with any row of results of the subquery, and one satisfies the condition.

Any: greater than any one of the subquery results, that is, greater than the minimum value.

Any (select sal from emp where deptno = 10); use of ALL

The representation is compared with the results of all rows of the subquery, and each row must meet the condition.

All: represents all rows larger than the subquery result set, that is, greater than the maximum value. = all: equals all rows in the subquery result set, that is, all values.

Inquire about the employee number, name, position and salary for which the department number is not 10 and the salary is lower than that of all employees in the 10 departments. Select empno,ename,job,salfrom empwhere deptno 10 and sal > all (select salfrom empwhere deptno = 10); multi-column subquery

Compare multiple columns of a subquery within an expression at the same time, that is, the records returned by the subquery contain multiple columns

The department and position of any employee who joined in 1981 are exactly the same as the name, department, position and date of entry, excluding those who joined in 1981. Select ename,deptno,job,hiredatefrom empwhere (deptno,job) in (select deptno,job from empwhere to_char (hiredate,'YYYY') = '1981') and to_char (hiredate,'YYYY') '1981'

-query the same employee's name, department, position and date as any employee who joined in 1981, excluding the 1981 employee select ename,deptno,job,hiredatefrom empwhere job in (select job from empwhere to_char (hiredate,'YYYY') = '1981') or deptno in (select deptno from empwhere to_char (hiredate,'YYYY') = '1981') and to_char (hiredate,'YYYY') '1981; null value in the subquery

How does the null value in the subquery result affect the main query?

-- query the name of an employee who is not the manager select enamefrom empwhere empno not in (select mgr from emp)

There is a null value in the result of the subquery, which causes no record to return in the main query, because all conditions and null value comparisons are null, so whenever there is a null value

The not in operator cannot be used if it is possible to become part of a subquery result collection.

What if the result of a subquery contains null values and you want to use the not in operator?

-- query the name of an employee who is not a manager select enamefrom empwhere empno not in (select nvl (mgr,0) from emp);-- use the nvl () function to remove the effect of null values

Using subqueries in the from clause

Using a subquery in the from clause is equivalent to using the query result of the subquery as a table. It is recommended to add a corresponding alias when using it.

-- query the name, salary, department number and average salary of employees whose salary is higher than the average salary of their own department select e.enameree.salree.deptnograd. Avgsalfrom emp e, (select avg (sal) as avgsal deptno from emp group by deptno) wwhere e.deptno = w.deptno and e.sal > w.avgsalting ROWNUM

Rownum is a pseudo column, which, as its name implies, is a special column that is similar to a table but is not actually stored in the table.

The function of rownum is to return the sequence number of the result set at each query, which is generated step by step when the output is recorded, the first row is displayed as 1, the second behavior 2, and so on.

Select rownum.emp.* from emp

A few things you need to know about rownum

Rownum is generated when the output is recorded, and rownum can only perform the = or interval operation between. When and... rownum is used with order by, because the rownum is generated when recording the output and the order by clause is executed at the end, it is important to note that when the two are used together, rownum is actually a sorted rownum. TOP-N query

Realize the function of sorting by a column in the table and outputting the largest or smallest N records

Top-N syntax

ASC: sort in ascending order, query the smallest N records

DESC: sort in descending order, query the largest N records

-- query the names and dates of the top 5 employees with the earliest entry date. Select rownum,ename,hiredatefrom (select ename,hiredatefrom emp order by hiredate) where rownum select outputs the final result

-- display 5 records per page and inquire about the information on page 1, page 2 and page 3 of the highest salary respectively, requiring the display of employee name, entry date, department name and salary. Select * from (select rownum rn,b.* from (select ename,hiredate,dname,sal from emp,dept where emp.deptno = dept.deptno order by sal desc) b where rownum (1-1) * 5

Select * from (select rownum rn,b.* from (select ename,hiredate,dname,sal from emp,dept where emp.deptno = dept.deptno order by sal desc) b where rownum (2-1) * 5

Select * from (select rownum rn,b.* from (select ename,hiredate,dname,sal from emp,dept where emp.deptno = dept.deptno order by sal desc) b where rownum (3-1) * 5

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.