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

Joins When No Join Types Are Valid

Hugo Kornelis has a brain-teaser for us: The query below can be executed in any version of the AdventureWorks sample database. Don’t bother understanding the logic, there is none. It is merely constructed to show how SQL Server handles what appears to be an impossible situation. 1 2 3 4 SELECT          d1.Name, d2.GroupName FROM            HumanResources.Department AS d1 FULL OUTER […]

Read More

Implementation Matters: CTEs In Postgres And SQL Server

Brent Ozar looks at a couple of places where Postgres and SQL Server differ in implementation details: In SQL Server, if you write this query: 1 2 3 4 With AllPosts AS (SELECT * FROM StackOverflow.dbo.Posts) SELECT *   FROM AllPosts   WHERE Id = 1; SQL Server builds a query plan for the entire operation at […]

Read More

Categories

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