SQL isn't supposed to be composed. It is a query language for a relational database structure. What composition is to run of the mill programming languages is what relations are to databases, roughly speaking.
>SQL lacks support for some pretty basic things like query aliases. Try writing 5 nested queries vs using variables to store the different criteria.
"Basic things" that are basic things for programming languages. SQL has support for what it should have support for - having intermediary variables (in the sense that general purpose programming languages have variables) is not one of those things. This comment belies a lack of understanding of how databases work and why.
>SQL has unnecessary grammar restrictions. Where you're putting your ORDER BY should be irrevant. Join syntax is frankly horrendous. It's difficult to autocomplete.
The decision regarding where to order is absolutely relevant and is strongly dependent on your data model. Join syntax is a syntax to accomplish joins, which are fundamental to modeling of relational data.
And, frankly, if autocomplete is a criteria for you, then, in the context of these tools, you are not a developer, you are an end user.
>Embedding SQL in your code is a recipe for disaster. Your compiler does not understand it, therefore refactoring means going over every query you have and manually looking to see what changes to do. It cannot be validated in compile time. SQL strings have no knowledge of the backing domain until they're actually run.
This statement makes it clear that you have not seriously developed an application that is strongly dependent on a serious database. At least almost everything you said here is "not even wrong": https://en.wikipedia.org/wiki/Not_even_wrong
>And finally, 99% of the time you really do want to fetch and object with relations from the DB, not simple maps of field names. Aliasing field names in SQL from joins is a waste of my time. Making the returned data map to whatever object actually contains the business logic I'm interested in gets old, fast.
I suggest you take a look at database design and relational information modeling. You really seem to not know what the problems are that relational databases solve, how to use them, or how to use them well.
How do you build a filtered search system? Query languages can definitely be composed. Look at Codd's relational algebra operators; they can most definitely be composed.
You can't do this in SQL:
base_query = get_base_query()
query = base_query.filter(condition_a)
query = base_query.filter(condition_b)
> SQL has support for what it should have support for - having intermediary variables (in the sense that general purpose programming languages have variables) is not one of those things. This comment belies a lack of understanding of how databases work and why.
Um, no. In fact the WITH statement was added in the latest SQL standard to help with this because, well... it turns out writing 5 nested queries is a pain.
> And, frankly, if autocomplete is a criteria for you, then, in the context of these tools, you are not a developer, you are an end user.
And you've never heard of data analysts who literally spend all day doing nothing but running queries and reports? You've never run drafts of queries to see if you're doing reasonable things?
> I suggest you take a look at database design and relational information modeling. You really seem to not know what the problems are that relational databases solve, how to use them, or how to use them well.
If you're going to lecture someone when you have no proof of backing your assertions with real-life use cases then I suggest you actually go read Codd's original paper on relational algebra rather than praising SQL as something handed down from the gods. It's an imperfect representation of the tree structure of relational queries composed of projections, joins, and selections.
If you're going to be cargo culting SQL, at least actually understand what the original was. SQL was the 70's idea of a "natural-looking" language to model relational algebra. And relational algebra is only one way to understand relational data stores (tuple calculus is another decent formalism for understanding it as well). And relational stores are only one of many different data modeling tools.
There is literally nothing in SQL that you cannot do more ergonomically with most query builders, since everything boils down to the same thing: an abstract tree of query expressions that can be understood by a DB engine and manipulated through basic relational algebra rules to optimize according to heuristics.