IMO a lot of the issue is that developers for many years using Java or PHP, were using SQL to handle everything. The application language was a pass through later between the client and the database.

Your goal was to accomplish as much as possible in a single query and then to simply return the results of that query to the interface. That meant formatting numbers or currency in your SQL. Optimizing inserts or updates to be handled in a single query. Grouping, counting, left/inner joins, having statements to filter on aggregate results. More than 1 or 2 queries for the primary area of the screen was both a rare and foreign experience.

And then ORMs started to slowly integrate themselves into the flow of various frameworks to automate the repetitive things around CRUD tasks. Then to address scaling & bloat problems we saw an uptick in REST APIs, microservices that further made those ORMs the norm...and then many developers started actively trying to stay within those API constraints to an almost religious degree which led to a nested payload becoming acceptable fueling the whole "NoSQL" situation, along with the idea that it was somehow better to repeat the same data thousands of times over.

A whole lot of people pushed back against this and eventually, it mostly ran its course. I've often seen resistance to SQL to be driven by fear of SQL more so than anything else. As soon as people get a basic comfort level with SQL, it become almost automatic.

This style of doing things resulted in spaghetti style unmanageable databases, filled with an unknowable number of triggers and procedures, all written in PL/SQL (which is much, much worse than either Java or PHP). The reason why ORMs started to become popular is that you can write your application without filling your DB with arcane and inscrutable logic

Here's a powerful mindset trick: think of SQL views as an sort of a REST API, but whose access language is SQL and not HTTP, and that returns data in a table rather than JSON (hierarchical).

I once tried to build a REST API to a database, and someone told me I already had a battle-tested and highly performant API that outperformed REST at scale -- it's called SQL. A SQL view is a dynamic lens into the underlying tables, so even if the underlying tables/schemas were to change, your consumers don't care as long as they can access the SQL View.

SQL views are also composable: you can build SQL views on top of other SQL views, and any changes made in the base views are propagated throughout. Need to add/transform a field? Do it in the view. Need pull in auxiliary data? Bring it in through a JOIN in the view. I've built many systems by composing SQL views and they're very maintainable and very flexible. They're kind of like function compositions but on tabular data.

The rule of thumb is: always access a database through a view, never the underlying raw tables. In computer science, a great many maintainability issues are alleviated through a layer of abstraction/indirection, and SQL views provide exactly that.

This centralization of the core logic becomes especially powerful if the database is accessed from multiple consumers (webapps, analytics backends, Tableau, ML tools, etc.) The "API" remains consistent throughout.

Two questions:

Do you have any example code that shows how this works? I get what you’re saying intuitively but example code will help me bring it to table.

What about cross cutting concerns? I’ve found stored procedures to be a performant solution here. By version controlling them, and limiting to pure functions, I found them quite maintainable. Would you instead just define a new view, or extend an existing one, or refactor into a separate view that’s then joined into the existing views?

I haven’t delved as far as views, admittedly. One app featured a bit of complicated logic and eschewing the ORM in favour of raw SQL helped (instead of getting tangled up in Demeter chains). Despite new developers, who have used purely ORM for years, shitting their pants at the raw SQL, both of us who worked on it felt it was the right call. We feel much better about leveraging more of the database in new projects.

In fact, when we took our experience to a Django project, my colleague wrote a Manager method in such a way that an ORM favouring developer questioned because it looked too much like SQL. But it was the obvious implementation to us after using raw SQL. And, after benchmarking, the most performant.

1. Let me try with a simple example. Suppose you have a fact table A with fields (ItemID, Item, Amt) where Amt is in USD. Rule of thumb is: don't expose A to the consumer; instead write a SQL View V_A and expose that instead:

  CREATE VIEW V_A AS SELECT ItemID, Item, Amt FROM A

  CREATE VIEW V_A_EURO AS SELECT ItemID, Item, Amt * 0.92 AS AmtEUR FROM V_A

Suppose the underlying table A now changes. There's been a merger and the company now stops reporting currencies in USD, and everything is now in British Pounds so your DBA adds a field AmtGBP and starts populating that field instead. Amt still contains historical data, but moving forward the data in Amt will be NULLs; AmtGBP is the new internal baseline currency. From a VIEW perspective, all you have to do is:

  ALTER VIEW V_A AS SELECT ItemID, Item, ISNULL(Amt, AmtGBP * 1.23) AS Amt FROM A

  Table A <- View V_A <- View V_A_Euro

(Possibly a bad idea for currency conversion for various reasons, but just wanted to mention it since this type of migration may be just right for other data)

For production, I created a command on the app that loads the stored procedures into the DB idempotently on each deployment/configuration. This won’t work if the app server scales but allowed us to store stored procs in VC.

For development, we ran the command on each page load as a sort of hacky “live reload”. It didn’t work well (which highlighted the issue with scalability in production) because Postgres, fairly, doesn’t like parallel redefinitions of the same stored proc.

I’m not sure how best to automate this. For production, seems like a case of running a command once per DB server.

And in development, using a fs watcher that loads changes in.

But I don’t know, this is new territory for us and I couldn’t find anything out there to manage it within the context of a web framework. Perhaps I’m searching for the wrong thing.

