Reputation: 835
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
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