[pcblues]

I have been developing software that includes SQL for twenty years, and watched my own mental progress from misunderstanding to understanding. I found the biggest initial problem is that I used to imagine SQL queries as an imperative language rather than as expressions of data. Maybe in the teaching of SQL, this should be highlighted so absolute beginners can have that mental model when they are formulating solutions and grappling with the syntax.

[k__]

I don't know if that's enough.

Understanding the difference between declarative and imperative programming is rather hard with all the abstractions we have today.

People always say, declarative programming is defining what you want, not doing the steps needed to get it. But today no imperative interface requires you to do all the steps either, plus, most programming languages use both paradigms at the same time.

[btilly]

Understanding the difference between declarative and imperative programming is rather hard with all the abstractions we have today.

The distinction is are you telling the computer *how* to do it, or telling it *what* steps to take.

If, even with access to all of the code, you'd have to ask the computer how it chose to do it to figure out what it did, you have a declarative system. If the code reads like instructions for a recipe, it is imperative.

The complications come with the fact that these two paradigms do not describe all of the possibilities. Notably object oriented and functional designs are neither imperative or declarative. (But may share some features with both.)

[k__]

Yes, that's probably what was always my problem.

Theory is one thing, but actual programming languages are something different. An "impure" mix.

So, when people told my language X is imperative and language Y is declarative, I got confused, because they often had parts of both.

[zbtaylor1]

Can you give an example? "How to do it" and "what steps to take" still sound like the same thing to me.

[btilly]

Sorry, I meant "what you wish done" and not "what steps to take".

A "how to do it" would look like this:

Search A, pulling out foo and bar. For each record you get, go to index B.foo, find where the B records are such that A.foo = B.foo. Now go to those records, and then read baz. Return all triples of foo, bar, baz that you found.

An equivalent "what to do" is:

    SELECT A.foo, A.bar, B.baz
    FROM A JOIN B ON A.foo = B.foo;

Note, you're not saying which table to go to first. You're not saying which index to go to. You're not saying how to do it. And indeed if there are several possible ways to do it, you don't know what it actually chose to do unless you do an EXPLAIN ANALYZE to ask to tell you.

Honestly I highly recommend the exercise of building a toy database, complete with indexes and simple tables. Then take a few queries against a real database, look at EXPLAIN ANALYZE, and then write the same code against your toy database. See how much code there is. Walk through what it does. Compare to what the real database did. Etc.

It is an amazing exercise for understanding what a database does for you. And you haven't even dealt with transactions, concurrency, etc, etc, etc.

Furthermore if you do run into one of the rare cases where the database is truly the wrong tool (this has happened to me a couple of times), knowing what a database would have done is very helpful in figuring out how you can do it better.

(Honestly, doing the same thing as the database, but in a low-level language like C++, without transactions and concurrency, and with specialized data structures, can easily lead to 1 to 2 orders of magnitude performance improvement. It is rare for this to be a good trade-off. But having done the exercise that I described when it didn't matter will position you well if you do need it.)

[10000truths]

I still feel like a functional query language would have made for an easier learning experience:

  PROJECT(
    INNER_JOIN(
      "A",
      "B",
      EQUALITY_EXPR(
        COLUMN_EXPR("A", "foo"),
        COLUMN_EXPR("B", "foo"),
      ),
    ),
    ["A.foo", "A.bar", "B.baz"],
  )

Conveys the same intent with a much more consistent syntax than a pseudo-English grammar.

[zbtaylor1]

Thank you!

[k__]

How to do it:

for(i=0;i<a.length;i++) { b[i] = a[i] + 10 }

Define your wish:

b = a.map(x => x + 10)

But things get muddy because many languages offer higher level for-loops and forEach functions, etc.

[pcblues]

I guess my point was that if you are trying to achieve results in a language paradigm that isn't the one the language was designed for, the learning curve is _really_ steep, and to use the declarative features of any language still requires you to understand the paradigms' differences. When I was at uni it wasn't until the final year that Programming Paradigms was a course, but even a rough introduction to them in any of the languages I studied earlier would have helped. Something like, "This language is used like this. It is not used like this, for example."

[k__]

I'm not even sure, I totally understood the difference now, 10 years after I studied CS.

[dboreham]

I've made significant $$ over the years being a person who can understand the imperative code the database server will likely execute to make that declarative goodness happen.

[tanin]

This is actually quite important.

Some tasks that are seemingly simple in a normal programming language can sometimes be impossible to achieve in SQL (e.g. dynamically generated columns...)

[SilverRed]

Thats where you create a massive beast of code which dynamically generates SQL from fragments with string interpolation!

[CRConrad]

Exactly. Thats when you create a massive beast of code which dynamically generates SQL from fragments with string interpolation... In SQL.