(.sql/.rb/.py, it doesn't matter).

And you have a "migrations" table in your database that contains the numbers of the migrations that have been run:

  select * from migrations;
      version
  ----------------
   1765
   2891

How to do with functions/procedures?

You commit the function definitions in a functions folder to your version system like:

  db/functions/report_x.sql
  CREATE or REPLACE function report_x() returns ...

  class UpdateReportXFun < ActiveRecord::Migration[5.2]
    def up
      execute File.read(
        Rails.root.join('db','functions','report_x.sql')
      )
    end
  end

I'm doing a lot of work in a Rails codebase where I edit views/functions/procedures all the time. My setup is quite usable.

My current setup: I edit those .sql files and run them with psql in my local while developing (without writing any migration yet).

I have some like this running on one screen to make sure the modified files are executed by psql immediately as I change them (you could use `guard` too):

  find ~/projectx/db/functions -type f -name "*.sql" | entr -d -p psql db_name -f /_

--

Once I finish and I know everything is great, I just add the migration. The migration is simply an indicator of which files I've modified and to specify the right order to run them (which is useful if they are dependencies), like:

  # migration
  def up
    execute File.read(function1_sql_file)
    execute File.read(function2_sql_file)
  end

[ I'm also using pgTAP to write tests for functions, it's quite nice :) ]

I’ll have a look at pgTAP too. Naturally we want to test in CI, I can see this working really well. I did look at myTAP too, since we have a few MySQL instances.

You are conflating many disparate things here.

SQL is a language (DSL) for accessing data. REST is a protocol and a data transport method (one could surmise a way to do REST without HTTP, but when reasonable people refer to REST they mean HTTP (over TCP (over IP (etc.)))).

Even REST is not an API. You can't do anything with a GET or a POST without other abstractions built on top of that. So I don't understand how anyone could make performance claims beyond something like "HTTP is slow" and "binary transport is faster", with regards to SQL vs. REST/HTTP.

SQL does not define how you receive your data. Databases have different methods of sending SQL and responding to SQL. Oracle, MSSQL, MySQL, etc.

> This centralization of the core logic becomes especially powerful if the database is accessed from multiple consumers

That's the entire point of an API. Any API. REST APIs, even.

If you want to make simple views that expose useful nouns I am down with it, but I have seen it taken too far too many times.

If you are building something from scratch, or your consumers don't have a hard requirement for going through REST, I would go directly to the database view.

I think one should view a SQL DB like a microservice. Instead of REST endpoints (or gRPC or whatever), create stored procedures. These define a strong contract with your DB, the capabilities that it provides to your app(s). Now you know what the query and insert patterns are, and can tweak the table layout under the covers without screwing up your application code.

Of course you can abuse this into a spaghetti monolith, just like you can evolve a microservice into a spaghetti monolith, but you shouldn't. There's no technology that will prevent you from making poor architectural decisions, you just have to not go down those dark paths.

> stored procedures. These define a strong contract with your DB

Exactly. It took me years to grasp this, but when I did my code became much simpler.

A REST API (that returns JSON) has to contort a tabular data structure into a loosely-typed hierarchical data structure (JSON) which has to be read back, reconstructed and in many instances type-checked (e.g. DATETIMEs are not native to JSON, nuanced datatypes like DECIMAL(18,3) are lost).

Whereas a SQL interface returns data in its native tabular format with all the correct types.

1) Version Control - I guess a lot of the stored procedures were being put straight into the DB without recording a history of the changes. These days you could easily do this using DB migrations I guess.

2) Testing - is unit testing a thing for Stored Procedures? I guess again, you might be able to do this from code as well programatically adding a stored procedure, running a bunch of tests and removing it again.

I do wonder - what do people generally do in practice for overcoming these objections? Does anyone have any other objections around using stored procedures?

2) TBH we never built anything complex enough to need this, and I would tend to think that if you do need this you're probably overcomplicating your DB. But you could probably do something that creates a temp DB, populates example data, and then runs tests.

My previous employer used some homespun systems for the same. A bit clunky, but easier to do than most people thinks.

You also have graphical debuggers for them.

Not to mention, 50%+ of ORM managed DB schemas that I've observed don't have proper constraints, indexes, relationships, etc. Because the developers using the ORM think it's a magical tool that makes understanding SQL and relational databases optional.

Of course the database vendor and culture also plays a role. We were primarily a mysql/postgresql shop

I'm working on a project with a Postgres database, and as it gets more complex I'm moving more stuff into stored procedures, pretty much wherever a single action requires multiple statements in series (e.g. check if this thing exists, check a value, get the id of some other thing, on success update another table).

Of course I would prefer to leave as much of it in the middle tier as possible because the ergonomics are better there but I don't want to sacrifice performance.

Yeah, if all of your queries are slow, you've got more work to do, but that doesn't change the overall process of optimization.

High performance SQL code should stay on the database, instead of doing wire round trips.

That pretty much describes my career, except it was more than 2-3 weeks. I agree there is value in being familiar with the ins and outs of SQL, but...

> As soon as people get a basic comfort level with SQL, it become almost automatic.

I don't know about that so much. I find that as soon as I need to write something much more complex than a simple select statement I am all but guaranteed to get caught by SQLs many gotchas. Eventually you realize the mistake, and that experience provides the knowledge to know how to correct it, but a good language helps guide you away from being trapped by those mistakes in the first place.

SQL is not a good language. It was clever for its time, but we've learned a lot about language theory in the many decades since. It is a travesty that we haven't put more effort into designing a modern database query language. To use SQL in 2020 is like writing software in COBOL when you could be writing software in Rust. Where is the declarative query language equivalent of Rust, Haskell, etc.?

The NoSQL movement was supposed to be about improving on query languages, but sadly it soon turned into "NotRelational" instead, which killed off any momentum away from SQL that was built.

