Oracle subquery, oracle subquery

Oracle subquery, oracle subquery

Subquery: contains another query within a query.

Common subquery

-- Query the information of all employees whose salaries are higher than 7654. select * from emp e where e. sal> (select sal from emp where empno = 7654); -- query the information of all employees who have the same job as 7654 who have a higher wage ratio. select * from emp ewhere e. sal> (select sal from emp where empno = 7654) and e. job = (select job from emp where empno = 7788); -- query the names, jobs, and salaries of the employees with the lowest wages 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 set.

-- Query the select * from emp where empno in (1234,234 5, 7369,790 0) Information of employees numbered 1234,2345, 72.16,7900 );

-- Select * from emp where empno not in (1234,234 5, 7369,790 0) to query employee information whose employee number is not 1234,2345, 72.16,7900 );

-- Query the employee information corresponding to the minimum wage of each department. select * from emp where sal in (select min (sal) from emp group by deptno );

Any keyword

Any: represents any.

<Any is smaller than any result returned by the subquery, that is, it is smaller than the maximum value of the returned result.

= Any is equal to any result in the subquery, which is equivalent to in

> Any is greater than any result returned by the subquery, that is, it is greater than the minimum value of the returned result.

-- Query the minimum wage of each department, select min (sal) min_sal from emp group by deptno;

Sal is greater than any (minimum wage for each Department), that is, the minimum value greater than the returned result

select * from emp where sal > any (select min(sal) from emp group by deptno);

Sal = any (minimum wage for each department), which is equal to each result in the subquery, same as in

select * from emp where sal = any (select min(sal) from emp group by deptno);

Sal <any (minimum wage for each Department) is greater than the maximum value returned.

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, that is, it is smaller than the minimum value of the returned result.

> All is greater than all the results returned by the subquery, that is, it is greater than the maximum value of the returned results.

= All is meaningless and cannot be implemented logically.

Query the wage sets of wages ranging from 2000 to 3500.

select distinct sal from emp where sal between 2000 and 3500;

> All (the total number of wages between 2000 and 3500), which is greater than the maximum value.

select * from emp where sal > all(select distinct sal from emp where sal between 2000 and 3500);

<All (the set of wages between 2000 and 3500), which is smaller than the minimum value.

select * from emp where sal < all(select distinct sal from emp where sal between 2000 and 3500);

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.