Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

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;


Find 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.


Ok, another reason to never use mysql.


The 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.


Recursive 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.


Ahhh, just read about that. The equivalent of the Oracle "start with, connect by" clause (http://docs.oracle.com/cd/B19306_01/server.102/b14200/querie...). Good to know.


It get really tedious and error prone when you start repeating the same subquery multiple times in a statement though.




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: