CODEWITHSUNDEEP
mysqlsqlpostgresql
walkingFox
walkingFox

Reputation: 53

Mysql support this kind of "with" sql?

In postgres, I use this kind of sql often.

with list (id, name) as ( 
    values 
    (1004007, 'aaa'), 
    (1002147, 'bbb'), 
    (1004493, 'ccc'), 
    (1007978, 'ddd'), 
    (1005218, 'eee'), 
    (1005507, 'fff') 
)
select * from list;

Dose Mysql support that kind of sql ?

Upvotes: 3

Views: 41

Answers (2)

walkingFox
walkingFox

Reputation: 53

Later 8.0 in mysql , we can use it like bellow

with 
list (a, b, c) as (VALUES 
ROW('1',-2,3), 
ROW('5',7,9), 
ROW('4',6,8)
)
select * from list;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269673

You can use select:

with list (id, name) as ( 
      select 1004007, 'aaa' union all 
      select 1002147, 'bbb' union all 
      select 1004493, 'ccc' union all
      select 1007978, 'ddd' union all
      select 1005218, 'eee' union all
      select 1005507, 'fff'
     )
select l.*
from list l;

Here is a db<>fiddle. Note: This also works in Postgres.

Pre 8.0 versions of MySQL do not support with. You can create a derived table:

select list.*
from (select 1004007, 'aaa' union all 
      select 1002147, 'bbb' union all 
      select 1004493, 'ccc' union all
      select 1007978, 'ddd' union all
      select 1005218, 'eee' union all
      select 1005507, 'fff'
     ) list;

This can then be used just a like a table reference.

Upvotes: 2

Related Questions