Oracle Sub-Query

Recently in the strengthening of Oracle query, on the Internet to see a good video, take the study notes and share with you

Syntax of the Oracle subquery (that is, the nesting of the SELECT statement)

Questions to note about subqueries:
1. Parentheses in the subquery syntax
2. Writing style of sub-query
3. Where subqueries can be used: where, select, Having,from
4. Cannot be used in the group by of the main query
Sub-query for 5.from
6. Main query and subquery may not be the same table
7. Sorting is not generally used in subqueries, because there is no meaning to the main query, but in top-n analysis order, to sort
8. Execution order: Execute the subquery first, then execute the main query, but the related query exception
9. Single-line subqueries can only use single-row subqueries, multiline subqueries using multi-row subqueries (query results multiple rows)
10. Subquery NULL problem

---------------------------------------------------------------------------------------------

(1). Where subqueries can be used: where, select, Having,from

1. Select (the query after the SELECT statement must be a one-line subquery, which results in 1 returns)
      
    select empno,ename,sal, (SELECT JOB from EMP where empno=7839) from EMP
   
   2. Having (average salary for a department with an average wage greater than 30th department's maximum salary
   Select Deptno,avg (sal)
        from EMP
         GROUP BY Deptno
        have avg (SAL) > (select Max (sal)
       & nbsp;          from EMP
                           where deptno=30)
   
   3.from--is followed by a table, or result set (a query statement), where the subquery can be seen as a new table
      select *
      from (select Empno, Ename,sal,sal*12 from EMP)
   
   
   

   4.where

Query payroll than Scott's employees

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 subquery returns the result that the main query can use.

SELECT * FROM emp
where Deptno = (select Deptno
From Dept
Where dname= ' sales ')--you can also use multiple table queries (the database only needs to be requested once, depending on the size of the Cartesian product to determine which method
--Better

(3) Sorting is not generally used in subqueries, because there is no meaning to the main query, but in top-n analysis order, to sort

Find top 3 employees with high wages:

--rownum:oracle automatically added pseudo-columns, to get the value of the pseudo-column, the query must be displayed in the SELECT statement
--rownum can only use <,<=
--line numbers are always generated in the default order and do not change with sorting
Select Rownum,empno,ename,sal
From (SELECT * from emp ORDER BY sal Desc)
where rownum<=3;

(4). Execution order: Executes the subquery first, then executes the main query, but the related query exception

Correlated subqueries: (there is a requirement for the outside table that there must be an alias), you can pass the values in the main query as parameters to the subquery
Example: Query the Employee table for salary greater than the average salary of the department,
Select Empno,ename,sal, (select AVG (SAL) from EMP where Deptno=e.deptno) salavg
From EMP E
Where Sal > (select AVG (SAL) from EMP where Deptno=e.deptno)

(5). Single-row subqueries can use only one-line subqueries, and multiline subqueries use multiple rows of subqueries (multiple rows of query results)

Single-line operator:=,>,>=,<,<=,<>
Multiline operators: In,any,all

Find employee information for 7566 employees, Sal greater than 7588

SELECT * FROM emp
where job =
(select Job from EMP where empno=7566) and
Sal> (select Sal from emp where empno=7588)


Query employee information with lowest wage
SELECT * FROM emp
Where Sal = (select min (sal) from EMP);

Find the minimum wage for the department with the minimum wage greater than number 20th and the minimum wage for the department


Select Dept,min (SAL) from EMP
GROUP BY Deptno
Have min (sal) > (select min (sal) from EMP
where deptno=20);


Multiline subquery:
SELECT *
From EMP
where Deptno in (SELECT * from dept where dname= ' sales ' or dname= ' accounting ')

You can also use multiple table queries

Query salary than 30th Department of any one employee high employee information

SELECT * FROM emp
where Sal > any (select Sal from emp where deptno=30)
--(select min (sal) from EMP where deptno=30)

Check salary is higher than the employee information of all employees in department 30th

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-row subquery null problem
SELECT * FROM emp
where job =
(Select Job
From EMP where ename= ' Tom ')


Null value issues for multiline subqueries
Queries are not employees of the boss
SELECT * FROM emp
where EMP not in (select Mgr from EMP)--If the subquery results are NULL
If there is a null value in the collection, do not use not in (can use in), because not is equal to <>all, (in equals any)
<>null is always false.


The right

SELECT * FROM emp
where EMP not in (select Mgr from EMP where Mgr are NOT NULL)



----------------------------Gorgeous split-line--------------------------------------------------------

Paging Query
SELECT * FROM
(select RowNum r, e.* from
(SELECT * from emp ORDER BY sal Desc) E1 where rownum<=5)
where r>1

This article is from the "8297090" blog, please be sure to keep this source http://8307090.blog.51cto.com/8297090/1674150

Oracle Sub-Query