Using a variable with pure SQL

Question

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?

@a_horse_with_no_name. I guess making `SQL` looks like every other programming language which have variables... — gdoron, May 06 '12 at 13:18

score 3 · Accepted Answer · answered May 06 '12 at 12:18

3

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

answered May 06 '12 at 12:18

gdoron

147,333
58
291
367

score 1 · Answer 2 · edited May 23 '17 at 11:48

1

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.

edited May 23 '17 at 11:48

Community

1
1

answered May 06 '12 at 12:29

Ben

51,770
36
127
149

In this case the CTE can easily be replaced by a derived table ("inline view"). Just replace the `retrieved_name` with the select from the with clause. – May 06 '12 at 12:34
@a_horse_with_no_name, of course! I'm assuming the actual query is more complicated. – Ben May 06 '12 at 12:37

score 0 · Answer 3 · answered May 06 '12 at 12:25

0

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

answered May 06 '12 at 12:25

Gpak

3,342
2
21
19

1

No you can not. (Not just because of the unmatched `)`) – gdoron May 06 '12 at 12:29

Using a variable with pure SQL

3 Answers3