Maybe Cypher from Neo4j?

I did this (actually for longer), but it always washes away.

One challenge for me is I don't see an incentive to learn complex SQL setups unless absolutely necessary. Since I tend to work at startups, development velocity is way more important than optimized codebases. I'd rather write the "dumb" solution that nearly every engineer can grok than the "elegant" solution that goes above people's heads.

That aggregate function, yes, I know I can do it 100% in SQL. However, it's much easier for me to selectively pull parts into memory and modify them with language tools I work with everyday. When the junior needs to modify that function, they can do it with tools they're most familiar with.

Implementing logic is single SQL queries can definitely be a bit challenging, but I thought it was also quite rewarding and liberating - Raw SQL is incredibly powerful!

Then I was empowered and did not miss for loops.

Now I still miss for loops.

I'll show off the POC if I ever get around to smoothing the rough edges.

i did tons of sql couple of years ago on a reporting team. Now I do android dev fulltime and don't remember any SQL beyond basics, highly doubt it will all come back to me if i tried.

It's declarative, the primitives seem entirely non-intuitive, it often takes a lot of fiddling to get what you want, the behind-the-scenes execution is mostly a black box, and while it's supposed to work the same on different implementations (of browsers/databases), there are tons of little gotcha quirks.

All in all, they're both entirely different skill sets from traditional programming, and also where experience counts for a ton more than just normal logical thinking.

My reaction to that is that it's similar in a different way: everyone needs to use it but many developers don't take it seriously, avoid learning how it works, and then complain that it's unintuitive (i.e. not something they had already known) and hard to use because they're basically just poking randomly until they get close enough to what they want.

It's true that not every implementation is the same but … where else is that not true? This is also true of operating systems, filesystems, every library implementing a standard format (“Why doesn't this PDF open in …”), etc. I would have trouble supporting the belief that databases are an outlier in this regard.

It doesn't matter much for SQL, because I always know what database I'm using, but for CSS, it's a massive pain.

In other words, it's about as true as it is for CSS and SQL, where that kind of simple use is also stable.

- When to use JOIN vs a subquery?

- When is a subquery actually a correlated subquery? Will this destroy your performance? Or is it a critical feature?

- Should you put constraints in the JOIN or in the WHERE? Will the distinction drastically affect performance?

- When do you use WHERE vs HAVING?

- Is the NULL from the join because no joined row was found, or because the joined row had a NULL value itself?

Etc. etc. The basic concepts are simple, but the implementation details quickly become very complex, particularly when you're ensuring high performance with indices, and making sure the query uses the indices.

And all of the questions I pose above have clear answers... but the answers certainly aren't obvious from SQL basic concepts.

And also, while JOIN seems like it ought to be intuitive, in real life it seems like it's like pointers in C -- some people get it pretty quickly, other people struggle forever.

Once you're at the point where you have to worry about these things, tuning the SQL is still probably much less complex than writing the query in your app language or figuring out how a NOSQL db can do these joins.

E.g., for a quick practice run, try optimizing Wordpress without making it a static page via caching - how many queries will you have to optimize and how much of a codebase rewrite will it be to make it significantly more performant?

the problem you describe is completely different and its more a measure of system health in its entirety of the DB. it can be easier to fix or harder depending on the precise cause. but that's a standard debugging skill which isn't strictly in the DBA skillset.

True, but those kinds of questions come up in every language: Should I use an array or a dictionary? Should people have references to projects or should projects have references to people or both? Is money a float, an int, a decimal or should I write my own money class? Should I memoize the results? Is it thread-safe?

As you can see, this could go on forever, pretty much for any language.

"Its easy if you think about it mathematically" is not enough.

There are simply things you have to learn to use a technology. If you want to write Java, you need to know what a variable is, what a for loop is and what Inheritance/Interfaces are. Likewise, in the case of SQL, it means you need to know concepts like normalization, ACID and joins. Just poking around until the code works won't do it.

This article is a great starting point:

https://blog.jooq.org/2016/03/17/10-easy-steps-to-a-complete...

there are many other articles worth reading on there

>the basic concepts are simple, but the implementation details quickly become very complex

This is no different than programming. Assuming that SQL doesn't have complexity because you can only SELECT, INSERT, UPDATE or DELETE is going to have you banging your head against the wall. Tackle the complexity in SQL like you'd tackle the complexity in your programming language of choice; read the docs, work through examples, and read how other people solve the problem. There's ton out there for SQL

>When do you use WHERE vs HAVING?

HAVINGs allow you to add a condition to an aggregate function. So SUM(myColumn) > 5 would be something you put in a HAVING clause. Honestly, this is pretty clear cut.

>Should you put constraints in the JOIN or in the WHERE? Will the distinction drastically affect performance?

The first thing to understand is a condition in a join versus a where might return a different result set, specifically on anything other than an INNER join. The impact to performance will depend on the rest of your query, your data, and your index coverage. For simple cases, there is likely no difference. For complex ones, there may be an impact

> Is the NULL from the join because no joined row was found, or because the joined row had a NULL value itself?

An inner join shouldn't produce a null. That's why it's an inner join, as the data needs to exist in both places. If you want to "test" whether a join found a row, look at the field you were joining to and see if it's a non-null value. Nulls won't join to Nulls unless you've change some settings in most RDBMS. If you're looking at other fields to determine the presence of a row from a join, make sure you're looking at a non-nullable field.

>When to use JOIN vs a subquery?

