CODEWITHSUNDEEP
sqldatabaseoracle-database
Mr. Adobo
Mr. Adobo

Reputation: 835

Using Subquery Table in FROM on another subquery

Basically I want to do something like this.

SELECT * 
FROM TABLE, (SELECT * FROM TABLE2) SUBQ
WHERE TABLE.SOMETHING IN (SELECT DISTINCT COL FROM SUBQ)

I want to know if it's even possible to call that subquery table in my FROM on another subquery, and if it is, how to do it.

This is a simplified example (my queries are too long), so I'm not looking into another way of rewriting it that doesn't use the subquery in the FROM.

Upvotes: 0

Views: 100

Answers (1)

Mihai Stancu
Mihai Stancu

Reputation: 16127

What you're looking for is called common table expression, it uses the WITH SQL keyword.

Of course if this can be refactored to use a temporary table or an (indexed) view it would be much better / more performant and it would serve multiple execution streams (if they all need access to the same data).

If different execution streams do not need to have access to the same data then creating many many temporary tables or views (while also trying to avoid name collisions) is less optimal and the CTE is more useful.

Upvotes: 1

Related Questions