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

Recently, I have been strengthening my oracle query. I saw a good video on the Internet. I would like to share my study notes with you.

Syntax for oracle subqueries (that is, nesting of select statements)

Problems to pay attention to in the subquery:

1. Parentheses in subquery syntax

two。 Writing style of subquery

3. Locations where subqueries can be used: where, select, having,from

4. Cannot be used in the group by of the main query

Subquery of 5.from

6. The main query and subquery may not be the same table

7. Sorting is generally not used in subqueries because it makes no sense to the main query, but in the top-N parsing order, sort

8. Execution order: execute the subquery first, then the main query, with the exception of related queries

9. Single-row subqueries can only use single-row subqueries, and multi-row subqueries use multi-row subqueries (query results are multiple rows).

10. Subquery null problem

-

(1)。 Locations where subqueries can be used: where, select, having,from

Select (the query after the select statement must be a single-row subquery, that is, the result returns 1)

SELECT EMPNO,ENAME,SAL, (SELECT JOB FROM EMP where empno=7839) from emp

2.having (query the average salary of a department whose average salary is greater than the highest salary of Department 30.

Select deptno,avg (sal)

From emp

Group by deptno

Having avg (sal) > (select max (sal))

From emp

Where deptno=30)

3. From-is followed by a table, or a result set (a query statement), and the subquery in fromMurray can be treated as a new table.

Select *

From (select empno,ename,sal,sal*12 from emp)

4.where

Inquire about employees whose wages are higher than scott

Select * from emp where sal > (select sal

From emp

Where ename='scott')

(2)。 The main query and the subquery may not be the same table, as long as the results returned by the subquery can be used by the main query

Select * from emp

Where deptno = (select deptno

From dept

Where dname='sales')-- you can also use a multi-table query (the database only needs to request once, and which method can be determined according to the size of the Cartesian product

-- better

(3) sorting is not generally used in subqueries because it does not make sense to the main query, but in top-N parsing order, sort is required.

Find the top 3 highest-paid employees:

Pseudo columns are automatically added by rownum:oracle. To get the values of pseudo columns, you must show them in the query shown in the select statement.

-- rownum can only use =, (select min (sal) from emp)

Where deptno=20)

Multi-line subquery:

Select *

From emp

Where deptno in (select * from dept where dname='sales' or dname='accounting')

You can also use multi-table queries

Query the employee information whose salary is higher than any one of the employees in department 30

Select * from emp

Where sal > any (select sal from emp where deptno=30)

-(select min (sal) from emp where deptno=30)

Inquire about employee information whose salary is higher than all one employee in department 30

Select * from emp

Where sal > all (select sal from emp where deptno=30)

-(select max (sal) from emp where deptno=30)

(6) subquery null problem

Single-line subquery null problem

Select * from emp

Where job =

(select job

From emp where ename='tom')

Null value problem of multi-row subquery

Inquiry is not an employee of the boss.

Select * from emp

Where emp not in (select mgr from emp)-if the result of the query is null

As long as there are null values in the collection, you cannot use not in (you can use in), because not in is equivalent to all, (in is equivalent to any)

Null is always fake.

Right

Select * from emp

Where emp not in (select mgr from emp where mgr is not null)

-- gorgeous dividing line

Paging query

Select * from

(select rownum r, e.* from

(select * from emp order by sal desc) E1 where rownum1

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.