Welcome to TDBSoverflow, Our class's own StackOverflow. Our rules:
  1. Use only meaningful and self-explanatory titles
  2. Tag your questions with meaningful keywords
  3. Use upvotes and downvotes to rate the answers
  4. When you receive a satisfying answer - Click the "V" button
Remember: you may get up to 5 bonus points to your final grade!

Duplicate sub query

+8 votes
75 views

In some of the questions, I found it convenient to use a sub query more than once.

For example something like:

SELECT...

From (SELECT .... FROM ... WHERE ...)

WHERE ... AND x = (SELECT ... FROM (SELECT .... FROM ... WHERE ...) WHERE ...)

Where the sub queries in bold are identical.

Is this legit, or should I try to avoid it?

(I guess a smart optimizer would only run the sub query once anyway)

* Using 'AS' and then referring that name did not work

Thanks!

asked Nov 11, 2017 by Assaf (31,090 points)

1 Answer

+1 vote
 
Best answer
Legit.

Reuse a table from a subquery will not work, it's like local variables in programming languages.
answered Nov 12, 2017 by Amit (27,910 points)
selected Nov 12, 2017 by Assaf
Thanks!

Is there any way to avoid writing it twice?
(something like a macro maybe?)
...