Oracle any, some, all usage

I seldom use any, some, and all in my daily work. Today I suddenly see this usage, which is very good and should be used in my work;

 

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

Next we will briefly introduce the meanings of these keywords.

* Some indicates that it satisfies one of the meanings. It is a comparative clause in the or string.

* Any also indicates satisfying the meaning of one of them. It is also a comparative clause concatenated with or. The difference is that any is generally used in a non-"=" comparative relationship, which is also easy to understand, the negative sentences in English use some in any statement, which is the same.

* "All" indicates that all of the query results meet the meaning of the query results, and the comparative clause in and string is used.

 

 

Any

Nested queries with [any] are the same as those with [some. Early SQL only allowed the use of [any]. Later versions introduced [some] to distinguish it from [any] in English, and reserved the [any] keyword.

Any:

Select EMP. empno, EMP. ename, EMP. Job, EMP. Sal from Scott. EMP where SAL> Any (select Sal from Scott. EMP where job = 'manager ');

The Query Process with any is equivalent to the execution process in two steps.

(1) Run "select Sal from Scott. EMP where job = 'manager '"

Select EMP. empno, EMP. ename, EMP. Job, EMP. Sal from Scott. EMP where SAL> 2975 or SAL> 2850;

 

Some

Some:

Select EMP. empno, EMP. ename, EMP. Job, EMP. Sal from Scott. EMP where sal = some (select Sal from Scott. EMP where job = 'manager ');

 

The nested query with some is the same as that of any.

(1) Run "select Sal from Scott. EMP where job = 'manager'" in the subquery. The result is 4.22.

(2) The parent query executes the following statements.

―――――――――――――――――――――――――――――――――――――

Select EMP. empno, EMP. ename, EMP. Job, EMP. Sal from Scott. EMP where sal = 2975 or sal = 2850 or sal = 2450;

 

All

 

All indicates query or subquery.

For example:

 

Select name from Edit

All is omitted before name.

You can add all | distinct before name.

All is all records.

Distinct is unique.

 

Select EMP. empno, EMP. ename, EMP. Job, EMP. Sal from Scott. EMP where SAL> All (select Sal from Scott. EMP where job = 'manager ');