I would love to see lisp-style (AST) macros applied to the relational model. Here's an example that might have an obvious solution (please share), but not off the top of my head. Find the id of every row having the minimum value per group by aggregation. I have to write a subquery to do this, but the transformation is quite regular and predictable. Where's my macro?

[0] https://www.postgresql.org/docs/11/functions-window.html

Edit: Just tested it, it works but I wouldn't call it pretty.

    create table testing(id int, grp int, other varchar2(255));
    
    insert into testing values (1, 1, '');
    insert into testing values (2, 1, '');
    insert into testing values (3, 1, '');
    insert into testing values (9, 2, '');
    insert into testing values (10, 2, '');
    
    
    with cte as (
      select grp, id, row_number() over (partition by grp order by id) as rn from testing
    )
    select grp, id from cte where rn = 1;

    select distinct on (grp) grp, id
    from testing
    order by grp, id;

When I did my testing I did it on Oracle since I have it available at work. For my non-work projects I use PostgreSQL and, for the most part, find that I can flip between the two database without too much trouble. Of course, this also means that I don't know about some PostgreSQL niceties like what you showed.

It's syntactic sugar, but it is a whole lot more readable in some cases than a window function. It's nice to not need a sub query or cte to express it.

For example, a phrase like row-number() over (partition by grp order by id) might return in many, many of your stored procedures, but there’s no way to only write it once in SQL.

The same thing effectively applies to that common table expression. There, you can create a view, and just write

  select grp, id from grouped_tests where rn = 1;

   select distinct grp, first_value(id) over (partition by grp order by id) as rn from testing;

Building on my previous example:

    insert into testing values (8, 2, 'stuff');   
    select distinct grp, first_value(id) over (partition by grp order by id) as rn, other from testing;

This would work properly within the CTE, but then you're joining to the CTE instead of just querying it. There are many ways to solve this problem.

My ideal solution would be something like this that gets rid of the CTE:

    select grp, id, row_number() over (partition by grp order by id) as rn, other from testing where rn = 1

https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...

https://www.postgresql.org/docs/9.1/tutorial-window.html

Or are you saying that efficient editing makes people forget about writing clean, concise code? I don't find that to be the case.

select top(1) with ties

id

from

table

order by

row_number() over (order by value asc)

In a meeting but is that the gist?

> select groupbycol, array_agg(id) from table group by groupbycol having avg(minavgcol) > T

Kusto/Azure Data Explorer [1] is a really interesting new query language, focused on analytics. It fixes a lot of the things that are awkward about SQL. A simple example:

  StormEvents 
  | where StartTime >= datetime(2007-11-01) and StartTime < datetime(2007-12-01)
  | where State == "FLORIDA"  
  | count 

Lisps are unreasonably effective because they have lambda calculus behind them. The Hindley-Milner type system and linear types, both mathematically sound, are a large part of Rust's effectiveness and success, in my eyes.

I still would like that SQL read a bit more set-theoretic, if less English-like. It would make it less quirky and more composable.

I don't really buy that. CL remains unreasonably effective even when used by somebody using it in an imperative manner. And I think the majority of CL code out there is truly multi-paradigm, not functional. I think most of the unreasonable effectiveness comes from the relationship between s-expressions and cons.

Take the condition system: it first struck me as weird and unnecessarily complicated until I encountered the use-case that conditions exactly solve.

I think that comes from engineers using the language being the same engineers who can add features to the language. It's an extreme form of dog-fooding.

It's like a beautiful mathematical core that grew a lot of scar-tissue to handle wordly edge cases.

EDIT: because I used Slack formatting, not Hackernews formatting.

I suppose that s-expressions and the ways to transform them when they represent a function application (that is, basically always) critically depend on lambda calculus. I can be wrong, though.

However, the two together make a really magical combination because each one is a sort of local maximum in its respective design space.

I wish that it didn't introduce an incomprehensible ternary logic for null handling. SQL with algebraic data types would be amazing.

Not only would sum types be extremely helpful but it would also be awesome to have a standard for product types. As it stands, some implementations have them but they are clunky, and others you have to resort to just using multiple columns, which is pretty bad for composability, bug-ridden, and leads to behavior that is almost like Hungarian notation in order to maintain unique column naming. For example:

    create table foo(
      start zoneddatetime,
      end zoneddatetime
      ...
    );

    create table foo(
      start_time timestamp,
      start_tz timezone,
      end_time timestamp,
      end_tz timezone
      ...
    );

