Oracle Sub-Query

Subquery: Includes another query within one query.

Normal subquery

--Find out information about all employees who are higher than 7654 salary SELECT * from emp e where e.sal > (select Sal from emp where empno = 7654);--query out the wage ratio of 7654 high, while with 778 8 Information for all employees in the same job Select * from emp ewhere e.sal > (select Sal from emp where empno = 7654) and E.job = (select Job from EM P WHERE empno = 7788);--check out the lowest wage employee name, job, salary select E.ename, E.job, e.sal from emp ewhere e.sal = (select min (sal) from EMP) ;

In query

The In keyword is used to match records in a collection

--Query employee information for employee number 1234,2345,7369,7900 SELECT * from emp where empno in (1234, 2345, 7369, 7900);

--Query employee number is not 1234,2345,7369,7900 employee information select * from EMP where empno not in (1234, 2345, 7369, 7900);

--Query the employee information for each department's minimum wage select * from emp where Sal in (select min (sal) from EMP Group by DEPTNO);

Any keyword

Any: denotes arbitrary.

< any smaller than any result returned by the subquery, which is less than the maximum value of the returned result

= any and sub-query results are equal, equivalent in

> any larger than any result returned by the subquery, which is greater than the minimum value of the returned result

--Query the minimum wage for each department select min (SAL) min_sal from EMP Group by DEPTNO;

Sal is greater than any (minimum wage per department), which is greater than the minimum value of the returned result

SELECT * from emp where sal > any (select min (sal) from EMP Group by DEPTNO);

Sal = Any (minimum wage per department), i.e. equal to each result in the subquery, with in

SELECT * from emp where sal = any (select min (sal) from EMP Group by DEPTNO);

Sal < Any (minimum wage per department), which is greater than the maximum value of the returned result

SELECT * from emp where Sal < any (select min (sal) from EMP Group by DEPTNO);

All keyword

All: Indicates all.

< All is smaller than all results returned by the subquery, which is less than the minimum value of the returned result

> All is larger than the result returned by the subquery, which is greater than the maximum value of the returned result

= All meaningless and logically not tenable

Check the Payroll collection for wages in the 2000 to 3500 payroll Section

Select distinct sal from EMP where Sal between and 3500;

> All (Payroll collection for payroll at 2000 to 3500), which is greater than the maximum value

SELECT * from emp where sal > All (SELECT DISTINCT Sal from EMP where Sal between and 3500);

< All (Payroll collection for payroll at 2000 to 3500), which is less than the minimum value

SELECT * from emp where Sal < All (select DISTINCT Sal from EMP where Sal between and 3500);

Oracle Sub-Query