Does anyone else think SQL needs help?
I have been writing a decent amount of SQL for three years now, and while the paradigm is extremely powerful, the language lends itself to unmaintainable spaghetti code quickly.
Fundamentally, SQL lacks meaningful abstraction, and there's no sane way to package very common operations.
Say you want to find elements in a row that correspond to some maximum value when grouped by date. Today, you'd need to write something like this EVERY SINGLE TIME:
``` SELECT sd1.sale_person_id, sd1.sale_person_name, sd1.no_products_sold, sd1.commission_percentage, sd1.sales_department FROM sales_department_details sd1 INNER JOIN (SELECT sale_person_id, MAX(no_products_sold) AS no_products_sold FROM sales_department_details GROUP BY sale_person_id) sd2 ON sd1.sale_person_id = sd2.sale_person_id AND sd1.no_products_sold = sd2.no_products_sold; ```
Wouldn't something like this be nicer?
``` SELECT sale_person_id, max(no_products_sold) as max_sold, link(sale_person_name, max_sold), FROM sales_department_details ```
Frankly, it seems like some sort of macro system is needed. Perhaps the SQL compiles into the above?
Agreed that SQL lends itself to spaghetti. However after working with it for 25 years both as a developer but also collaborating with analysts and data scientists, I have to say I appreciate its basic declarative nature and remarkable portability as it has expanded into distributed databases.
For me the value of being able to walk up to an unfamiliar database of widely varying tech and start getting value out of it immediately is the killer app. Macros or other DB-specific extensions would be useful at times, but I’m not sure how much they would enable solving the messiness problem in a generic way that reshapes the paradigm more broadly. My instinct is the messiness is a consequence of the direct utility and trying to make it nicer from a programmerly aesthetics point of view might be hard to sell. It’s not like SQL doesn’t have affordances for aesthetics already (eg. WITH clauses).
The story of the past 10 years at my FAANG has been one of making our key-value databases look as much like relational databases as possible, complete with SQL query engines (at least two of them, because what would a FAANG be without multiple slightly incompatible ways of doing things?). Honestly it's been a huge boon. Should we have abandoned SQL entirely, and come up with a new language? Maybe. But given that the primary initial motivation for this work was to allow analysts to creation millions of reports without bothering eng, it had to be a language that analysts understand. That's SQL.
I can't believe that at time of this writing, only a few other comments are mentioning VIEWs! Like that is this dude's exact complaint!
smh...
Frankly, the biggest problem with SQL is that not enough people know it well (and a lot of people think they know it well).
I find CTEs (the WITH statement) greatly improved my ability to run more complex queries because they offer an abstraction I can use to name and compose queries together.
SQL views are useful for that kind of thing too.
Wouldn't
SELECT * FROM
(SELECT DENSE_RANK() OVER ( PARTITION BY sd1.sale_person_id ORDER BY sd1.no_products_sold DESC) r ,sd1.sale_person_id ,sd1.sale_person_name ,sd1.no_products_sold ,sd1.commission_percentage ,sd1.sales_department
FROM sales_department_details sd1) sd
Where sd.r = 1
do the same without the inner join?
You could take a look at EdgeDB. It's built on postgres, but uses its own query language and data model. Its data model has build in support for links and polymorphism. The query language EdgeQL makes it easy to follow links and output embedded arrays.
>Frankly, it seems like some sort of macro system is needed
Views, materialized views, CTEs.
I've written about this topic a fair amount; first examining some of the criticisms of SQL that have been present since the language's inception [1], then looking at a new project called Malloy that I'm quite excited about, and think has a lot of potential to address some of the problems of SQL as it relates to data analysis [2].
[1]: https://carlineng.com/?postid=sql-critique#blog [2]: https://carlineng.com/?postid=malloy-intro#blog
What do you think of PRQL ("prequel")?
This is a very poorly written SQL.. Try some modern syntax: select DISTINCT sale_person_id, sale_person_name, max(no_products_sold) over (partition by sale_person_id) AS max_products_sold, commission_percentage, sales_department from no_products_sold;
I think every dialect has an idiomatic way to do what you're asking. e.g., in Snowflake it's `select * from sales_department_details qualify no_products_sold = max(no_products_sold) over (partition by sales_person_id)`, PSQL `select distinct on (sales_person_id) * from sales_department_details order by sales_person_id, no_products_sold desc`, ...
It sounds like you just reinvented VIEWs?
Elixir's Ecto library basically lets you write sql in elixir while abstracting away all the stuff op talking about.
you can simply assign a subquery to a variable and reuse it.
PS: a lot of you are saying use views. thats dangerous. In most cases, thats a nice way to end up with ballooning autovacume processes as your databasse gets bigger. Materialized views are fine as long as your use case doesn't need realtime accuracy.
create view max_products_sold as select sale_person_id, max(no_products_sold) AS no_products_sold from sales_department_details group by sale_person_id;
select sale_person_id, sale_person_name, no_products_sold, commission_percentage, sales_department from sales_department_details natural join max_products_sold;
I am using JooQ now at work to write SQL statements in a Java DSL. Java’s type system mostly works as intended and is helpful, particularly in conjunction with an IDE. Many kinds of metaprogramming are possible with JooQ.
Learn Datalog Today: http://www.learndatalogtoday.org/
Put your example on https://www.db-fiddle.com/ or http://sqlfiddle.com/ or some other sql fiddle then we can all experiment with it and come up with leaner solutions.
Just asking for mooar build in stuff just leads to bloated software.
Absolutely.
https://www.scattered-thoughts.net/writing/against-sql (found from HN a week or so ago)
Pine [1] and jq [2] are combinator query languages.
Every expression represents a filter of some kind.
In SQL (and in relational algebra), every expression represents a dataset.
That means composing SQL expressions is composing data, not composing operations on data.
Since there is a somewhat low, backwards-compatible barrier to entry to build a combinator language on top of SQL (Pine is an example), whether or not trading the increased learning curve of using combinators for better composability is a good idea can stand the test of time.
[1]: https://github.com/pine-lang/pine [2]: https://stedolan.github.io/jq/
Check out dbt (not dbt-cloud), great open-source SQL tool, macro system is included in the ever-growing set of features. It helps managing SQLs a lot.
Nicer formatting of OPs woes:
SELECT
sd1.sale_person_id,
sd1.sale_person_name,
sd1.no_products_sold,
sd1.commission_percentage,
sd1.sales_department
FROM
sales_department_details sd1
INNER JOIN (
SELECT
sale_person_id,
MAX(no_products_sold) AS no_products_sold
FROM sales_department_details
GROUP BY sale_person_id
) sd2
ON
sd1.sale_person_id = sd2.sale_person_id
AND sd1.no_products_sold = sd2.no_products_sold;What you described is already solved by window functions.
That’s because you are 1) writing naive SQL and 2) forcing naive SQL on an arbitrary data structure. You may use VIEWs, CTEs, GROUP BY CUBE/ROLLUP for 1) and rethink your structures for 2).
I think it would be neat if columns of a resultset could contain other resultsets (relvars?) as values. This would be the natural outcome of a join operation, and give greater flexibility on how you want to reduce those sub-relvars down. It also makes recursive queries more natural, yielding a "tree" of results. You could even build a reducer for a recursive query that concatenated rows to a string to serialize into whatever: json, XML, etc.
I'm not an expert but whenever I need to use SQL it seems to be complicated enough as I need at the time to do the job. When the job becomes more complicated, so does SQL.
I think it is a remarkably well designed language. I often get decent results for minimal effort. It isn't something I use day to day but more often month to month and I generally forget what I learned a couple of years ago.
For example, I wanted to clear down some records for a defunct client from our backup results database. I started by using SELECT to reliably find the customer (not as simple as it might sound). Then I construct an INNER JOIN (in this case most joins are the same) to link to the jobs. A SELECT COUNT(*) experiment looks about right. I snapshot the VM and change my experiment to a DELETE.
SQL is a bit strange but it lends itself to experimentation ... to results, way better than any other language I know.
Then you get into functions and views and the like. SQL has just enough complication for whatever job you have at hand and no more.
I recently ran up a query in Icinga2 (Director) against a MariaDB and it seemed to write itself. I get far fewer syntax errors in SQL than anything else that I try to abuse!
From https://bitbucket.org/djarvis/rxm/src/master/ :
root > people, # "root" keyword starts the document
person > person, # maps table context to a node
.age > @age, # @ maps a column to an attribute node
.first_name > name/first, # maps a column to a node
.last_name > .../last, # ... reuses the previous node's path
account.person_id +> person.person_id, # +> performs an INNER JOIN
account > account, # context is now "account" node
.id > @id, # account id attribute
^, # pop stack to previous table context
address > address, # switch context to "address" node
.*, # glob remaining columns
; # Denotes optional WHERE clause
Charles
Goldfarb
456
123 Query Lane
San Francisco
1) templating constructs or env vars? The programming languages can provide that too, but... prepared statements and variable-based substitution was a huge improvement to SQL code from programming languages (at least in java and ruby). Why wasn't that part of the SQL standard, why did it have to evolve organically? If it was in the specification, so many SQL injection vulns would have been avoided.
2) SQL statement syntax should start with the table specification so you can get autocompletion help when doing the columns and other expressions. The INSERT statement has it right, while SELECTs suck because the columns are specified before the WHERE clause, so there is no context. At least provide alternate syntaxes like SELECT FROM