3 ms·
All SQL databases have this feature, it's nothing specific to PostgreSQL. I don't like the "with" syntax though, I just do it all inline. select * from (
by socialist_coder 13y ago
All SQL databases have this feature, it's nothing specific to PostgreSQL.
I don't like the "with" syntax though, I just do it all inline.
select * from
(
select * from blah 1
) a,
(
select * from blah 2
) b
where a.x = b.x;
- adwf 13y agoThe advantage of CTEs is they have the explicit "WITH RECURSIVE" option. Whereas an aliased SELECT statement can't refer to itself as far as I know.
- socialist_coder 13y agoAhhh, just read about that. The equivalent of the Oracle "start with, connect by" clause (http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm#i2060615 http://docs.oracle.com/cd/B19306_01/server.102/b14200/querie...). Good to know.
- twic 13y agoRecursive CTEs are super amazing. One of the great underused features of SQL. Give me tall PostgreSQL and a WITH RECURSIVE to steer by, and i will happily take on any graph database. Not promising i'll win, mind.
- saosebastiao 13y agoFind me CTEs in mysql or sqlite. I dare you. And of course you can do it all inline. For transactional uses, that is probably best anyway. But there are clear use cases where inline queries are significantly less readable. Imagine subqueries nested 6 levels deep...CTEs allow you to flatten it such that you can linearly read the buildup of the query from top to bottom. It also allows the optimizer additional room for optimization, as it allows the optimizer to decide based on its analyze data whether the query is best run inline or by creating temp tables.
- socialist_coder 13y agoOk, another reason to never use mysql.
- yxhuvud 13y agoIt get really tedious and error prone when you start repeating the same subquery multiple times in a statement though.