In summary I think a QL should produce a table from a database when provided with 1) the set of field names desired, and 2) the set of constraints on the desired rows. Anything that goes beyond this is in need of justification.

It is for this impedance mismatch (of understanding) that I have an aversion against SQL (therefore I am not well-versed). I basically see SQL as an exercise in complexity, resulting from the constraints prevalent in the seventies. Please prove me wrong!

First of all, it absolutely can and does infer joins if you use a natural join.[1]

However, a lot of the time you don't want the database to infer the joins because how you join two tables depends a lot on the specifics of your query. Not every join is along a clean primary key/foreign key axis. Sometimes you have datasets from different sources. Othertimes, you have more novel relationships between your tables. The whole point of the relational model is to give the end user a lot of flexibility in querying the database which means you need to specify the joins a lot of the time.

1. https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnaturalj...

Some 90%+ of the times, you want exactly the join the database would infer, but natural join is basically useless, so you must repeat the basic join all the way every time.

SQL did it backwards. The simple join keyword should mean natural join over a foreign key. A cross product should be created with the `product` keyword or something similar.

That's because the structure doesn't always has the feature which you need this specific time to rely upon. And that's a feature, not a bug, which allows having flexibility between data structure and data queries.

If you have a relation, DB engine maintains that, ensuring integrity defined as such. However, you are free to query non-integral (in this sense) references, which you're able to describe in queries. If you won't be able to specify joins, you'd have to always express relations in the structure. Since queries in SQL can be arbitrary, to reach parity with flexibility you'd have to have all possible relations. Then you have the problem of choosing what you need for this moment. In other words, some relations are not known before query time.

The answer is that there are different kind of joins (you may object that this is part of "set of constraints on the desired rows", however, then you are just specifying joins explicitly), and that generally speaking this is a non-unique property, i.e. you could usually infer several joins that represent dramatically different result sets that still have the same tuple archetype.

I should point out that these issues aren't brought on by the added expressiveness of SQL, but rather, these issues are already present in basic relational algebra; I'd argue they're fairly fundamental.

It's a declarative language that in theory abstracts you away from the underlying execution environment, it's up to that engine to work out how to optimally execute the query, you as a programmer should focus on the data you require.

In the frame of reference of how the queries get executed, yes it's high level. But this is the same thing that could be said for, say, C - it's high level in the frame of reference of CPU instructions, low level in the frame of reference of memory management.

Data abstractions in SQL are accomplished with views:

https://www.postgresql.org/docs/current/sql-createview.html

Model your most common joins once into a view, then use the view instead of repeating the joins everywhere.

> In summary I think a QL should produce a table from a database

In SQL we call these projections, which is just whatever columns are being returned from the amalgamation of tables you used in your query. This could include aggregations or window functions.

Things are possible in SQL that are simply more difficult with anything else, and my time and my companies are more important than figuring out the perfect approach to every problem. SQL makes 80% of problems trivial, and the remaining 20% I can easily solve somewhere else (like Elasticsearch or sqlite or even custom serdes to disk).

http://www.postgresqltutorial.com/postgresql-natural-join/

2. Not all joins can be inferred like this:

` FROM People AS p JOIN Age_Groups AS ag ON ag.min_age <= p.age AND p.age < ag.max_age`

https://blog.jooq.org/2018/02/20/type-safe-implicit-join-thr...

With some more UX work around foreign key naming/declaration and careful attention from the developer (maybe helped by the IDE) to the caveats mentioned, this impedance mismatched could be reduced.

It would indeed be nice to do that automatically based on foreign keys; I suppose one could write a PostgreSQL extension for that (or maybe one exists?).

Of course you still need the ability to perform joins that are not implied by the database structure.

Indeed, this should be available in the SQL language as well. I can't think of a performance problem, having implemented this in jOOQ. There's always a very obvious mapping between the notation and inner/left joins, which are reusable for common paths and subpaths.

At least for navigating to-one relationships. To-many relationships raise more questions.

Some RDBMS support a T1 JOIN T2 ON KEY syntax, which is nice as well, but the path notation is much more powerful:

    SELECT 
      c.first_name,
      c.last_name
      c.address.street, 
      c.address.zip, 
      c.address.city
    FROM customer c

    SELECT 
      c.first_name,
      c.last_name
      a.street, 
      a.zip, 
      a.city
    FROM customer c
    JOIN address a USING (address_id)

https://www.postgresql.org/docs/current/sql-createview.html

It not.

    SELECT * FROM "document", "line";

A CROSS JOIN. ie: a Cartesian product. That is not what most of the time you want.

Why cartesian? Because this is the most fundamental join of all. Is like "fold" in functional languages (ie: You implement map, filter, etc on top of fold).

Consider the JOIN clause a extra help. Not was always present:

https://stackoverflow.com/questions/25161747/how-to-implemen...

When using old fox pro, JOIN was not available.

----

Ok, but why not infer that if I join FROM "document", "line", it use the PKs??? That could be nice, but SQL is kind of EXPLICIT with not many implicit exceptions. And you don't wanna that behavior all the time.

This is in contrast to OO. Where you can only use a kind of "join", but lack the capabilities to do much more without extra work.

In fact, put JOINs not bother much. It fit well. Other aspect of the syntax of SQL are more annoying.

----

> If the answer is performance... SQL is not high-level

So, if you are on python, and use a generator to pipe several inputs instead of materialize each step in a big list and pass to the other, because I'm aware that is not memory efficient, suddenly is not high-level?

> I basically see SQL as an exercise in complexity, resulting from the constraints prevalent in the seventies. Please prove me wrong!

I say is the TOTAL OPPOSITE. Sql is TOO SIMPLE!

You are correct about it to be a product of the era. More exactly, the original plan was use SQL for the END USER to interface with the database. To write "off-shots" queries, not to be a proper programming interface.

That is why, SELECT come first, instead of Last. Also, why SQL can't be composed, you don't have variables, can't express loops or recursive functions, and many many other really annoying stuff. SQL is alike CSS. Too simple. Not even lisp-like capable.

P.D: Of course, exist a lot of extensions to cover for it (including WITH clauses), but the fact is, sql was not made for be a proper database language and it show.

Why is this relevant? Because SQL with a bit more power could be abstract and allowed to fix any small shortcoming more naturally.

----

I'm in the hunt to build a relational language, not tied to a particular RDBMS, with full programming power. Not follow the SQL syntax, but more the LINQ style:

http://tablam.org

Still in research/alpha, and looking for help!

https://stackoverflow.com/questions/2117651/comparing-sql-an...

To clarify, I think Prolog is a lousy data storage language but a fantastic query language. What I've done in the past is used Prolog to query a Common Lisp object-oriented database (after modifying the Prolog to produce composable lazy streams, which is not usually included in the Prolog box but is very handy for database querying.) What I want to try next is the above with standard PostgresQL as the backend repository. That will make possible an apples-to-apples SQL comparison possible.

Could you share more details about this? I'd be very interested in exploring something like this.

Actually, Datalog is a subset of Prolog, and could be used on any kind of data, not necessarily database. Nowadays, it's implemented in different languages, just like the Clojure version.

So like SQL, Datalog it's a declarative language and implemented on different kinds of platforms but IMO it's much more reasonable than SQL since SQL is much worse on composition.

Most people ignore the fact that Recursive Common Table Expression (CTE) makes SQL Turing complete

https://lagunita.stanford.edu/courses/DB/2014/SelfPaced/abou...

After this I would have a look at the postgres documentation. There's so many great examples between there and stack overflow.

I really hate knowing how easy it is to do some queries and struggling to use the Django ORM to achieve what I could write in minutes - but for maintainability I hate dropping down to SQL in that context.

For a fun advanced tutorial, have a go at pivot tables with crosstab using the docs. I've used it for analytics queries to chart categories as headings, with complex aggregations etc.

https://www.postgresql.org/docs/9.1/tablefunc.html

Window functions are another classic

https://www.postgresql.org/docs/9.1/tutorial-window.html

In general working with SQL and gradually solving the problems you have learning as you go is excellent. If you want faster than that there probably are courses but I really do rate their docs. Postgres is great.

It includes short videos, quizzes, articles, and exercises.