I want to rewrite the following query by assigning a "variable name" to the nested query.
select lastname
from table2
where firstname = (select name from table1 where val="whatever")
I want the query to look something like this:
( select name
from table1
where val = "whatever") as retrieved_name;
select lastname
from table2
where firstname = retrieved_name
Is it possible in pure SQL?
Is it possible in pure SQL?
No.
You don't have variables with "pure SQL". You can have variables with Oralce-PL\SQL and etc'.
Sort of, if you're willing to use a join instead of a sub-select and you're not using MySQL you can use a CTE (Common Table Expression):
with retrieved_name as (
select name
from table1
where val = "whatever"
)
select lastname
from table2 t2
join retrieved_name t1
on t2.firstname = t1.name
As a_horse_with_no_name pointed out this is identical to:
select lastname
from table2 t2
join ( select name
from table1
where val = "whatever" ) t1
on t2.firstname = t1.name
I actually prefer the inline view of the second example rather than a CTE unless the query is ridiculous but it's just a personal preference.
You can do something like this instead
select name as retrieved_name from table1 where val="whatever");
select lastname from table2 where firstname=retrieved_name
))
SQLlooks like every other programming language which have variables...