4 ms·
Yeah I'm not sold. One example from this post that struck me was the author wanted to embed a select in a table expression. I'm not a fan of this at all. I don'
by cletus 8y ago
Yeah I'm not sold. One example from this post that struck me was the author wanted to embed a select in a table expression. I'm not a fan of this at all. I don't want it not to be clear if a given expression list will explode in values or not.
I like the fact that SQL has a solid foundation in relational algebra. I see no such foundation for the alternative.
I do like what LINQ did here (being SQLish), which was to put the FROM clauses first. Some SQL variants have WITH clauses that are quite convenient but you end up with:
WITH (...) AS a,
(...) AS b
SELECT
a.a1,
b.b1
FROM a
JOIN b
ON a.a = b.b
Common alternative:
SELECT
a.a1,
b.b1
FROM (...) a
JOIN (...) b
ON a.a = b.b
whereas I'd prefer:
FROM (...) a
JOIN (...) b
ON a.a = b.b
SELECT
a.a1,
b.b1
- dragonwriter 8y ago> Some SQL variants have WITH clauses “Some variants”, including standard SQL since SQL:1999.
- MarkusWinand 7y agoWITH is supported by all major SQL brands in the meanwhile. https://modern-sql.com/feature/with#compatibility https://modern-sql.com/feature/with#compatibility
- latch 7y agoDoes the spec have any execution requirements re WITH? Pretty sure `WITH` in postgresql is gated and thus can have considerable performance implication vs a nested query (or none at all depending on the query).
- trollied 7y agoExecution is down to the RDBMS. Postgres is changing in the next release, BTW: https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
- mathh 7y agoImpossibility of With clause inlining is indeed one of the major limitation of Postgres, compared to Oracle. We we still have to wait for optimizer hints