Quantified Comparsion Predicates

Kevin Feasel

2016-07-13

Syntax

Lukas Eder discusses the ALL, SOME, and ANY predicates:

Ultimately, you should always choose performance first, and then – most certainly – intuitiveness second (because some poor soul might need to maintain your query). But personally, I find these quantifiers quite elegant for three reasons:

  1. They express the quantification right where it belongs. With the comparison operator. Compare this with the solution using LIMIT, which may be far away, visually, from the greater-than operator. Quantifiers are much more concise, even than when using MAX() (in my opinion)

  2. They’re very set oriented. I like thinking in terms of sets when I work with SQL. Whenever I can omit the ORDER BY clause, I will. If only to avoid potentially slow operations (in case the database doesn’t optimise this, and a full O(N log N) sort operation is invoked)

  3. Quantified comparison predicates work on rows too, not just on single values.

I’ve known about these, but could probably count on one hand the number of times I’ve ever used one.

Related Posts

Window Functions In WHERE Clauses

Shane O’Neill covers an annoying but necessary thing to remember around window functions: Now, a new learner of SQL comes along with the requirement to find the last 2 rows per PartitionId. They are diligent and enthusiastic and have just read about Windows Functions. They think to themselves Wow! This is great! I can do […]

Read More

Query Tuning With The APPLY Operator

Daniel Janik walks through using the APPLY operator to tune a couple of queries: Recently we were doing a project that heavily focused on query tuning and many tables had various outer joins. My co-worker pointed out that many of these could be converted to an apply rather than a join. Apply gives you both […]

Read More

Categories

July 2016
MTWTFSS
« Jun Aug »
 123
45678910
11121314151617
18192021222324
25262728293031