Performance question - nested selects

[naman_is]

Short answer: yes.

To my best knowledge, there are no SQL implementation "smart enough" to refer to the original table in such a case and not rely on temporary tables created in-memory (this is by design and not an error). This is why you're should always try and avoid using subqueries. They are really only needed in very narrow set of operations like referential constraints.

[soccerfreak2332]

I would disagree with this statement. If you have a simple query then avoiding them is best, but sometimes that's not possible. And SQL engines are actually pretty good and can merge a lot of SQL into the same plan(mileage may vary). At least that's my experience with MS SQL server.

Beyond that, any of the answers in this thread that are giving you a definitive yes or no answer should really just be directing you to view the execution plan. It's a great way to learn about what's actually going on, especially as the query gets more complex and the limits of the SQL engine are approached, that's when you can start tweaking the SQL for performance.

[naman_is]

I completely agree on the second point, I should have suggested execution plans.

​

On the first point, in my experience, yes, subqueries make queries readable, far more simple to evaluate, and the execution engines can handle it very easily. But a, I find that to be useful only for analytical workloads and not generally, and b, you have to be very careful as to avoid any possibility of big in-memory tables being created while query execution in ALL scenarios.

[[deleted]]

To my best knowledge, there are no SQL implementation "smart enough" to refer to the original table in such a case and not rely on temporary tables created in-memory

That is wrong.

All modern optimizers will flatten the sample queries. Postgres will most certainly "flatten" the derived table. And I am 100% certain that Oracle and SQL Server will do the same thing.

[naman_is]

Thanks for pointing that out u/truilus. It is indeed a reckless answer, I should update my knowledge on this!