[siganakis]

I think that the biggest problem with SQL is more around the actual syntax of the language and how verbose it feels to write complicated queries.

I would prefer a syntax layer that can be compiled / transformed back to SQL but that does basic things like having a query start with the tables, then joins, then groupings then the final projection.

Also a less cumbersome way to use the "WITH" statement to form named sub-queries.

Perhaps something like:

    SELECT 
        COUNT(*) as columns,
        column_type,
        table_name
    FROM (
        SELECT  c.id, 
                c.type AS column_type,
                t.name AS table_name
        FROM tables t
        INNER JOIN columns c
        ON t.id = c.table_id
        WHERE t.system=false;
    ) a
    HAVING COUNT(*) > 1
    ORDER BY columns DESC

Being re-written as:

    # Use ":="  to replace WITH for named ephermal views
    # Replace "WHERE" with "?", "SELECT" with "|>" at the end
    non_system := tables 
        ? system=false 
        |> name:table_name, is:table_id

    # Replace INNER JOIN with "*="
    non_system_columns := non_system.table_id *= columns.table_id
        |> c.id, c.type:column_type

    # GROUP BY columns are automatically generated by non-aggregated columns
    column_types := non_system_columns
        |> COUNT(*):columns DESC, column_type, table_name

So the final query may look like:

    non_system := tables ? system=false 
        |> name:table_name, is:table_id
    non_system_columns := non_system.table_id *= columns.table_id
        |> c.id, c.type:column_type
    
    non_system_columns 
        |> COUNT(*):columns DESC, column_type, table_name

Any thoughts on this?

[jamii]

Take a look at http://www.infoq.com/presentations/theory-language-integrate... . Wadler's library supports the LINQ interface but does a better job of rewriting the final program before submitting it to the database. He demonstrates using first-class queries to create denormalised views of the database that are compiled away when used in other queries.

[cordite]

It's also really cool when you can compose queries. A thing in the Haskell world called esqueleto does this

https://hackage.haskell.org/package/esqueleto

[siganakis]

Yeah, I think composability is one of the biggest things missing from SQL.

The issue is that composability is often tied to actually moving data around in the database which has terrible performance. That is, you can compose a query of multiple queries that dump partial data sets into temp tables.

Views get you part of the way there, but they are designed to be long lived and are visible to all database users until they are dropped. This means its dangerous to change them or clean them up, as its not always clear who they are being used by.

Ephermal or temporary views that are session/connection based, or even loadable as modules would be useful to me.

[siddboots]

I completely agree that this is one of the major lackings of SQL.

Most databases offer the WITH syntax, but, bizarrely, the SQL standard specifies that the WITH block should materialised separately, which prevents it from being used as a device for abstraction.

Oracle is the only database I'm aware of that allows the WITH block, but doesn't impose the optimiser fence.

[ZenoArrow]

"Ephermal or temporary views that are session/connection based, or even loadable as modules would be useful to me."

You mean like temp tables in MSSQL? They come in various flavours, they're all pretty handy. The most flexible of these is the XML variable, but there are far simpler options than that.

[collyw]

I find the SQL version a lot easier to read. I assume the other way would be less effort to write. (Though should you not have a GROUP BY in there for the count?)

In contrast to most languages, I do find SQL is generally easier to understand when reading it, rather than writing it.