A better way to phrase this would be when to just join the table, vs writing a sub query and joining to that. When is a question of the complexity of the query and performance characteristics, and that can't be answered in the abstract. The most important thing is that in a large number of cases you can do both, and knowing how to express things in both ways is powerful.

>And all of the questions I pose above have clear answers

No they don't. Any time you're wondering about how different SQL impacts performance, there's absolutely a huge "it depends" angle on it, because how you've structured the tables, index coverage, and the volume of data, can have a significant impact. This is why DBAs still have jobs, because the database is an incredibly complex system. You seem to be complaining that SQL shouldn't be complex, yet are not willing to accept that it is more complex that you've assumed it to be. It's complex. You don't need to know everything if your just a dev, but don't just assume it's simple.

>while JOIN seems like it ought to be intuitive

I'd check the diagram here -> https://stackoverflow.com/questions/13997365/sql-joins-as-ve... Half those joins aren't needed as you can re-order a right join into a left join. For 95% of development Inner joins and left joins are all you need. The other 5% is an outer join and that's mainly needed in report writing, not app development.

Best thing you can do is to learn how to read EXPLAIN ANALYZE results.

Normally, subqueries return a single value whereas joins can result in n rows of output for 1 row being joined on, and you can access all the columns of those n rows. There are ways to make use of more than one value (e.g. (tuple) IN (subquery)) but if you want to SELECT more than one value, you need to join.

Depending on the database, it might be slower to do a correlated subquery than a join though (MySQL especially).

> - When is a subquery actually a correlated subquery? Will this destroy your performance? Or is it a critical feature?

A subquery is a correlated subquery when it references symbols from the outer query. That means it needs to be evaluated once per row, and can't be evaluated once at the start of query execution. It can destroy performance if it needs to be evaluated too often - if it's in your 'where' clause and is evaluated over too many rows, e.g. it's mixed in with a boolean expression that can't be short cut.

> - Should you put constraints in the JOIN or in the WHERE? Will the distinction drastically affect performance?

Conventionally, you should put equi-join constraints (equality expressions with foreign / primary keys in other tables) in the JOIN clause and other constraints in the WHERE clause. For inner joins, it doesn't make a difference where you put the predicate, semantically. There is a semantic difference for outer joins though (left join, right join, full outer join): failure to join results in a tuple worth of null values from one or both sides (left/right vs full), rather than eliminating the row.

Where the semantics aren't different, performance should not be affected. Of course the database engine might be stupid, but a fundamental requirement of a reasonable query planner is in determining (a) join order and (b) which indexes to use for the combination of join predicate and where predicate. No query planner worth its salt won't consider using the where clause along with the ON clause on the JOIN when fetching rows in the joined table.

> - When do you use WHERE vs HAVING?

WHERE is before GROUP BY and filters the rows that enter aggregation (if any), HAVING comes after GROUP BY and filters the aggregated rows. If you use a derived table (a nested query with a table alias), then you can use WHERE instead of HAVING for no semantic difference, but derived tables may execute differently (MySQL will generally materialize them, PostgreSQL will see through them).

> - Is the NULL from the join because no joined row was found, or because the joined row had a NULL value itself?

If the column is nullable, and you used an outer join, you can't tell. Normally you check for the primary key or some other non-nullable column to discover if a join failed (most often used in anti-join, when you want to find all rows that don't have corresponding rows in the join).

Relational algebra, which is what SQL is ultimately based on, is much more elegant.

Triggers, functions, procedures, access control.....

Sometimes it really feels like you're trying to give instructions to someone via chat which gets Google translated to chinese, japanese and russian on the way - it's this very lossy communication channel where you need to tweak the language "just so" to get maximum performance. I think it's no wonder that newer DB designes opted for more direct and tailored APIs.

and yet, for any nontrivial operations, opting for a few simple K:V stores instead means one basically ends up implementing an unrolled SQL engine processing loop to do the application-specific select statements you would otherwise need.

there's a reason SQLite took over from dbm files ...

With SQL you get unintended query plans, but it always returns the same rows (if it doesn't time out).

There have been a number of instances where I have had to look something up seemingly for the first time, found a good Stack Overflow answer, and then chuckled to myself because I had already upvoted that exact answer at some point in the past. SQL queries are a pretty common source of this, along with uncommon git and terminal commands.

The key aspect of SQL that makes it easy for me is readability. Once you know the basics you can understand what a query is doing just by looking at it. Regular expressions notation on the other hand are really though to read, and indeed I keep always forgetting its syntax.

In a given week I might work with all of the following: SQL, C#, Python, JS (Kendo, Vue, React), XSL, bash, and more. I'm a quick learner and I pick things up fast, always have, I've got a deadline and I don't have the time or capacity to fully internalize the minutiae of all the technologies I have to work with. In other words I depend on the tools to show me the options at my disposal and construct syntactically correct expressions.

What I really dislike about SQL is common to most systems I dislike, where the tools aren't discoverable to me. In SQL it boils down to the fundamental syntactical requirement to put the SELECT clause before the FROM clause. So I have to build up my statement in this weird spiral pattern where I change something deeper in before I know what I can SELECT in the first place. The ability to give tables shorthand names with MS-SQL, i.e.

`left join [dbo].[sometable] st`

...is very helpful but I gather this isn't common to all dialects. Working with XML in SQL is a nightmare as the tool cannot tell you whether you can do obvious things like pass an sql:variable into nodes() until you actually query the server. (spoiler: you can't.)

I vastly prefer the functional programming approach to working with data e.g. C#'s lambda style linq. The tool shows me all the pieces I have to work with, and all I have to do is piece them together the right way.

