Saturday, April 4, 2015

Zipping in SQL

"That I am that same wall; the truth is so."
-- Shakespeare, A Midsummer Night's Dream
Act V, Scene I, Line 160

SQL and higher order functions are completely different.  SQL is used to apply functions against collections of data, while higher order functions are used to apply functions against collections of data.  Totally different.

I mean have you ever seen both Michael Fogus and Itzik Ben-Gan talked about in the same blog post?!?

I have no idea why people classify declarative programming with SQL as completely different than declarative programming with higher order functions.

I'll use this blog post as a jumping off point to show that they are very similar things.

Let's look at the higher order function of zip.  The idea of zip is that you take two or more collections of data and zip them together forming one collection of zipped data.  Say you have a collection of greetings ["Hello", "Hi", "Aloha"] and another collection of names ["Mike", "Kelsey", "Jack"].  Given a zip function which takes one string and concats it with ", " and another string you could zip these collections together with that zip function obtaining the following collection ["Hello, Mike", "Hi, Kelsey", "Aloha, Jack"].

Let's look at this in some code.

In the first example we have C# using the zip method on the String class.  For the C# zip, we need two collections and a lambda express.

In the second example we have Clojure using the map function.  For the Clojure map, we need a function and one or more collections (in this example we have two).

In the third example we have T-SQL using an INNER JOIN along with a CONCAT in the SELECT statement.  An INNER JOIN you say?  Yes, an INNER JOIN can be thought of as the same thing as a zip.  The key is that the key you are matching on (in this case the ROW_NUMBER) needs to match.  In the Clojure example we are using a vector which is an associative data structure with the keys being the index values, which is the exact same thing we have in the SQL!

Please play around with the fiddle I created to show this in action.