New Pipe Syntax in SQL

created, $=dv.current().file.ctime & modified, =this.modified tags:sqldatabasecs

FROM clause leading. LINQ in .NET has this.

SQLite creator on not rushing to include experimental syntax

My goal is to keep SQLite relevant and viable through the year 2050. That’s a long time from now. If I knew that standard SQL was not going to change any between now and then, I’d go ahead and make non-standard extensions that allowed for FROM-clause-first queries, as that seems like a useful extension. The problem is that standard SQL will not remain static. Probably some future version of “standard SQL” will support some kind of FROM-clause-first query format. I need to ensure that whatever SQLite supports will be compatible with the standard, whenever it drops. And the only way to do that is to support nothing until after the standard appears.

SQL query

SELECT component_id, COUNT(*)
FROM ticketing_system_table
WHERE
	assignee_user.email = 'username@email.com'
	AND status in ('NEW')
GROUP BY component_id
ORDER BY component_id DESC;

As Pipe query syntax

FROM ticketing_system_table
|> WHERE 
	assignee_user.email = 'username@email.com'
	AND status in ('NEW')
|> AGGREGATE COUNT(*)
	GROUP AND ORDER BY component_id DESC;

In SQL the standard clauses occur in one rigidly defined order. Expressing anything else requires subqueries or other workaround. With pipe operator syntax, operations can be composed arbitrarily, in any order.

Standard SQL cannot express multi-level aggregations without subqueries, resulting in complex “inside out” data flow.

There’s a mismatch of syntax order with semantic order in traditional SQL. There’s need for subqueries in these cases

• filtering anywhere other than three supported locations
• aggregation two or more times
• projecting computed expressions before the final select, so they can be referenced multiple times by name, in later select, where and joins.

Inside out data flow: With the inline from clause data flow starts in the middle with the most deeply nested table reference. Then logic builds outwards, applying operations both above and below, while traversing outwards through nested subqueries.