# SQL vs. Streams: For Comprehension… It’s All the Same

# SQL vs. Streams: For Comprehension… It’s All the Same

### When it comes to coding in SQL vs. coding in Java streams, they're not as different as you might think. In fact basically they are the same to understand.

Join the DZone community and get the full member experience.

Join For FreeMariaDB TX**,** proven in production and driven by the community, is a complete database solution for any and every enterprise — a modern database for modern applications.

Recently, at Devoxx, I saw this beautiful slide in a talk by Kevlin Henney.

In his talk, he was displaying a variety of approaches to solve the FizzBuzz “problem”, including a couple of very elegant solutions in completely declarative approaches and languages.

In this particular slide, Kevlin used a notation that is derived from math. The set builder notation. Here’s an example from Wikipedia:

The example reads: For all `n`

in `ℤ`

(the set of all integer numbers), take those for which there exists (`∃`

) another integer `k`

, for which the following equation is satisfied: `n = 2k`

.

Or in plain English: All even integers. (Because for even integers, there exists another integer that is half the even integer.)

Beautiful, eh? In imperative programming, we’d probably do something like this instead:

```
List<Integer> even = new ArrayList<>();
for (int i = /* hmm...? */; i < /* what to put here */; i++)
even.add(i * 2);
```

Or this:

```
List<Integer> even = new ArrayList<>();
for (int i = /* hmm...? */; i < /* what to put here */; i = i + 2)
even.add(i);
```

But there are several problems with the imperative approach:

- We have to realistically start somewhere.
- We have to realistically end somewhere.
- We have to store all values in an intermediate collection.

Sure, those aren’t severe limitations in every day use-cases, because we’re probably solving a real world problem where we don’t actually need an infinite number of even integers, and storing them in an intermediate collection doesn’t consume all of our memory, but still, the declarative, mathematical approach is much leaner because we can still answer those questions about where to start and where to end later, and we never need to materialise any intermediate collection before we make those final decisions.

For instance, we can *declare* X to be that set, and then *declare* Y to be a set that is derived from X, and finally materialise Z, which is a very tiny set derived from Y. For this, we may have never needed to materialise all the (even) integers.

## How This Compares to SQL

Kevlin made a cunning comparison. Of course, all functional programming aficionados will immediately recognize that languages like Scala have something called a “for comprehension”, which models precisely the mathematical set-builder notation.

Java 8 now has the Streams API, which allows us, to some extent, model something similar (although not as powerful). But Kevlin didn’t use those “modern” languages. He used SQL as a comparison. That “arcane” declarative programming language that has been around forever, and that we love so much. Yes, here’s how we can *declare* all the even numbers in SQL:

`SELECT n FROM integers WHERE MOD(n, 2) = 0`

If optimizers were perfect, this semi-self-join between the two references of the `integers`

“table” could be optimized perfectly. In most databases, we’d probably manually transform the above notation to this equivalent one:

```
SELECT n
FROM integers
WHERE MOD(n, 2) = 0
```

Yes, indeed. The set-builder notation and the SQL language are very similar beasts. The former prefers using mathematical symbols for brevity and conciseness, the latter prefers using English words to connect the different operators, but it’s *the same thing*. And if you squint hard enough, you’ll see that Java 8 Streams, for instance, are also pretty much the same thing:

I’ve blogged about this recently where all the Java 8 Streams operations are compared to their SQL clause counterparts: Common SQL Clauses and Their Equivalents in Java 8 Streams.

## How Is This Better?

It’s simple. Both the set-builder notation, and the SQL language (and in principle, other languages’ for comprehensions) are *declarative*. They are expressions, which can be composed to other, more complex expressions, without necessarily executing them.

Remember the imperative approach? We tell the machine *exactly* what to do:

- Start counting from this particular minimal integer value
- Stop counting at this particular maximal integer value
- Store all even integers in between in this particular intermediate collection

What if we don’t actually need negative integers? What if we just wanted to have a utility that calculates even integers and then reuse that to list all positive integers? Or, all positive integers less than 100? Etc.

In the imperative approach, we have to refactor constantly, to avoid the overhead of

- Producing too many integers
- Storing too many integers (or storing them at all)

In truly declarative languages like SQL, we’re just describing “even integers” with an expression, possibly assigning the expression a name:

```
CREATE VIEW even_integers AS
SELECT n
FROM integers
WHERE EXISTS (
SELECT k
FROM integers
WHERE k = 2 * n
)
```

So, when we actually *use* and materialize the even integers, e.g. positive integers less than 100, the optimizer can optimize away the double access to the `integer`

table and produce only the exact number of values that we’re requesting (without materializing them in intermediate collections):

```
SELECT n
FROM even_integers
WHERE n BETWEEN 0 AND 100
```

## Conclusion

Thinking in terms of sets, in terms of declaring sets, has always been our dream as software engineers. The approach is extremely compelling and elegant. We can delegate a lot of boring algorithmic work to the implementation engine of the declarative programming language. In the case of SQL, it would be a SQL database optimiser, which figures out a great lot of optimizations that we might not have thought of.

The above example is trivial. We can perfectly live in a world where we manually iterate over a local integer variable that goes from 0 to 100:

```
for (int i = 0; i <= 100; i++)
doSomething(i);
```

But stuff gets hairy quite quickly. Compare Mario Fusco‘s famous tweet’s two versions of the same algorithm:

Imperative vs. Functional — Separation of Concerns

This also applies to SQL, and what’s even better in SQL than with Streams: The SQL statement is a declarative expression tree, not a formally ordered set of stream pipeline operations. The optimiser can freely reorder/transform the expression tree into something that it thinks is more optimal. This isn’t just a promise. This works in modern SQL databases every day, for very complex queries, which you can write in a matter of seconds, rather than hours.

Stay tuned for a short series of blog posts on the jOOQ blog illustrating what modern cost-based optimization can do for you, when you’re using the SQL language.

MariaDB AX is an open source database for modern analytics: distributed, columnar and easy to use.

Published at DZone with permission of Lukas Eder , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

## {{ parent.title || parent.header.title}}

## {{ parent.tldr }}

## {{ parent.linkDescription }}

{{ parent.urlSource.name }}