1

Early last year I was on a project using Oracle DB and was introduced to a new querying format where you could query the results of the previous query. It was only for a few weeks we were helping on the project so so I don't recall exactly how things were written. But, it was something like the outline below. Note all of the query I believe was written in a stored procedure and in just 1 procedure. Forgive me for the rude formatting but I just cannot recall how things were just that I found it awesome the ability to do the query of queries and not have all the nested selects in one statement.

e.g. SP: X

select firstName from users where active = true;

select authors from books where authorFirstName in (previous select);

Any guidance on what this style of querying is called that would help me research this would be greatly appreciated as I would like to learn more on it and follow the format more.

Ben
  • 51,770
  • 36
  • 127
  • 149
edjm
  • 4,830
  • 7
  • 36
  • 65
  • I don't think this exists... you could do it if you put the first result-set into an array of some description, otherwise I don't know. – Ben Mar 30 '15 at 12:37
  • 2
    Are looking for a common table expression? `with first_query as (select firstName from users where active = true) select authors from books where authorFirstName in (select firstname from first_query)` –  Mar 30 '15 at 12:48
  • I think I may have found the topic. [link](http://oracle-base.com/articles/misc/with-clause.php)http://oracle-base.com/articles/misc/with-clause.php. This would make it so we don't need to redefine the same subquery multiple times. Instead we just use the query name defined in the WITH clause. I'm not sure if this is only within Oracle or if it is supported by other DB engines. I'm looking into this for postgresql. Example from the page: – edjm Mar 30 '15 at 12:55
  • WITH
    dept_costs AS ( SELECT dname, SUM(sal) dept_total
    FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY dname), avg_cost AS ( SELECT SUM(dept_total)/COUNT(*) avg FROM dept_costs) SELECT * FROM dept_costs WHERE dept_total > (SELECT avg FROM avg_cost) ORDER BY dname;     – edjm Mar 30 '15 at 12:57
  • 1
    Yes you can use it in PostgreSQL, but note that a CTE in PostgreSQL is, by design, an "optimiser fence", so the query in the CTE runs without being transformed into the queries that reference it. Also you can run DML statements within PostgreSQL CTEs so you can (for example) insert rows into a table and return the assigned primary key values in a CTE, then use those PK values in a subsequent CTE that runs an update and returns other calculated values, then use those values in an insert statement in the main query. Not really a "purists" approach, but extremely useful. – David Aldridge Mar 30 '15 at 13:01
  • Going nuts here. I'm not able to format my code properly on this forum, can someone repost my code so it is formatted correctly? – edjm Mar 30 '15 at 13:04
  • Leave four spaces at the front of each line – David Aldridge Mar 30 '15 at 13:26
  • Thanks @DavidAldridge [link](http://stackoverflow.com/editing-help)http://stackoverflow.com/editing-help – edjm Mar 30 '15 at 13:46

3 Answers3

2

You can use the SQL with clause to give a sub-query a name and then use that name. Example here:

SQL WITH clause example

Community
  • 1
  • 1
Rene
  • 10,391
  • 5
  • 33
  • 46
1

the form you mentioned is subquery, which may be wrote with joins (depends on the query and subqueries):

select firstName from users where active = true;

select authors from books where authorFirstName in (previous select);

is equal to:

select books.authors 
from books 
join users on books.authorFirstName =users.firstName 
where users.active = true;

or equal to another subquery:

select authors 
from books 
where exists (select firstName 
             from users
             where 
             books.authorFirstName =users.firstName
             and active = true);

you can also use with statement:

with cte as (
select firstName from users where active = true)
select authors from books where authorFirstName in (select firtsname from cte);

and other forms ....

void
  • 7,760
  • 3
  • 25
  • 43
  • This doesn't seem to be what the OP is asking at all (plus there are now 2 deleted answers saying exactly this). – Ben Mar 30 '15 at 12:47
  • 1
    @Ben, it also won't be a good reason for down voting. (with my regards). – void Mar 30 '15 at 12:48
  • 3
    Not answering the question is a relatively good reason for downvoting. – Ben Mar 30 '15 at 12:50
  • Ok, I agree but these was per my understanding from question, I think question needs to be more clear isn't? if not can you pleas clarify me? – void Mar 30 '15 at 12:54
  • 1
    Odd that everybody provides the same answer. Probably the sanest response to his question. With my 18 years of Oracle knowledge this is the only answer I could come up with. How can you be so sure it's not what he means? – Non Plus Ultra Mar 30 '15 at 12:56
  • 1
    The "with" statement is what I was looking for. Thank you all for your attempts and contributions. Greatly appreciate this information. This applies to all of the posts on this thread. – edjm Mar 30 '15 at 13:17
1

This is called an subquery. The syntax usually is as follows:

select authors from books where authorFirstName in (select firstName from users where active = true);

Similar to that are the inline view:

select authors from books join  
(select firstName from users where active = true) users2 on users2.firstname = authors.authorfirstname;

and with clause

with (select firstName from users where active = true) as users2
select authors from books where authorsfirstname = users2.firstname;

All have different advantages and usages.

Non Plus Ultra
  • 867
  • 7
  • 17