Oracle Basic Learning Subquery _oracle

The first thing to consider when using subqueries is that subqueries can be nested in multiple tiers and subqueries () enclosed by parentheses (), let's take a look at the detailed descriptions below.

Basic introduction

1,wherer: A subquery typically returns a single row of more than a single row of multiple columns;

2,having: The subquery Returns a single column, indicating that the statistic function is to be used;

3,from: Subqueries return multiple rows and columns of data (table structure);

4,select: Returns a single row (generally not used);

Example detailed

Where (the filter action for the data row):

A: Check the employee information below the company's average wage.

SELECT * from emp where sal< (select AVG (SAL) from EMP);

The above query returns a single column that can be used as a filter condition for a WHERE clause;

B: Inquire about the company's earliest employees.

SELECT * from emp where hiredate= (select MIN. (hiredate) from EMP);

C: Query employees with the same job and salary as Scott.

 select* from the EMP

 where (job,sal) = (select Job,sal from

 emp

 where ename = ' Scott ') and ename <> ' Scott ';

In: Refers to the same content returned by a 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 ');

cannot be empty in a subquery.

Any

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

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

Larger than the maximum value returned by a subquery

 select* from EMP where Sal < any (select Sal from emp where job= ' manager ');

Smaller than the maximum value returned by a subquery

All

<all: Smaller than the minimum value returned by the subquery

All: Larger than the maximum value returned by a subquery

The probability of a where subquery is high;

Having

The average wage is queried for job titles above the company's average wage.

 Select Job,count (empno), avg (SAL) from EMP Group BY job has 
 avg (SAL) > (select AVG (SAL) from EMP);

Select (generally not used):

Query each employee's numbered name position department name.

 Select E.empno,e.ename,e.job,

 (select D.dname from Dept d Whered.deptno=e.deptno) from EMP e;

(1+n) the second inquiry;

From (emphasis):

Query the name position of each department number.

 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)

  Step 1:select d.deptno,d.dname,d.locfrom Dept D;

  Step-by-Step 2:select deptno,count (empno) from the EMP group by DEPTNO;



 Correct query:

 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 (+);

Multi-table queries and subqueries can be implemented in statistics, so what better way?

A: In the actual work, the main goal of subqueries is to solve the performance problems of multiple table query, so the most used in development. The maximum effect is to solve the problem of the performance of Cartesian product caused by multiple table queries.

Complex query = Simple Query + Limited query + multiple table Query + packet statistics query + subquery;

Summarize

This is all about Oracle subquery, I hope the content of this article for everyone to learn or use Oracle can help, if you have questions you can message exchange.