[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.