How to Find the Closest Subset Sum with SQL

I've stumbled upon this very interesting question on Stack Overflow, recently. Its title is: [How to] compare a number with sum of subset of numbers In this article, we'll compare the user's imperative approach to the extremely elegant (Oracle) SQL approach. We'll be making use of any combination of these awesome SQL features: Window functions … Continue reading How to Find the Closest Subset Sum with SQL

The Danger of Subtype Polymorphism Applied to Tuples

Java 8 has lambdas and streams, but no tuples, which is a shame. This is why we have implemented tuples in jOOλ - Java 8's missing parts. Tuples are really boring value type containers. Essentially, they're just an enumeration of types like these: public class Tuple2<T1, T2> { public final T1 v1; public final T2 … Continue reading The Danger of Subtype Polymorphism Applied to Tuples

jOOQ Tuesdays: Markus Winand is on a Modern SQL Mission

Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month where we interview someone we find exciting in our industry from a jOOQ perspective. This includes people who work with SQL, Java, Open Source, and a variety of other related topics. We are excited to … Continue reading jOOQ Tuesdays: Markus Winand is on a Modern SQL Mission

The 10 Most Popular DB Engines (SQL and NoSQL) in 2015

About two years ago, we've published this post about the 10 most popular DB engines, where we analyzed the data published by Solid IT on their DB Ranking website. In the meantime, the Solid IT measurement system has found to be a credible source, such that the website has also been cited at Gartner, InfoWorld, … Continue reading The 10 Most Popular DB Engines (SQL and NoSQL) in 2015

Semi Join and Anti Join Should Have Their Own Syntax in SQL

Relational algebra nicely describes the various operations that we know in SQL as well from a more abstract, formal perspective. One of the most common relational JOIN operations is the "equi-join" or SQL INNER JOIN. The above example "equi-joins" the ACTOR, FILM_ACTOR, and FILM tables from the Sakila database, in order to produce a new … Continue reading Semi Join and Anti Join Should Have Their Own Syntax in SQL

A Beginner’s Guide to Using Java EE with jOOQ

Java EE ships with its own persistence API: JPA. JPA is most powerful when you want to map your RDBMS entities (tables / relations) to Java entities (classes), mostly following a 1:1 mapping strategy. The idea behind this is that often, business logic isn't really set-oriented as relational algebra or SQL, but record-oriented, meaning that … Continue reading A Beginner’s Guide to Using Java EE with jOOQ

How to Quickly Enumerate Indexes in Oracle 11gR2

Do you want to know real quick what kind of indexes there are on any given table in your Oracle schema? Nothing simpler than that. Just run the following query: SELECT i.index_name, listagg(c.column_name, ', ') WITHIN GROUP (ORDER BY c.column_position) AS columns FROM all_indexes i JOIN all_ind_columns c ON i.index_name = c.index_name WHERE i.table_name = … Continue reading How to Quickly Enumerate Indexes in Oracle 11gR2

You Probably don’t Use SQL INTERSECT or EXCEPT Often Enough

When people talk about SQL JOIN, they often use Venn Diagrams to illustrate inclusion and exclusion of the two joined sets: While these Venn diagrams are certainly useful to understand (and remember) SQL JOIN syntax, they're not entirely accurate, because SQL JOIN is a special type of a cartesian product, the CROSS JOIN. In a … Continue reading You Probably don’t Use SQL INTERSECT or EXCEPT Often Enough