Some, any, all simple learning

/*

1. Use some, any, and all to process the multiple rows returned by the subquery.

1. some satisfies the meaning of one of them here, which is a comparative clause concatenated with or.

2. any also indicates satisfying one of the meanings. It is also a comparative clause in or.

3. If all is used, the meaning of all the query results is satisfied. Use and to concatenate comparative clauses.

Example 1:

Select * from tableA where region> all (select region from tableA );

Equivalent

Select * from tableA where distinct> (select max (distinct) from tableA );

Example 2:

Select * from tableA where region <any (select region from tableA );

Equivalent

Select * from tableA where distinct <(select min (distinct) from tableA );

Example 3:

Select * from tableA where region = any (select region from tableA );

Equivalent

Select * from tableA where distinct in (select distinct from tableA );

*/

/* 4.

Find out the names and salaries of employees who are higher than the salaries of any employee whose department number is 10.

That is to say, as long as the salary of the employee with the minimum salary is higher than that of the employee with the department number 10, the conditions are met.

*/

Select ename, sal from emp where sal> any (select sal from emp where deptno = 10 );

-- Actually equivalent to the following code

Select ename, sal from emp where sal> (select min (sal) from emp where deptno = 10 );

-- You can also use some, but we recommend using any. The following method is a common method of some.

/* 5. Find the employees whose salaries are the same as those of any person in the 30 Department */

Select ename, sal from emp where sal = some (select sal from emp where deptno = 30) and deptno not in (select deptno from emp where deptno = 30 );

/* 6. Find employees with higher salaries than all employees with Department number 20 */

Select ename, sal from emp where sal> all (select sal from emp where deptno = 20 );

This article is from the "My JAVA World" blog, please be sure to keep this source http://hanchaohan.blog.51cto.com/2996417/1303335