An SQL statement starts with "select ABC.XYZ", but you have no idea what it means, because only one screen later it is written that "ABC" is actually an alias for "T_ACCOUNT_BUSINESS_CREDITS" or something. The logical order would be "from ... where ... select ...".

Imagine a programming language designed like SQL. It would look approximately like this:

  function foo(a) {
    return s;
    b = join(a);
    s = concatenate("[", b, "]");
    by the way, a is list of strings, b is string, s is string;
    also, function "join" is imported from "lists", and "concatenate" is imported from "string" module;
    actually, don't return the result, just tell me how many characters it would have;
  }

The entire statement is a logical one like a mathematical equation. You wouldn't think that the meaning of (a + b) * (c + d) implies that (a + b) has to be done first. Maybe in some computer languages it may like early C compilers.

Given that: "select ABC.XYZ" is perfectly well formed, it's like a forward reference declaration where ABC is the alias for a table-like thing and XYZ is the alias for the column-like thing. I mostly read my SQL inside-out, starting with uncorrelated subqueries.

Another pet peeve: using the keyword INNER or OUTER is just noise.

Okay:

  const { ABC, XYZ } = source_table.get(id = 5);

No offence but if you are at the level of struggling with the syntax then you should not be using SQL until you have more experience. There have been plenty of well-founded critiques of the crappy syntax of SQL, but it's not ultimately that hard. There are worse things you'll have to cope with such as the implications of 3-valued logic that comes with nulls.

The giving-tables-alias-names feature has been standard probably since the very first standard was released. In some cases such as self-joins, it is necessary.

If I can give you a piece of advice, arrange things using the 'with' clause and learn to break things down as simple as possible at each step. Let the optimiser sort things out for you. If performance is poor, look at the query plan. Also understand you don't have to write everything in one huge statement. Spool intermediate results off to temp tables if that helps (not @tables but #tables - @tables have problems with them).

As for the XML, I guess that may be better handled outside the DB. IMO XML should never be made part of SQL. Good luck.

Edit: I'm going to clarify this. It is a mistake to throw CTEs at a beginner but to be clear, CTEs have very important advantages over the using temp tables to hold intermediate results. It comes down to efficiency.

MSSQL only optimises within a single statement; it does not optimise across statements, so if you have a several queries comprising a single CTE, the optimiser has plenty to get its teeth into and may produce a much more efficient query plan.

Also a query plan of a complex CTE can (depending on what you're doing) end up being a straightforward pipeline where one result feeds into another into another and finally gets spat out at the end. That can be very efficient. If you use temp tables you spool intermediate results (which may be large), re-read, spool into another temp table etc. If you're working with a large data set that can use up a lot of memory, and if it's large enough that it has to spill to disk... oh dear.

So for beginners, yes, as you get more expertise, CTEs are the way to go (depending, of course, on various factors)

(Conversely, temp tables do have a definite cardinality, whereas queries in a CTE are estimates and can be badly out leading to very poor query plans. So temp tables can work to your advantage here).

This is the same operation but without the screwup syntax. The whole notion of left join style syntax is an abomination and - excepting for those who have internalized it to the detriment of normal discourse - represents needless cognitive load.

When working with time-indexed data I feel I'm forcing the database to do something it doesn't want to. E.g. if I want to answer the query "How many sales are there per day this month?" and I want an entry for every day in the month, even when there were zero sales. Or another query asking "Which days didn't have sales?" and listing the days. I haven't found a way to do this in-database. I end up answering in code based on the data I fetch.

Definitely feel it's me not SQL, but not found the answer.

Thinking about the intersection of sets is important for grokking what you're trying to do in that example (and with SQL in general). For me, at least, SQL suddenly made a lot of sense when the idea that I was working with intersections and subsets of sets.

In your example you have (1) a set of all dates in a range, and (2) a set of sales totals for days that had sales. Set 1 could be a "numbers table" or generated with something like "generate_series()" in PostgreSQL. Set 2 is made by summarizing the data in a sales table by date using "GROUP BY" and "SUM".

Then you're just looking at JOIN'ing those sets and COAELSCE'ing the NULL returned from days when there are no sales into 0.

I've done it this way when doing it per-minute or per-hour - i.e. for 60 minutes or 24 hours; fixed, constant ranges. I picked this example because (to me at least) it's more difficult :)

Calendar months have different lengths, so something would have to tell it which month to use (and account for leap years). But say you wanted the last 60, 90 or 120 days? I suppose you could first create a temporary table to be the "numbers table", custom made for the range you want to fetch - is there an alternative?

You can use days, months, weeks, years, or whatever you wanted as the "increment" in the CTE using the DATEADD function. Then (for nulls) you simply LEFT JOIN your CTE with the desired date part (aggregated) of your table. This gives you your first answer, and a simply filter will get you your second answer.

It's also common for people to just create all these date tables beforehand as actual, materialized tables. In my opinion, this is less elegant (what happens in 2101?! Somebody had better remember to add to the table!) but it naturally works just as well and probably saves some perf.

EDIT: specifically for your example, in an OLAP style you would generate a Times table and then foreign key the sales table to it based on the date. Then you can easily query against that Times table as the filter/bucket for your queries.

For days as my example has, would the Time table be generated for say 1970-2050? Given months are of different lengths and there are leap years, I'm assuming this is needed, rather than storing a single year.

  date_key | year_num | month_num | day_num | quarter_num | week_num | month_name | month_name_short
  ...
  20200101   2020       1           1         1             1          january      jan
  20200102   2020       1           2         1             1          january      jan
  ...
  20200401   2020       4           1         2             14         april        apr
  ...

