Learn about ALL and SOME/ANY

Query Xtractor is mainly a productivity tool. But it can be also used to learn SQL. One particularly great way to do it is to turn ON one of the Always Emulate options. We have this for grouping sets, crosstabs, Intersect/Except, All/Some/Any. With the setting OFF, we use the native support when available, and we emulate features when there is no other way. The obvious advantage in having ALL/SOME/ANY expressions emulated as well for let’s say SQL Server – where they are recognized and implemented by the vendor – is to better understand what they actually do through equivalent constructs.

The story of SOME vs ANY

Initial SQL syntax supported just ALL and ANY. ALL is an universal quantifier, while ANY was supposed to always be an existential quantifier. However, in English, ANY is frequently used as an universal quantifier as well. “I can beat ANY of you” is not synonym of “I can beat SOME of you“. It’s in fact synonym of “I can beat ALL of you“, each and every one of you!

With ANY being confusing, SOME has been introduced as a more reliable synonym for ANY with the adoption of the SQL-92 standard. ANY was supposed to be retained for a while just for backward compatibility with previous product versions. But we still have it today.

This is why we have a Use SOME setting, by default true, that you can switch to always use either SOME, or ANY. It is not recommended to use sometimes SOME and sometimes ANY in your queries. So the switch prevents this and helps you be consistent.

With history being told, we recommend of course to leave it on the SOME version, it’s the best practice. For the rest of this article however, we’ll stick with ANY.

>= ALL and <= ANY

ALL/ANY may appear in Where or Having clauses, in conditional expressions like dept.DEPTNO < ALL (SELECT DEPTNO from emp) or dept.DEPTNO = ANY (SELECT DEPTNO FROM emp).

Following queries return department numbers from dept, linked with related numbers from emp through such an expression. > ALL (>= ALL is similar) means find all those values greater than each matching value from the other table. It’s interesting now to look at the generated SQL. > ALL has been actually emulated through > (SELECT MAX(DEPTNO) FROM emp). That’s because a number greater than the maximum of some other numbers is also greater than each of those numbers. It is enough to compare it with the greatest.

< ANY (<= ANY is similar) means find all those values less than at least one matching value from the other table. And < ANY has been actually emulated through < (SELECT MAX(DEPTNO) FROM emp).

<= ALL and >= ANY

Following queries translate into similar expressions, but with MIN instead of MAX. <= ALL (< ALL is similar) gets translated into <= (SELECT MIN(DEPTNO) FROM emp), while >= ANY (> ANY is similar) gets emulated as >= (SELECT MIN(DEPTNO) FROM emp).

<> ALL and = ANY

These combinations will be emulated with lists. <> ALL becomes NOT IN (SELECT MIN(DEPTNO) FROM emp), while = ANY becomes IN (SELECT MIN(DEPTNO) FROM emp). This is because different from all means none of those values, and equal with any means one of those values at least.

= ALL and <> ANY

These two combinations will show a “cannot be emulated” error at this time, because we found no easy way to emulate them. They are also more rarely used and a bit confused. = ALL means each dept value must be equal with all values from emp. <> ANY means each dept value must be different from at least one value from emp.

Conclusion

Examine our emulated SQL queries to learn about equivalent expressions when features do not have native support on current database platform. By turning Always Emulate settings ON, you may enjoy emulation – and learn more about SQL – even when the features are built in by the vendor.

Remark that all equivalent notations we found for ALL/ANY here could be designed directly with our builder as well. You may choose directly If value Is [not] in list, or MIN/MAX inline aggregators, instead of All and Any.