Oracle basic learning child query

First of all, when using subqueries, please note that subqueries can be nested multiple levels and subqueries need to be enclosed in parentheses (). Let’s take a look at the detailed introduction below.

Basic introduction

1, wherer: subquery will generally return a single row, single column, single row, multiple columns, multiple rows and single column;

2, having: subquery will return a single row, single column, while Indicates that statistical functions are to be used;

3, from: subquery returns multi-row and multi-column data (table structure);

4, select: returns a single row and single column (generally not used);

Example Detailed Explanation

where (filtering data rows):

a: Query employee information that is lower than the company's average salary.

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

The above query returns a single row and a single column that can be used as the filter condition of the where clause;

b: Query the information of the earliest employee hired by the company.

select * from emp where hiredate= (select MIN（hiredate） from emp);

C: Query information about employees who have the same job as scott and have the same salary.

select* from emp
 
where (job,sal) =( select job,sal
 
from emp
 
where ename =&#39;scott&#39;) and ename <>&#39;scott&#39;;

in: refers to the same content returned by the subquery.

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

not in:

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

There cannot be null in the subquery.

any:

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

It is larger than the maximum value returned by the subquery

select* from emp where sal < any(select sal from emp where job=&#39;manager&#39;);

It is smaller than the maximum value returned by the subquery

all:

all: larger than the maximum value returned by the subquery

where subquery The probability is very high;

having:

Query the job title and number of positions and average salary that are higher than the company's average salary.

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

select (generally not used):

Query the number, name, position, and department name of each employee.

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

(1+n) queries;

from(Key points):

Query the name, location, and number of people in each department.

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

(Multiple table query)

分步1： select d.deptno,d.dname,d.locfrom dept d;
 
 分步2：select deptno,count(empno)from emp group by deptno;
 
 
 
正确的查询：
 
select d.deptno,d.dname,d.loc，temp.count
from dept d,(select deptno,count(empno) count from emp
group by deptno) temp
where d.deptno=temp.deptno(+);

Multiple table queries and subqueries can both achieve statistics, so which method is better?

Answer: In actual work, the main purpose of subquery is to solve the performance problem of multi-table query, so it is used the most in development. The biggest role is to solve the problem of Cartesian product affecting performance caused by multi-table queries.

Complex query = simple query + limited query + multi-table query + grouped statistical query + subquery;

Summary

The above is all about Oracle subquery, I hope The content of this article can be helpful to everyone in learning or using Oracle. If you have any questions, you can leave a message to communicate.

For more articles related to Oracle basic learning, please pay attention to the PHP Chinese website!