CODEWITHSUNDEEP
sqloracle-database
edjm
edjm

Reputation: 5472

What is the proper term or style called for a query of query?

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.

Upvotes: 1

Views: 179

Answers (3)

void
void

Reputation: 7880

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 ....

Upvotes: 1

Non Plus Ultra
Non Plus Ultra

Reputation: 906

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.

Upvotes: 1

Rene
Rene

Reputation: 10541

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

Upvotes: 2

Related Questions