Set-based Programming in SQL

TLDR set-based programming is specifying what rather than how. Describe the data you want. Let the engine decide how to get it.

Let's conceptualize it.

Let Go of Control

Remember when you first learned to program? I remember learning about control flow near the very beginning: if, else, for loops. I wrote procedural code because my brain was still grasping the basics.

This is how I imagine my early code looked.

// procedural
const filteredItems = [];
for (let i = 0; i < items.length; i++) {
  if (items[i].price < 5) {
    break;
  }
  filteredItems.push(items[i]);
}

Notice all the things that must be specified:

• Start at the beginning of the list let i = 0
• Go through all the items i < items.length
• Do not skip any items i++
• Break on the filtering condition if (items[i].price < 5) { break; }
• Push the current item into an array variable filteredItems.push(items[i])

So much noise to filter on one condition.

Let's compare that to the functional style.

// functional
const filteredItems = items.filter(item => item.price < 5);

This style of programming is very popular and for good reason.

It's a one-liner and that's nice but the real benefit is the signal to noise ratio. You tell the program what you want. It is declarative. You don't specify the details of how it happens. You trust the array.filter method. Good SQL follows the same path, trusting the engine.

The analogy comparing procedural code to functional code and procedural SQL to declarative SQL is not perfect but I think it is useful.

SQL - What, Not How

In SQL we don't have to specify how the engine should do things. We just need to tell it what to do. Control flow concepts like if, else, break, for are antithetical to set-based SQL.

If you solve SQL problems with control flow the code becomes difficult to read and it will not scale. It will burn CPU cycles and hog memory. You are hammering in a screw.

The core of set-based programming is what, not how. Describe the data you want. Let the SQL engine decide how to get it.

Is Control Flow a Thing in SQL?

Yes, here are some of the keywords:

• BREAK
• CONTINUE
• GOTO just no..
• WHILE
• Cursors - OPEN, FETCH, CLOSE

Don't use these if you're writing new SQL. You wouldn't disable garbage collection on a new project built with a modern language, would you? Then don't write procedural SQL.

You may need to learn the procedural syntax in SQL to understand how to get rid of it in a codebase you've inherited. Cross that bridge when you get there.

Oddly enough, a project I'd worked on with tons of procedural SQL also had roll-your-own garbage collection in the app code.

The Beautiful Parts of SQL

By removing control flow we're left with a fun and useful subset of the language.

• SELECT, INSERT, UPDATE, DELETE
• SUM, AVERAGE, Aggregate Functions..
• GROUP BY, SORT
• JOINs
• CTEs

If you learn these well, you will write good SQL queries. You won't be pigeonholed when you need to squeeze out more performance with the proper tools.