I think the Kimball approach gets bogged down in temporal evolution (Slowly Changing Dimension type 1? 2? 3? 4? 5?), but that's more about the underlying absence of meaningful bitemporalism than the dimensional schema approach per se.

If your reporting queries are hard or complex it's because you did a bad job architecting the reporting tables. Reporting queries should almost always be the simplest form of query if you designed your warehouse properly.

Days with sales is easy with a simple SUM or COUNT and GROUP BY.

Days without sales is easy if you think about this dimensionally.

  SELECT dim_days.day_of_month,
    COUNT(fact_orders.order_id)
  /*
    Select from dim_days first because you want every day.
  */
  FROM dim_days
  /*
    Outer join to the fact table to pull in the data you have and add that to your dates.
  */
  LEFT JOIN fact_orders
    ON dim_days.calendar_day = fact_orders.order_day
  /*
    Filter results by the desired range.
  */
  WHERE dim_days.month = 4
    AND dim_days.year = 2020
  GROUP BY dim_days.day_of_month;

"SQL is easy. Data is hard."

Aggregate functions in SQL are IMHO quite awesome once you develop a comfort level to stop worrying about them per se. I wouldn't like to try to get Excel to tell me--or write the code to do manually--something like "show me the standard deviation in units sold by day of week over the last ten summers."

SELECT -> map

FROM -> stream

JOIN -> flatMap

WHERE -> filter

GROUP BY -> collect

ORDER BY -> sorted

https://blog.jooq.org/2015/08/13/common-sql-clauses-and-thei...

  x <- ..
  y <- ..
  return (x, y)

This is explained in the blog post I linked, including an example.

  List<Integer> s1 = Stream.of(1, 2);
  Supplier<Stream<String>> s2 = ()->Stream.of("A", "B");

  s1.flatMap(v1 -> s2.get()
                   .map(v2 -> tuple(v1, v2)))
    .forEach(System.out::println);

Until I started thinking about SQL as manipulating sets it never "clicked". Once it did, though, a whole world of applications filled with mismatched procedural thinking mapped on to SQL was revealed to me, and my own work became much easier.

I suspect it was mostly because the tree-like structures people were trying to represent are an absolute pain to work with when you have to shove them into two dimensional rows and columns. I doubt SQL itself had much to do with it as ORMs were already all the rage when Mongo emerged. But ORMs only slightly improve on papering over the data structure impedance mismatch. Most of the pain points present with using SQL directly remain in ORMs when it comes to this problem.

With Mongo you can just throw the tree at it and it will happily store it and nicely give it back again. Which eventually leads to its own set of problems due to how it handles said tree internally, but that's why it has fallen out of favour and SQL databases are the new hotness again.

You'll notice the frontrunners in the NoSQL movement of the time weren't relational databases with a different query language. They all took different approaches to dealing with data itself. Sometimes, humorously, they even maintained SQL as the query language. NoSQL didn't come to mean "No SQL" at all, but rather "Not Relational".

As an aside, while it might go a bit outside the spirit of SQL and introduces its own challenges, I remain somewhat amazed that we haven't seen a standard way emerge to query tree-like structures to better reflect the needs of a fairly common use case. You can kind of get there in unconventional ways, like using json_agg in Postgres, but that all seems pretty hacky.

It would be really nice if there were databases that attempted to expose a data interface that is more tree-friendly. That's why I'm keeping an eye out on projects like EdgeDB and DGraph, and even Hasura.

We run into problems when someone with a proclivity to one side meets a problem best solved by thinking on the other side of the spectrum. An example is a young dev in my organization who was given requirements for a new app that required a data store. Not being comfortable with SQL or relational databases, he chose a document store. It wasn't a good fit. Very quickly requirements expanded and caused his code to balloon into a mess of nested loops with lots of if-checks. Performance has progressively decayed as well. A simple multi-table join with filtering would have knocked off 2/3 of the code.

Well, it's more attuned to the dynamically typed mindset, sure. Programmers who understand the value of static type systems should understand the value of relational schemas.

