select句別名をgroup byやorder byで使用できるか確認

OracleとSQL Serverはgroup byで別名を使用できない

• MySQL

(8.0.29)

drop table tab1 ;
create table tab1(col1 int,col2 int);
insert into tab1 values(1,1);
insert into tab1 values(1,2);
insert into tab1 values(2,1);
select * from tab1;

select col1,col2,col1+col2 col3 from tab1;
select col1,col2,col1+col2 col3 from tab1 order by col3;
select col1+col2 col3,count(*) from tab1 group by col3;

order by -> OK
group by -> OK

• Oracle

(19c)

drop table tab1 purge;
create table tab1(col1 int,col2 int);
insert into tab1 values(1,1);
insert into tab1 values(1,2);
insert into tab1 values(2,1);
commit;
select * from tab1;

select col1,col2,col1+col2 col3 from tab1;
select col1,col2,col1+col2 col3 from tab1 order by col3;
select col1+col2 col3,count(*) from tab1 group by col3;

order by -> OK
group by -> ORA-00904: "COL3": 無効な識別子です。

(23c)
order by -> OK
group by -> OK

• PostgreSQL

(14)

drop table tab1 ;
create table tab1(col1 int,col2 int);
insert into tab1 values(1,1);
insert into tab1 values(1,2);
insert into tab1 values(2,1);
select * from tab1;

select col1,col2,col1+col2 col3 from tab1;
select col1,col2,col1+col2 col3 from tab1 order by col3;
select col1+col2 col3,count(*) from tab1 group by col3;

order by -> OK
group by -> OK

• SQL Server

(2019)

drop table tab1 ;
create table tab1(col1 int,col2 int);
insert into tab1 values(1,1);
insert into tab1 values(1,2);
insert into tab1 values(2,1);
select * from tab1;

select col1,col2,col1+col2 col3 from tab1;
select col1,col2,col1+col2 col3 from tab1 order by col3;
select col1+col2 col3,count(*) from tab1 group by col3;

order by -> OK
group by -> 列名 'col3' が無効です。