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).
|
1 2 3 4 5 6 7 8 |
select* from patient_details whereStatus='Alive' and BP_status >ALL(select BP_status from patient_details whereStatus='Dead' ) |
Mostly Developer use EXISTS in lieu of ALL.
|
1 2 3 4 5 6 7 8 9 10 |
select* from patient_details dtl whereStatus='Alive' andnotEXISTS ( select BP_status from patient_details p_dtl where dtl.BP_status < = p_dtl.BP_status and Status='Dead' ) |
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.
|
1 2 3 4 5 6 7 |
select*from patient_details whereStatus='Alive' andBP_status >any(select BP_status from patient_details whereStatus='Dead' ) |
Using EXITS instead of ANY
|
1 2 3 4 5 6 7 8 9 10 |
select* from patient_details dtl whereStatus='Alive' andEXISTS( select BP_status from patient_details p_dtl where dtl.BP_status <= p_dtl. BP_status and Status='Dead' ) |
|
1 2 3 4 5 6 7 8 9 10 |
select* from patient_details dtl whereStatus='Alive' and BP_status >( select min(BP_status) from patient_details p_dtl where dtl.BP_status <= p_dtl. BP_status and Status='Dead' ) |
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
|
1 2 3 |
BP_status <strong>>ALL</strong>(select BP_status from patient_details whereStatus='Healthy' |
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:
|
1 2 3 4 5 6 7 8 |
select* from patient_details dtl where BP_status =any( select BP_status from patient_details p_dtl where Status='Dead' ) |
|
1 2 3 4 5 6 7 8 |
select* from patient_details dtl where BP_status IN( select BP_status from patient_details p_dtl where Status='Dead' ) |
NOT IN
You can get the same results with the <> ALL operator, which is equivalent to NOT IN.
For interpretation:
|
1 2 3 4 5 6 7 8 |
select* from patient_details dtl where BP_status <>ALL( select BP_status from patient_details p_dtl where Status='Dead' ) |
|
1 2 3 4 5 6 7 |
select* from patient_details dtl where BP_status NOTin( select BP_status from patient_details p_dtl where Status='Dead') |
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.
References
- Comparison Operators Modified by ANY, SOME, or ALL
- SOME | ANY (Transact-SQL)
- Quantified Expressions (XQuery)
- ALL (Transact-SQL)
- SOME | ANY (Transact-SQL)
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
- SSIS Web Service Tasks - May 14, 2019
- Spatial SQL data types in SQL Server - July 11, 2018
- The HashBytes function in T-SQL - May 16, 2018