Oracle Database basic learning (6) subquery, oracle Database

Oracle Database basic learning (6) subquery, oracle Database

A subquery contains multiple nested query statements in a select statement.

 

1Use subquery in THE where clause (generally, return "Single Row, single column" Single Row, multiple columns "multiple rows, single column" (in, any, all can be provided ))

Example 1: Find the employee information below the average salary (returns a single row)

select * from emp where sal < (select avg(sal) from emp)

Example 2: Find the earliest employee information of the company (return to single row)

select * from emp where hiredate =(select min(hiredate) from emp) 

Example 3: work in the same job as scott and have the same salary (multiple rows are returned in a single column) -- but this is generally not the case

select * from emp where ((job, sal)= (select job, sal from emp where ename='SCOTT')) and (ename != 'SCOTT')

 

When the subquery returns multiple rows with single columns, you can use in, any, and all to compare them.

(1) usage of in and not in (cannot be used with null)

Example: Query Information about employees whose salaries are different from those of managers.

select * from emp where sal not in (select sal from emp where job='MANAGER') ;

 

(2) usage of any ("> any" is greater than the smallest "<any" is smaller than the maximum)

Example 1: query the employee information with the same salary as the Job MANAGER ("= any" and "in)

select * from emp where sal =any (select sal from emp where job='MANAGER') ;

 

Example 2: Query Information about employees whose jobs are higher than the MANAGER's minimum wage ("> any" is larger than the minimum wage)

select * from emp where sal >any (select sal from emp where job='MANAGER') ;

 

Example 3: Query Information about employees whose jobs are lower than the MANAGER's highest salary ("<any" is smaller than the largest)

select * from emp where sal <any (select sal from emp where job='MANAGER') ;

 

 

(3) usage of all ("> all" is smaller than the largest "<all" is smaller than the smallest)

Example 1: Query Information about employees who have higher salaries than managers ("> all" is bigger than the largest one)

select * from emp where sal >all (select sal from emp where job='MANAGER') ;

 

 

Example 2: Query employee information that is lower than the MANAGER's salary ("<all" means smaller than the minimum)

select * from emp where sal <all (select sal from emp where job='MANAGER') ;

 

 

2. subquery is used in the having clause. Only a single row and single column are returned in the subquery, and statistical functions are used.

Example 1: query the job name, number of employees, and average salary of employees higher than the company's average salary.

select job, count(empno), avg(sal)from empgroup by job having avg(sal) > (select avg(sal) from emp) ;

 

 

3. Use a subquery in the select clause (a single row and a single column are returned, but not generally used)

Example 1: query the ID, name, position, and department name of each employee

Multi-Table query:

select e.empno, e.ename, e.job, d.dnamefrom emp e, dept dwhere e.deptno=d.deptno(+) ;

 

Use select subquery:

select e.empno, e.ename, e.job, (select dname from dept d where e.deptno=d.deptno) dnamefrom emp e ;   

 

 

4. Use subquery in the from clause (multiple rows and multiple columns are returned)

Example 1: query the name, location, and number of people in each department

Multi-Table query:

 

select d.dname, d.loc, count(empno)from emp e, dept dwhere e.deptno=d.deptno(+)group by d.dname, d.loc ;

 

 

 

 

Use the from subquery:

|-Query the Department name, location, and number

select deptno, dname, loc from dept ; 

 

|-Query the Department ID and department owner

select  deptno, count(empno)from empgroup by deptno ;

 

|-Subquery in from is equivalent to multi-table join query in two tables

select d.dname, d.loc, temp.countfrom dept d, (select deptno, count(empno) count from emp group by deptno ) tempwhere d.deptno=temp.deptno(+) ;

 

 

 

Note: 1. When a subquery returns a single row, multiple rows, and multiple columns, subquery is implemented in the where clause.

2. subquery is used in the having clause. Only a single row and single column are returned in the subquery, and statistical functions are used.

3. When the subquery returns multiple rows and multiple columns, implement the subquery in the from clause.

4. subqueries are not implemented in the select clause.