(EDIT - and as another data point I dislike SQL's syntax. The semantics are bearable but the syntax just makes my brain melt)

Agree that SQL's syntax is rather bad.

[0] https://www.postgresql.org/docs/10/datatype-json.html

[1] https://docs.microsoft.com/en-us/sql/t-sql/functions/json-qu...

Yes I get that but I was trying to make the point that there's more to the relational model than just assurances. It offers a richly entwined semantic structure and an elegant route to reducing duplication and the risk on inconsistency that comes with it. None of this is in tension with dynamic typing.

(Also - dynamic languages aren't just for sloppy thinkers and cowboy coders. There's actual value that is lost in moving to static typing even if you think the trade-off is well worth it)

I think it's because I usually touch SQL like once a month and by the time I need it I already forgot everything :shrug:

This kind of "magic" isn't always clear when programmers are mostly used to working with data structures and procedural code.

The other problem, IMO, is that programming languages are very poor at bridging the difference between the SQL domain and the language domain. We really need plugins for compilers because ORM libraries often are harder to learn than the database itself.

Actually in past year I discovered that JetBrains IDEs (in my case PyCharm) have nice feature that seems like not everyone is aware. It is due to their integration with DataGrip. If you configure the IDE to connect to your database, it will start looking for SQL in your strings, it will then do code highlighting, autocomplete table names and fields. If you use refactoring it will understand SQL, if you refactor database it will produce migration statements. It appears to solve all the issues that ORM supposed to solve, and you still have full control since SQL didn't need to be abstracted from you.

Edit: this video shows how it works in action (forwarded to a relevant part): https://youtu.be/_FlpiNno088?t=2868

My problem is that it is like some sort of black magic to me. If I write a complex query I have no idea if what is spit back to me is actually what I want. The only way is seeding lots of records and then manually checking that each filter and calculation is doing what I want.

In code complex things can be broken down into more simple items. Then I can reason about and test those building blocks into something I understand and am confident that is working as intended.

[1] https://en.wikipedia.org/wiki/QUEL_query_languages

I know PG has a bunch of procedural-language alternatives, but afaik, no relational-language alternatives.

At the same time, I'd also expect it to not be that difficult to transpile from say mysql to postgres, yet there's very little in that space, at least in open source (there is however many ORMS that map to either mysql or postgres..)

Both of which, amongst the procedural-languages, you'd find a hundred transpilers (JS->C) and VM-languages (Clojure,Scala,etc on jvm) implemented even by bored/curious students. Which makes my suspicion that there's no technical blocker, just a cultural one.

That is, QUEL losing to SQL, such that you can't find any implementation, is an absurd concept -- it should be available on postgres (perhaps requiring some special starting keyword) -- but somehow it is not.

libpq refers to PostQUEL.

Ingres became Post-Ingres which became POSTGRES which became Postgres95 which became PostgreSQL.

https://www.postgresql.org/docs/12/history.html

Depends a lot on the language. I've lost countless hours to things like JOOQ trying to figure out how to get it to do what I want, or express the query in its quirky not-quite-right DSL, plus dealing with mappings, pojos, auto-generation, and so on.

However, on the other hand, in a dynamic language with just enough support to move the result of your queries into a map of key/value, I feel no friction at all. I'm using little more than a simple jdbc wrapper in Clojure and even after months on the project, I'm still just continuously stoked with seamless the whole thing is.

However, I have found it worthwhile to learn to use the more advanced features you mention. Extending type safety to queries is incredibly useful. Consider cases when developers are making code and schema changes concurrently that overlap.

[1] https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/Result...

For example, if I have a `timestamp with time zone` column in PostgreSQL, that is represented in the generated code. I will be prevented from inserting a Java `String` into that column - I will have to provide an `OffsetDateTime`. (This is how it would be in a typical configuration - it is flexible enough to do pretty much anything).

Another example, let's say I reference some column in a query in one of my feature branches. Meanwhile, somebody has dropped that column on the master branch. When I rebase my feature branch onto master, my build will fail.

Having the database automatically figure out how to run a query is a great feature, but most of the time I'd happily just write explicit nested loops for the sake of predictability.

What's interesting to me is that apparently the CS program at my university hardly did anything with SQL, and I notice too that most programmers I meet "in the wild" are lacking in SQL skills, as you mentioned. It's led to some interesting interview situations where I really struggle with any questions about algorithms (my college courses didn't cover algorithms at all) while the interviewer will tell me that I have the best SQL skills of anyone they interviewed.

Speaking with others that went to other universities, I've heard that it's similar elsewhere for the "business/programming" to include SQL classes but eschew algorithms, while CS programs will ignore SQL but focus heavily on algorithms. It seems to me like both programs could benefit from meeting in the middle a bit.

Most ORMs are bad for queries (pull over all the objects and look at their properties!), but LINQ will actually turn your code into SQL under the hood with some remarkable machinery.

i would change that to ORMs CAN be bad for queries. most ORMs are super configurable and can be changed only to pull specific stuff.

i would argue that most ORMs are a plus to productivity because for most of cases, you just need simple querying (select * from, simple updates, simple deletes).

also, if you really need the power of raw sql you an just use that -- and even then, for most advanced ORMs (django's, for example) exposes a LOT of really advanced sql stuff in python, which, for a lot of developers, is a lot more expressive.

Of course using raw SQL is possible but might force you into converting other parts of the code into raw SQL. There is Arel, but it's just a more verbose SQL in quasi-AST form.

On the other hand, I never had much problems with LINQ or Hibernate.

I sometimes wonder if the debate about ORM is mainly driven by some traumatic horror witnessed at some point in every developer's career. If we judged programming languages by the same metric... Oh... Actually we kind often do that too!

If you're doing complex reporting queries, an ORM is strictly worse. And yes, I've seen developers, architects, and authors of ORMs that believed otherwise. They are wrong.

As an example, very, very few ORMs can make the distinction between

    SELECT ...
    FROM foo
      LEFT JOIN bar
        ON foo.id = bar.foo_id
          AND bar.category_id = 5
      LEFT JOIN baz
        ON bar.id = baz.bar_id
    ...

    SELECT ...
    FROM foo
      LEFT JOIN bar
        ON foo.id = bar.foo_id
      LEFT JOIN baz
        ON bar.id = baz.bar_id
          AND bar.category_id = 5
    ...

If so, the result of the query would be identical but the second one would likely have performance problems given the category_id filter is not being applied at the point where bar is joined. A clever database engine might find an optimisation but I wouldn't count on it.

This illustrates the subtle problem that ORMs can introduce - all logical tests pass but issues emerge over time. I found that typically every ORM based problem was resolved by rewriting it using SQL.

I used to use ORMs extensively, but my philosophy now is that you can make simple queries by using a query generator (i.e. connection.GetByID("MyTable", 100)) and complex queries have to be written by hand. I would not choose to use an ORM again.

With inner joins the semantics are the same. If the performance is different, the database query optimizer is broken.

However, I'm reasonably certain we'd see poor performance with that query on MySQL 5.7, but I don't think it's got as good an optimiser as Postgres for example.

Even SQLite figures this one out and does it right.

In the second case, the first join will always return a row from bar for a given foo_id if it exists, but the second join does not return values from baz for rows where bar.category_id <> 5.

SQL maybe a few hundred hours? And off and on again rather than constantly so I don't always remember beyond the basics. The concepts make sense to me but the language always feels foreign.

It certainly makes sense for all developers to learn more SQL! But it just hasn't been part of the daily work requirement for many.

As for the attraction of noSQL databases, I think perhaps it's more of a new-hotness trend rather than a serious technical decision. NoSQL has its place, but like most of this kind of tooling, it's often applied incorrectly because of a lack of thinking through the business logic.

So when I needed one for my personal stuff, I went with CouchDB, because it's just a very nice RESTful API around a data structure I understand well.

The first time ever using Mongo I had a cloud cluster connected and working in about 10 minutes after signing up. Trying to integrate SQLite took me around 1.5 hours before it was functional. To this day I have yet to set up a real cloud SQL database because the one time I tried it with PostreSQL I just couldnt get it to work.

People struggling with sql because they usually don't know easier way to query that they resulting to use more complex query. For example `select where id in` and `case when` are both powerful query that many don't know or under utilized.

With `select where in` for example, we can do object mapping / join on application level instead db level.

but throwing strings over the wire, trying to get as much as possible without crossing cardinalities and ignoring the n query problem makes SQL at scale disastrous

Devs always get one of these things wrong and not being able to use my programming language in the query makes some things hard to express

Datalog and Datomic are definitely worth learning https://youtu.be/Pz_NvY1kw6I

    INSERT INTO "my_table"
       "col1": value1,
       "col2": value2,
       ...

insert into Permissions(UserId, Create, Read, Update, Delete, Share, ForceUnlock, LaunchNukes) values (12345, 1, 1, 0, 1, 0, 0, 1);

When I wrote my (now unmaintained) statically typed SQL dialect for F#, which compiles to underlying SQLite/Postgres/MSSQL, I added a single-row insert with Field=Value because it's nice to have and took no more than 30 minutes to do. It's only a tweak to the parser after all -- you just parse it to the same AST used to represent the `INSERT ... VALUES` clause and all later stages of the compilation do not need to know about it.

https://rspeele.gitbooks.io/rezoom-sql/doc/Language/InsertSt...

    INSERT INTO tbl WITH AUTO NAME
    SELECT 'foo' AS name, 1 AS en1, 0 AS en2;

    INSERT INTO tbl SET a = 1, b = 2;

Thankfully we had one semester course on databases, all the respective layers, got to implement B+Tree indexes in C, and a GUI application using Oracle's Forms their 90's Visual Basic variant, but using PL/SQL instead.

I definitely don't think in SQL.

That said, I like working w SQL databases -- just please give me a battle tested ORM...

In my mind the best example of this is grouping.

Just like most languages now have "Map" and "Filter" functions in their standard libraries, many now also have some form of "GroupBy" function for working with lists and objects in memory. Invariably the meaning of this is that it takes:

  1. A list of Xs

  2. A key function from an X to a Y, where Ys can be tested for equality

A list of pairs (Y, Xs), each pair containing:

  1. A key value Y

  2. A list of Xs from the input list matching that key

By contrast in SQL, the first problem is super easy - group by department, select sum(salary). The second problem trips a lot of people up because suddenly you aren't really using GROUP BY anymore, even though the thing you're doing is still a "grouping" task. I think the main way to do it is to use ROW_NUMBER() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) to get a ranking number for each employee then select only those with ranking <= 3. Why do I think that's worse?

1. If you were to describe to a person what you're trying to accomplish, the first step would be the same for problems 1 and 2 - get the employees for each department. But the queries in SQL are not alike and do not appear to share the same first step.

2. There needed to be dedicated extra language syntax to do this, instead of having one function (not even special syntax) that gives you the essence of grouping - which other functions then aggregate/massage further into whatever you need. This makes it harder to learn and harder to pick the right tool for a given query job.

3. Locality is almost fundamental to programming. I like writing the inner dept.OrderByDescending(e => e.Salary).Take(3) in the real programming language, because I can think locally to "this department's employees" and even down to "this employee's salary". In SQL, you're a mile above the data tying things together with push pins and string by ID, row number, etc. You rarely get to think locally.

4. In the same vein as above, structured data maps better to how I think. When I write data code, the metaphors in my head involve places and containers - drawers, shelves, moving things around. I am organizing the objects into boxes, going into each box, and picking my top 3. I am not marking each object with a number then going over them all as a big set and tossing out the ones with numbers greater than 3.

5. When I get my result from this query, I will probably want to work with it in the nested-list form, even if only to display it with merged cells for the the department (so I don't repeat every department name 3 times). Funny enough, if not using an ORM, I'll end up using my language's GroupBy to massage the flat rows back into this form.

Despite all this I want even less to do with NoSQL. I don't want to chuck blobs of JSON into the DB and end up with duplicate data, orphan data, ill-defined schemas, data that's only efficiently queryable one way, etc. I really do want to store my data in a relational form, and a normalized one where possible. I am happy with my tables. I just would love to query them in a LINQ-like language that is capable of representing and manipulating nested data structures -- and returning them to my application as such.