Using Subquery Table in FROM on another subquery

Question

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.

Can you provide some sample input and results data? Your query, as written, seems unlikely (you are filtering the left table by values in the right, but then keeping all rows in the right table even if they don't match). — Gordon Linoff
– Gordon Linoff, Commented May 7, 2013 at 16:08

Mihai Stancu · Accepted Answer · 2013-05-07 16:20:35Z

1

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.

edited May 7, 2013 at 16:20

answered May 7, 2013 at 16:03

Mihai Stancu

16.2k2 gold badges35 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mr. Adobo Over a year ago

This does seem to be what I'm looking for. I'll check it out.

Mihai Stancu Over a year ago

In some RDBMSs you can rely on the fact that the database has a query cache. Even though the query string is verbosely repeated many times it's results will be computed only once and then retrieved from cache (this is the case for MySQL for example). It's still ugly and repetitive but it works "just as fast".

John Woo Over a year ago

@MihaiStancu "..I down voted because.." -- actually I don't care if you do. You don't need to say it. Citing reason is already enough :) Anyway, I think you are right. Rereading the question again, ".if it's even possible to call that subquery table in my FROM on another subquery" -- it's recursive. WITH is the key. I deleted my answer :)

Collectives™ on Stack Overflow

Using Subquery Table in FROM on another subquery

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related