Nikhilesh Patel
C:\Users\admin\Desktop\Any\Images\NOT_IN.png

SQL Server universal comparison quantified predicates (ANY, ALL, SOME)

July 27, 2017 by

SQL server provides us with comparison operators to modify subqueries. This article will start with the definition of universal quantification, with a quick brush up with real life logical examples.

In addition, I’ll discuss

  • How the SOME keyword becomes the ISO standard equivalent for ANY
  • The keywords ALL/SOME/ANY
  • Types of queries where universal quantification is helpful, including the comparison of the operators IN and NOT IN.

What are Universal Quantified Predicates

Quantifier

A quantifier is like a logical operator such as “And” or “Or”. This represents a logical formula by specifying a quantity for which a particular statement returns TRUE. It is not a numeric quantity; it binds the variables in a logical proposition

Universal Quantification

Universal quantification is a logical constant,, which clarify as “given any” or “for all. In other words, it is described as “any among a set” or “all in the set”. It conveys that universal quantification can be satisfied by every member of the set. In short, its result depends on each and every member of the set.

For example…

A. Everyone (ALL) in the class spoke English.
B. Someone (ANYONE) in the class spoke English.

The first statement (A) indicates that, all persons in the class must speak English for the statement to be True. To be clearer, the statement will be False if anyone does not speak English. For the next statement (B) “Anyone in the class spoke English”, if anyone in the class can speak English than the statement should be True.

Comparison predicates and SQL server

SQL recognizes the quantifiers ANY (or SOME) and ALL. A quantifier allows one or more expressions to be compared with one or more values. To be clear, it manipulates comparison operators to compare the outer query values to the inner query values.

SOME

Originally, the SQL syntax just supported ALL and ANY. But as we know, ALL and ANY are universal quantifiers. In English, though, ANY is quite frequently used as a universal quantifier also. Take an example of, “I can eat ANY number of mangos.” This is not the same as “I can eat some mangos.” It’s, in fact, the synonym of “I can eat all of the mangos“.

Because the ANY keyword can be confusing, the keyword SOME was introduced in lieu of ANY and was initiated as per SQL-92 standard. That is why, today, you can switch between either SOME or ANY.

ANY/SOME and ALL

ALL (connatural with “AND”)

The purpose of the ALL comparison condition is to compare a scalar value to a subquery with the single column set. The subquery must be followed by the preceded operator such as =, !=, >, <, <=, >=. It’s translated into a series of logical expressions separated by AND operators. For illustrative purposes, X <> ALL (A1, A2, A3) is translated to X <> A1 AND X <> A2 AND X <> A3.

Syntax for ALL and (ANY/SOME)

For our example, I have created one table “patient details”.

To use this data, we want to get patient details of who is alive and whose BP status is greater than BP status of ”Dead” person (In short, rows that include “Alive” and bp_status is higher than the bp_status of all dead people).

Mostly Developer use EXISTS in lieu of ALL.

Described in depth

  • “Field = ALL (sub query)”: IF every value that is returned by the subquery is a match, then it returns true
  • “Field > ALL (sub query)”: IF every value that is returned by the subquery is greater, than it evaluates to TRUE.
  • “Field < ALL (sub query)”: IF every value that is returned by the subquery is smaller, then it evaluates to TRUE.
  • Same with ”Field >= ALL (sub query)” and ” Field <= ALL (sub query)”
  • “Field != ALL (sub query)”: IF every value that is returned by the subquery is not match, then it evaluates to TRUE

ANY/SOME (connatural with “OR”)

The keyword ANY is translated into a series of equality predicates separated by the OR operator, for example, X ANY (A1, A2, A3) is translated to X = A1 OR X = A2 OR X = A3.

In short, ANY (or SOME) allows you to specify the comparison you want in each predicate, such as X<ANY (A1, A2, A3) is translated to X < A1 OR X < A2 OR X < A3.

Exemplification

If I want to get a patient detail whose status is “Alive” and, whose BP status is greater than the minimum status of “Dead” person, then we should use the query below.

Using EXITS instead of ANY

To wrap up ANY keyword,

  • “Field = ANY (sub query)”: IF some (one or more) value that is returned by the subquery is match, then it returns TRUE
  • “Field > ALL (sub query)”: IF some (one or more) value that is returned by the subquery is greater, then it evaluates to TRUE
  • “Field < ALL (sub query)”: IF some (one or more) value that is returned by the subquery is smaller, then it evaluates to TRUE
  • Same with ”Field >= ALL (sub query)” and “Field <= ALL (sub query)”
  • “Field != ALL (sub query)”: IF some (one or more) value that is returned by the subquery is not matched, then it evaluates to TRUE

To sum up, for the following forms, if you specify the ALL or (ANY/SOME) quantifier, when the subquery may return none, one, or several rows.

On the top of that, providing that a subquery contains zero rows as output, the condition becomes TRUE. Take an example of

where the subquery gives zero rows, which means the full query expression estimation is TRUE, so all the rows are captured.

To be more specific, in SQL server, ANY/SOME and ALL require a subquery as input. So, instead of v <> ANY (b1, b2, b3), you would write to v <>ANY (SELECT b1 UNION ALL SELECT b2 UNION ALL SELECT b3).

IN

The “=ANY” operator is equivalent to IN. you can use either IN or =ANY.

For clarification:


The screenshot is from ApexSQL Plan, a free tool to view and analyze SQL Server query execution plans


NOT IN

You can get the same results with the <> ALL operator, which is equivalent to NOT IN.

For interpretation:



The execution plan above is for a small amount of data, so it looks like same for both cases. It may be varied accordingly the size of data it retrieves.

Note that, IN allows, as input, either a list of literals or a subquery returning a single column.

Simply put, universal quantification is a topic that is somewhat obscure, but I hope you have found these explanations and examples interesting and perhaps useful in some cases.

See more

To view and analyze SQL Server query execution plans for free, check out ApexSQL Plan

References



Nikhilesh Patel

Nikhilesh Patel

Nikhilesh Patel is a database professional having 7+ years of experience. Most of his vocation focuses on database design, development, administration, performance tuning and optimization (both SQL Server and Oracle). He has collaborated with SQL Server 2000/2005/2008/2012/2014/2016, Oracle and PostgreSQL databases. He has fabricated and developed databases for insurance, telecom and communication domain. He is a database administrator at HighQ solution. He continuously develops his professional skills to keep accelerating with new technologies.

In his downtime, he enjoys spending time with his family, especially with his wife. On the top of that, he loves to do wondering and exploring different places.

Say hi and catch him on LinkedIn
Nikhilesh Patel
Query analysis

About Nikhilesh Patel

Nikhilesh Patel is a database professional having 7+ years of experience. Most of his vocation focuses on database design, development, administration, performance tuning and optimization (both SQL Server and Oracle). He has collaborated with SQL Server 2000/2005/2008/2012/2014/2016, Oracle and PostgreSQL databases. He has fabricated and developed databases for insurance, telecom and communication domain. He is a database administrator at HighQ solution. He continuously develops his professional skills to keep accelerating with new technologies.

In his downtime, he enjoys spending time with his family, especially with his wife. On the top of that, he loves to do wondering and exploring different places.

Say hi and catch him on LinkedIn

259 Views