CODEWITHSUNDEEP
sql
snakile
snakile

Reputation: 54521

Using a variable with pure SQL

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?

Upvotes: 1

Views: 707

Answers (3)

Ben
Ben

Reputation: 52853

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.

Upvotes: 1

Gpak
Gpak

Reputation: 3390

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

Upvotes: 0

gdoron
gdoron

Reputation: 150253

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

Upvotes: 3

Related Questions