Oracle Sub-Query

Subquery: Contains another query inside a query.

Normal subquery

--Query for information about all employees that are higher than the 7654 wage select * from emp e where e.sal > (select Sal from emp where empno = 7654);--query out wages higher than 7654, same time with 7 788 information for all employees who are engaged in the same job Select * from emp ewhere e.sal > (select Sal from emp where empno = 7654) and E.job = (select Job from EMP 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 EM p);

In query

In keyword 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);

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbwfnateymde=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/center ">

--Query employee number is not 1234,2345. 7369,7900 Employee Information SELECT * from EMP where empno not in (1234, 2345, 7369, 7900);

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

Anykeyword

Any: denotes casual.

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

= Any and subquery are arbitrarily one result equal. Equivalent in

> Any is larger than the random result returned by a 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);

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbwfnateymde=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/center ">

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

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbwfnateymde=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/center ">

Sal < Any (minimum wage per department). The maximum value that is greater than the return result

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

Allkeyword

All: Indicates all.

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

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

= All has no meaning. It's not logically true.

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

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbwfnateymde=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/center ">

< 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