Oracle subquery
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
