{"id":67441,"date":"2016-08-25T13:26:08","date_gmt":"2016-08-25T13:26:08","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=67441"},"modified":"2021-09-29T16:21:20","modified_gmt":"2021-09-29T16:21:20","slug":"predicates-with-subqueries","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/predicates-with-subqueries\/","title":{"rendered":"Predicates With Subqueries"},"content":{"rendered":"

You\u2019ve probably read that SQL is a language based on sets and predicates. Well, that sounds impressive but what does it really mean? It means that we ought to have some predicates that use sets as their operands. And we do; but a lot of them are a bit obscure and not often used. All of them can be written with the [NOT] EXISTS () predicate and other logical operators in correlated subqueries. While that is perfectly OK, the results can be a bit hard to read. Let us examine some of these features and see how we can use them.\u00a0<\/strong><\/p>\n

The [NOT] EXISTS() Predicate<\/h2>\n

I am going to assume by now that you have seen an EXISTS()<\/code> predicate in SQL. , but perhaps you do not yet fully appreciate it. It existed at the very start of the language, and it has a nice intuitive meaning. This is because it is one of the few predicates that we have, perhaps the only one, \u00a0that evaluates to either TRUE<\/code> and FALSE<\/code>, but never UNKNOWN<\/code>.<\/p>\n

The current BNF is very simple.<\/p>\n

<exists predicate> ::= EXISTS <table subquery><\/code><\/p>\n

Originally, the <table subquery><\/code> had to be a one column table. This is because the EXISTS()<\/code> predicate was defined in the same part of the standard<\/em> that gave us the \u201c<expression <comp op> ALL <table subquery><\/code>\u201d and \u201c<expression <comp op> [SOME|ANY] <table subquery><\/code>\u201d predicate definitions (we will get to them in another section so be patient). Originally, comparison operators were defined only for scalars; currently standard SQL allows row-based comparisons.<\/p>\n

The single column restriction does not make any sense for the EXISTS()<\/code> predicate; it is clearly a table-based function. If the cardinality of <table subquery><\/code> is greater than zero, then the result of the <exists predicate><\/code> is TRUE<\/code>; otherwise, the result of the <exists predicate> is FALSE<\/code>.<\/p>\n

This is why there was a popular piece of folklore that the best way to write EXISTS()<\/code> predicate was “EXISTS (SELECT <constant> FROM\u2026)<\/code>” or “EXISTS (SELECT <expression> FROM\u2026)<\/code>” and it actually did work that way in some of the very early SQL engines; they actually materialized the query expression table! However, using “EXISTS (SELECT * FROM\u2026)<\/code>” defined the asterisk as a single undefined column. It is the preferred choice today, but we see it as standing for an entire row, <\/em>not a column. Hey, it is hard to write good, clear BNF for a new language! The truth is that internally optimizers quickly got rid of the materialization, and simply evaluated the table expression until they got a row.<\/p>\n

It was also confusing in those days to realize that a NULL<\/code> exists. There were proposals to make a table subquery of all NULLs<\/code> return an UNKNOWN<\/code> result from EXISTS()<\/code>. We decided against it, on the grounds that existence is at a “higher level” than exact, known values. Imagine that you have a paper bag and cannot see what is in it, but you can still pick it up and know of it has some kind of contents.<\/p>\n

Quantified Comparison Predicates: SOME ANY and ALL\u00a0<\/strong><\/h2>\n

These predicates have been in the language from the very early days. They were our attempts to get the universal quantifier (“) which means \u201cfor All\u201d and existential quantifier (\uf024) which means \u201cthere exists\u201d from formal logic into SQL. A surprising number of SQL programmers do not even know they exist.<\/p>\n

The current definition of these predicates allows row comparisons, but that\u00a0 has not been implemented in SQL Server. Let us stick to the basic, original scalar value syntax that is in SQL Server.<\/p>\n

<quantified comparison predicate> ::=\r\n<value expression> <quantified comparison predicate part 2>\r\n<quantified comparison predicate part 2> ::=\r\n<comp op> <quantifier> <table subquery>\r\n<quantifier> ::= <all> | <some>\r\n<all> ::= ALL\r\n<some> ::= SOME| ANY<\/pre>\n
The easiest way to think of this is that we are using an abbreviation to distribute the comparisons over a set of AND<\/code>-ed or OR<\/code>-ed simple comparison predicates.\u00a0<\/strong><\/p>\n
The predicate \u201c<value expression> <comp op> ANY <table expression><\/code>\u201d is equivalent to taking each row, call it \u201cs\u201d and assume that they are numbered from 1 to n, and testing <value expression> <comp op><\/code> s with OR<\/code>s between the expanded expressions:<\/p>\n
((<value expression> <comp op> s1)\r\nOR (<value expression> <comp op> s2)\r\n\u00a0 ...\r\nOR (<value expression> <comp op> sn))<\/pre>\n
When you get a single TRUE<\/code> result, the whole predicate is TRUE<\/code>.<\/p>\n
As long as table S has cardinality greater than zero and one non-NULL<\/code> value, you will get a result of TRUE<\/code> or FALSE<\/code>. The keyword SOME<\/code> is the same as ANY<\/code>; it is just a matter of style and readability. Likewise, \u201c<value expression> <comp op> ALL <table expression><\/code>\u201d takes each row s<\/code> of table S<\/code> and tests <value expression> <comp op><\/code>s with AND<\/code>s between the expanded expressions:<\/p>\n
((<value expression> <comp op> s1)\r\nAND (<value expression> <comp op> s2)\r\n\u00a0 ...\r\nAND (<value expression> <comp op> sn))\r\n<\/pre>\n
When you get a single FALSE<\/code> result, the whole predicate is FALSE<\/code>. As long as table S<\/code> has cardinality greater than zero and all non-NULL<\/code> values, you will get a result of TRUE<\/code> or FALSE<\/code>.<\/p>\n
That sounds reasonable so far. Now let EmptyTable<\/code> be an empty table (no rows, cardinality zero) and NULLTable <\/code>be a table with only NULL<\/code>s in its rows (but cardinality greater than zero). The rules for SQL say that \u201c<value expression> <comp op> ALL NullTable<\/code>\u201d always returns UNKNOWN<\/code>, and likewise \u201c<value expression> <comp op> ANY NullTable<\/code>\u201d always returns UNKNOWN<\/code>. This makes sense, because every row comparison test in the expansion would return UNKNOWN<\/code>, so the series of OR<\/code> and AND<\/code> operators would behave in the usual way.<\/p>\n
This is easier to see with actual values, we can use\u00a0 a table with (10, NULL, 10)<\/code> and value expression of 10 to get:<\/p>\n
(s1 = 10) AND (s2 = 10) AND (s3 = 10)\r\n(10 = 10) AND (NULL = 10) AND (10 = 10)\r\n(TRUE) AND (UNKNOWN) AND (TRUE)\r\n(UNKNOWN) AND (TRUE)\r\n(UNKNOWN)<\/pre>\n
See how SQL’s three valued logic can bite us? In the DML, and unknown is rejected, but in the DDL an unknown is accepted.Likewise, the table\u00a0 (10, NULL, 25) yields<\/p>\n
(s1 = 10) AND (s2 = 10) AND (s3 = 10)\r\n(10 = 10) AND (NULL = 10) AND (25 = 10)\r\n(TRUE) AND (UNKNOWN) AND (FALSE)\r\n(UNKNOWN) AND (FALSE)\r\n(UNKNOWN)<\/pre>\n
Repeat the exercise with ORs:<\/p>\n
(s1 = 10) OR (s2 = 10) OR (s3 = 10)\r\n(10 = 10) OR (NULL = 10) OR (10 = 10)\r\n(TRUE) OR (UNKNOWN) OR (TRUE)\r\n(UNKNOWN) OR (TRUE)\r\n(TRUE)<\/pre>\n
Likewise, the row (10, NULL, 25) yields<\/p>\n
(s1 = 10) OR (s2 = 10) OR (s3 = 10)\r\n(10 = 10) OR (NULL = 10) OR (25 = 10)\r\n(TRUE) OR (UNKNOWN) OR (FALSE)\r\n(TRUE) OR (FALSE)\r\n(TRUE)<\/pre>\n
However, \u201c<value expression> <comp op> ALL EmptyTable<\/code>\u201d always returns TRUE<\/code> and \u201c<value expression> <comp op> ANY EmptyTable<\/code>\u201d always returns FALSE<\/code>. Most people have no trouble seeing why the ANY<\/code> predicate works that way; you cannot find a match, so the result is FALSE<\/code>. But most people have lots of trouble seeing why the ALL<\/code> predicate is TRUE<\/code>. This convention is called existential import in formal logic. If I were to walk into a bar and announce that I can beat any pink elephant in the bar, that would be a true statement. The fact that there are no pink elephants in the bar merely shows that the problem is reduced to the minimum case.<\/p>\n
This was actually a major issue in the early days of symbolic logic. Lewis Carroll believed in existential import, which means if you say “all men are mortal” you imply “some men (at least one) exists” but historically logic went against them. That is because we define them in terms of\u00a0 AND<\/code> and OR<\/code> to give us some very nice mathematical properties.<\/p>\n
\u00a0 <\/code>1) \u2203x P(x) = \u00ac\u2203 x \u00acP(x)<\/code><\/p>\n
\u00a0 <\/code>2) \u2203x P(x) = \u00ac\u2200 \u00acP(x)<\/code><\/p>\n
This rule lets us use the [NOT] EXISTS()<\/code> predicate in some cases. The “Table1.x <comp op> ALL (SELECT y FROM Table2 WHERE <search condition>)<\/code>” predicate converts to:<\/p>\n
\u00a0... NOT EXISTS (SELECT *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Table1, Table2\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE Table1.x <comp op> Table2.y\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND NOT <search condition>)...\r\n\r\n<\/pre>\n
The “Table1.x <comp op> ANY (SELECT y FROM Table2 WHERE <search\u00a0condition>)\"<\/code> predicate converts to<\/p>\n
\u00a0... EXISTS (SELECT *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Table1, Table2\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE Table1.x <comp op> Table2.y\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND <search condition>) ...\r\n<\/pre>\n
Of the two quantified predicates, the\u00a0 ALL<\/code> predicate is used more. The ANY predicate is more easily replaced and more naturally written with an EXISTS() predicate or an IN predicate. In fact, the standard defines the IN()\u00a0 predicate as shorthand for \u201c= ANY\u201d and the NOT IN predicate as shorthand for \u201c<> ANY\u201d, which is how most people would construct them in English.<\/p>\n
The IN() predicate first appeared in the Pascal programming language, so it is also more natural for programmers. We generally teach the IN() predicate without mentioning that it is an ANY predicate in disguise. Stealing from Pascal, the IN() predicate can have a simple list of expressions (they do not have to be scalars!) Or a sub query. The sub query may or may not be correlated.<\/p>\n
The ALL<\/code> Predicate and Extrema functions<\/h2>\n
It is counter-intuitive at first that these two predicates are not the same in SQL:<\/p>\n
\u00a0 \u00a0x >= (SELECT MAX(y) FROM Table1)<\/code><\/p>\n
\u00a0 \u00a0x >= ALL (SELECT y FROM Table1)<\/code><\/p>\n
but you have to remember the rules for the extrema functions — they drop out all the NULL<\/code>s before returning the greatest or least values. The ALL<\/code> predicate does not drop NULL<\/code>s, so you can get them in the results.<\/p>\n
However, if you know that there are no NULL<\/code>s in a column or are willing to drop the NULL<\/code>s yourself, then you can use the ALL<\/code> predicate to construct single queries to do work that would otherwise be done by two queries. For example, in order to find which manager handles the largest number of products, you would first construct a derived table :<\/p>\n
SELECT manager_name\r\n\u00a0FROM (SELECT manager_name, COUNT(*)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Personnel\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GROUP BY manager_name)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 AS Product_Cnts (manager_name, product_cnt)\r\n\u00a0WHERE product_cnt =\r\n\u00a0\u00a0\u00a0 (SELECT MAX(product_cnt)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Product_Cnts);<\/pre>\n
But Alex Dorfman found a single query solution instead:<\/p>\n
SELECT manager_name, COUNT(*)AS\u00a0 product_cnt\r\n\u00a0 FROM Personnel\r\n\u00a0GROUP BY manager_name\r\nHAVING COUNT(*) + 1\r\n\u00a0\u00a0 > ALL (SELECT DISTINCT COUNT(*)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Personnel\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GROUP BY manager_name);<\/pre>\n
The use of the SELECT DISTINCT<\/code> in the subquery is to guarantee that we do not get duplicate rows when two managers handle the same number of products. You can also add a “.. WHERE dept IS NOT NULL<\/code>” clause to the subquery to get the effect of a true MAX()<\/code> aggregate function.\u00a0<\/strong><\/p>\n
UNIQUE Predicate<\/h2>\n
While SQL Server has the ANSI standard UNIQUE<\/code> constraint, it does not have the UNIQUE<\/code> predicate. The syntax is quite simple and \u00a0can be computed by comparing COUNT (*)<\/code> to COUNT (<value expression >)<\/code>.<\/p>\n
UNIQUE <table subquery><\/pre>\n
If there are no two rows in <table subquery><\/code> such that the value of each column in one row is non-NULL<\/code> and is not distinct from the value of the corresponding column in the other row, then the result of the <unique predicate> is TRUE<\/code>; otherwise, the result of the <unique predicate><\/code> is FALSE<\/code>.<\/p>\n
This is based on the GROUP BY<\/code> equivalence operator.\u00a0 Essentially you are saying the make-believe HAVING<\/code> clause has a COUNT(*) = 1<\/code>. And optimizer does not even have to look at the table if it has unique indexes on the appropriate columns, so implementation should be pretty fast in modern SQL engines.<\/p>\n
MATCH Predicate<\/h2>\n
While not available in SQL Server, the MATCH predicate \u00a0is conceptually useful for other advanced constructs. It involves matching rows against a table. ANSI standard SQL uses it in CASE expressions and declarative referential integrity constraints. It is very hard to fake in SQL server. Here is the syntax<\/p>\n
<match predicate> ::= <row value predicand> <match predicate part 2>\r\n<match predicate part 2>\r\n\u00a0\u00a0 ::= MATCH [UNIQUE] [SIMPLE | PARTIAL | FULL] <table subquery>\r\n<\/pre>\n
Obviously the <row value predicand> has to have the same structure as the table to which it is matched. If neither SIMPLE, PARTIAL, nor FULL is specified, then SIMPLE is implicit. The idea is to take a template and match the table so subquery against a row value. The the matching options handle NULL<\/code>s slightly differently. A full match must have a NULL<\/code> in the corresponding columns or an exact matching value. A partial match gives the benefit of the doubt to the NULL<\/code>s (think of the CHECK() constraint in DDL). A simple match follows the usual rules for row equivalence in DDL. This is probably easier to see with actual example<\/p>\n
Table 1<\/p>\n\n\n\n\n\n\n\n
\n
column_1<\/strong><\/p>\n<\/td>\n
\n
column_2<\/strong><\/p>\n<\/td>\n
\n
column_3<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
10<\/p>\n<\/td>\n
\n
10<\/p>\n<\/td>\n
\n
10<\/p>\n<\/td>\n<\/tr>\n
\n
10<\/p>\n<\/td>\n
\n
10<\/p>\n<\/td>\n
\n
10<\/p>\n<\/td>\n<\/tr>\n
\n
10<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n
\n
10<\/p>\n<\/td>\n<\/tr>\n
\n
10<\/p>\n<\/td>\n
\n
10<\/p>\n<\/td>\n
\n
20<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Here are some examples of <match predicate><\/code> expressions, all of which are TRUE<\/code>:<\/p>\n
... `..\r\n\r\n\r\n... WHERE ROW(10, 10, 10) MATCH SIMPLE\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 (SELECT * FROM Table_1) ...\r\n\r\n... WHERE ROW(10, NULL, 10) MATCH UNIQUE SIMPLE\r\n\u00a0\u00a0\u00a0 (SELECT * FROM Table_1) ...\r\n\r\n... WHERE ROW(NULL, NULL, NULL) MATCH PARTIAL\r\n\u00a0\u00a0\u00a0 (SELECT * FROM Table_1) ...\r\n\r\n... WHERE ROW(10, NULL, 10) MATCH PARTIAL\r\n\u00a0\u00a0\u00a0 (SELECT * FROM Table_1) ...\r\n\r\n... WHERE ROW(NULL, NULL, NULL) MATCH UNIQUE PARTIAL\r\n\u00a0\u00a0\u00a0 (SELECT * FROM Table_1) ...\r\n\r\n... WHERE ROW(NULL, NULL, NULL) MATCH FULL\r\n\u00a0\u00a0\u00a0 (SELECT * FROM Table_1) ...\r\n\r\n... WHERE ROW(10, 10, 10) MATCH FULL\r\n\u00a0\u00a0\u00a0 (SELECT * FROM Table_1) ...<\/pre>\n
And here are examples that are FALSE<\/code>:<\/p>\n
... WHERE ROW(10, 10, 10) MATCH UNIQUE SIMPLE\r\n\u00a0\u00a0\u00a0 (SELECT * FROM Table_1) ...\r\n\r\n... WHERE ROW(10, NULL, 10) MATCH UNIQUE PARTIAL\r\n\u00a0\u00a0\u00a0 (SELECT * FROM Table_1) ...\r\n\r\n... WHERE ROW(10, NULL, 10) MATCH FULL\r\n\u00a0\u00a0\u00a0 (SELECT * FROM Table_1) ...\r\n\r\n... WHERE ROW(10, 10, 10) MATCH UNIQUE FULL\r\n\u00a0\u00a0\u00a0 (SELECT * FROM Table_1) ...<\/pre>\n
Conclusion<\/h1>\n
Set-oriented predicates can greatly simplify the answering of many real-life business questions. \u00a0They are definitely more than mathematical curiosities. There is more to set-oriented predicates in SQL than just the simple IN() and EXISTS(). It will be worthwhile for you to sit down and make up some sample data so you can play with it. Learn how the other features that you might not have known about actually work in this language.<\/p>\n","protected":false},"excerpt":{"rendered":"
The  ALL, SOME and ANY predicates aren’t much used in SQL Server, but they are there. You can use the Exists() predicate instead but the logic is more contorted and difficult to read at a glance. Set-oriented predicates can greatly simplify the answering of many real-life business questions, so it is worth getting familiar with them. Joe Celko explains.…<\/p>\n","protected":false},"author":96214,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[],"coauthors":[6781],"class_list":["post-67441","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/67441","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/96214"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=67441"}],"version-history":[{"count":10,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/67441\/revisions"}],"predecessor-version":[{"id":92506,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/67441\/revisions\/92506"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=67441"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=67441"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=67441"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=67441"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}