Have you ever wondered about the use-case behind SQL's ANY (also: SOME) and ALL keywords? You have probably not yet encountered these keywords in the wild. Yet they can be extremely useful. But first, let's see how they're defined in the SQL standard. The easy part: 8.7 <quantified comparison predicate> Function Specify a quantified comparison. … Continue reading A Wonderful SQL Feature: Quantified Comparison Predicates (ANY, ALL)
Tag: row value expressions
Yet Another 10 Common Mistakes Java Developers Make When Writing SQL (You Won’t BELIEVE the Last One)
(Sorry for that click-bait heading. Couldn't resist ;-) ) We're on a mission. To teach you SQL. But mostly, we want to teach you how to appreciate SQL. You'll love it! Getting SQL right or wrong shouldn't be about that You're-Doing-It-Wrong™ attitude that can be encountered often when evangelists promote their object of evangelism. Getting … Continue reading Yet Another 10 Common Mistakes Java Developers Make When Writing SQL (You Won’t BELIEVE the Last One)
Counting Distinct Records in SQL
The SQL language and its depths... Some of you readers might be aware of MySQL's capability of counting distinct records through the COUNT() aggregate function. The MySQL documentation reads: COUNT(DISTINCT expr,[expr...]) Returns a count of the number of rows with different non-NULL expr values. In other words, you can count distinct first and last names very easily: SELECT … Continue reading Counting Distinct Records in SQL
SQL Query Transformation Fun: Predicates with Row Value Expressions
Recently, I've blogged about how well jOOQ's supported databases implement row value expressions and predicates formed from them. Some sample articles: Row value expressions and the BETWEEN predicate Row value expressions and the NULL predicate A Typesafety Comparison of SQL Access APIs Row value expressions (or records, tuples) are useful to express more complex predicates, such as this … Continue reading SQL Query Transformation Fun: Predicates with Row Value Expressions
A Typesafety Comparison of SQL Access APIs
SQL is a very expressive and distinct language. It is one of the few declarative languages which are used by a broad audience in everyday work. As a declarative language, SQL allows to specify what we're expecting as output, not how this output should be produced. As a side-effect of this, ad-hoc record data types … Continue reading A Typesafety Comparison of SQL Access APIs
Row value expressions and the BETWEEN predicate
Now this is a simple example of how SQL clause simulation can get nasty if you want to make use of some more advanced SQL clauses that aren't supported in all databases. Consider the following predicate and equivalent transformations thereof: The BETWEEN predicate The BETWEEN predicate is a convenient form of expressing the fact that … Continue reading Row value expressions and the BETWEEN predicate
Row value expressions and the NULL predicate
Row value expressions are something very powerful in SQL. They have been around since the early days of standard SQL, e.g. in SQL 1992, even if not all databases implement them correctly, still today. As always, the NULL predicate is one that is a bit tricky to understand, also in the context of row value … Continue reading Row value expressions and the NULL predicate