Go Oracle Some,any,all,exists,in

Original address: http://blog.csdn.net/shangboerds/article/details/43983791

--Start

One thing that these keywords have in common is that they are generally used in sub-queries. Everyone is familiar with in and EXISTS, here I do not introduce, below we look at how to use the other several keywords, first, we define the following table:

[SQL]View Plaincopyprint?

2. --Student
4. CREATE TABLE STUDENT
6. (
8. ID VARCHAR2 (8), ---school number
10. Name VARCHAR2, ---name
12. Class VARCHAR2, ---class
14. Chinese number (5), ---language scores
16. Math Number (5) ---math score
18. );
22. INSERT into STUDENT (ID, NAME, class, Chinese, MATH) VALUES (' 20090001 ', ' Zhang San ', ' Grade A ', 80, 90);
24. INSERT into STUDENT (ID, NAME, class, Chinese, MATH) VALUES (' 20090002 ', ' John Doe ', ' Grade A ', 60, 75  );
26. INSERT into STUDENT (ID, NAME, class, Chinese, MATH) VALUES (' 20090003 ', ' Harry ', ' Grade A ', 90, 95);
28. INSERT into STUDENT (ID, NAME, class, Chinese, MATH) VALUES (' 20090004 ', ' Yangwen ', ' grade B ', 70, 90  );
30. INSERT into STUDENT (ID, NAME, class, Chinese, MATH) VALUES (' 20090004 ', ' Li Bai ', ' Grade B ', 85, 80);
32. INSERT into STUDENT (ID, NAME, class, Chinese, MATH) VALUES (' 20090005 ', ' King Blue ', ' Grade B ', N  ULL, 70);

Suppose now let you inquire, Class A which student mathematics result is higher than Class B mathematics result minimum value, how to do? We can use the following SQL:

[SQL]View Plaincopyprint?

1. SELECT NAME from STUDENT WHERE class=' Grade A class ' and MATH >
2. (
3. SELECT MIN (MATH) from STUDENT WHERE class=' Grade B class '
4. );

In addition, we can also use some or any. Note: Any and SOME work exactly the same way as they are used.

[SQL]View Plaincopyprint?

1. SELECT NAME from STUDENT WHERE class=' V Class A ' and MATH > any
2. (
3. SELECT MATH from STUDENT WHERE class=' Grade B class '
4. );

Suppose now let you inquire, Class A which student mathematics result is higher than Class B mathematics result maximum, how to do? We can use the following sql:

[SQL]View Plaincopyprint?

1. SELECT NAME from STUDENT WHERE class=' Grade A class ' and MATH >
2. (
3. SELECT MAX (MATH) from STUDENT WHERE class=' Grade B class '
4. );

In addition, we can also use all, as shown in sql:

[SQL]View Plaincopyprint?

1. SELECT NAME from STUDENT WHERE class=' Grade A ' and MATH > All
2. (
3. SELECT MATH from STUDENT WHERE class=' Grade B class '
4. );

At this point, we can sum up the correspondence between Some,any,all and MIN, MAX:

[Plain]View Plaincopyprint?

1. > Any (sub-qurey)---> MIN (Sub-qurey)
2. < any (sub-query)---< MAX (Sub-qurey)
3. > All (sub-query)---> MAX (Sub-qurey)
4. < All (sub-query)---< MIN (Sub-qurey)

At this point, you should understand the role of the Some,any,all keyword. This is all about math, and you're wrong if you think you'll get a similar answer to a similar operation on your language score. All of the reasons are caused by null. Let's discuss the effect of NULL on individual keywords.

[SQL]View Plaincopyprint?

2. ---statement 1
4. SELECT NAME from STUDENT WHERE class=' Grade A class ' and Chinese >
6. (
8. SELECT MAX (Chinese) from STUDENT WHERE class=' Grade B class '
10. );
14. ---statement 2
16. SELECT NAME from STUDENT WHERE class=' Grade A class ' and Chinese > all
18. (
20. SELECT Chinese from STUDENT WHERE class=' Grade B class '
22. );

In general, we think that statement 1 and statement 2 will return the same result, however, the result of the above two statements is surprising, statement 1 returns Harry, Statement 2 does not return anything, why does this happen? The MAX function ignores null values by default, so statement 1 returns Harry. However, Statement 2 does not ignore NULL, and any values and null comparisons return an unknown value. Not only that, the following two statements also return different results:

[SQL]View Plaincopyprint?

2. ---statement 1
4. SELECT NAME from STUDENT WHERE class=' Grade A class ' and Chinese <
6. (
8. SELECT MIN (Chinese) from STUDENT WHERE class=' Grade B class '
10. );
14. ---statement 2
16. SELECT NAME from STUDENT WHERE class=' Grade A class ' and Chinese < all
18. (
20. SELECT Chinese from STUDENT WHERE class=' Grade B class '
22. );

Go Oracle Some,any,all,exists,in