Oracle SQL: multiple use of a previous result in a statement

Question

I would like to achieve something like this:

SELECT col1,col2-(SELECT foo FROM table1) 
FROM table2 
WHERE col2>(SELECT foo FROM table1)

Without SELECTing foo twice.

Can I do that with Oracle SQL? Would it be more efficient than SELECTing twice (or multiple times)?

Is there really only a single row in table1? If not, what is the relationship between the two tables? — user359040
– user359040, Commented Jan 12, 2012 at 12:05

Thilo · Accepted Answer · 2012-01-12 12:06:35Z

6

How about

WITH
  foo      AS  select foo from table1
SELECT
    col1, col2 - foo.foo
FROM
    table2, foo
WHERE
   col2 > foo.foo

answered Jan 12, 2012 at 12:06

Thilo

264k107 gold badges527 silver badges674 bronze badges

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

2 Comments

APC Over a year ago

+1 The WITH clause is expressly provided for the purpose of re-using a sub-query in more than one place.

Thilo Over a year ago

Note that in this case it is only actually used in once place (in the cartesian join). However, that may have been because the example has been simplified, and WITH is a good thing to know about.

Daniel Hilgarth · Accepted Answer · 2012-01-12 11:54:51Z

4

You could write it like this:

SELECT
    s.col1, s.col2 - s.foo
FROM
(
    SELECT col1, col2, (SELECT foo FROM table1) as foo
    FROM table2
) s
WHERE s.col2 > s.foo

answered Jan 12, 2012 at 11:54

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

Comments

Justin Cave · Accepted Answer · 2012-01-12 11:56:50Z

1

Assuming that your subquery returns a single row (otherwise, both uses of the subquery would return errors), simply do a Cartesian join

SELECT a.col1, a.col2 - b.foo
  FROM table2 a,
       (SELECT foo FROM table1) b
 WHERE a.col2 > b.foo

answered Jan 12, 2012 at 11:56

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Comments

Florin Ghita · Accepted Answer · 2012-01-12 12:07:00Z

1

SELECT col1, col2 - foo
  FROM table2, table1
 WHERE col2 > foo

But only if table1 has 1 row.

answered Jan 12, 2012 at 12:07

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

Collectives™ on Stack Overflow

Oracle SQL: multiple use of a previous result in a statement

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related