in句サブクエリ内でunionをunion all

MySQL

(8.0.22)

drop table tab1;
drop table tab2;

create table tab1(col1 int, col2 int, col3 int);
create table tab2(col1 int, col2 int, col3 int);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);

drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i int;
set i = 1;
while i <= 100000 do
insert into tab1 values(i,floor(rand() * 1000)+1,floor(rand() * 1000)+1);
insert into tab2 values(i,floor(rand() * 1000)+1,floor(rand() * 1000)+1);
set i = i + 1;
end while;
end
//
delimiter ;

start transaction;
call proc1();
commit;

select count(*) from tab1;
select count(*) from tab2;

explain
select col1,col2
from tab1
where col2 in (
select col2 from tab2
union
select col3 from tab2
)
;

explain
select col1,col2
from tab1
where col2 in (
select col2 from tab2
union all
select col3 from tab2
)
;

Oracle

(19c)

drop table tab1 purge;
drop table tab2 purge;

create table tab1(col1 int, col2 int, col3 int);
create table tab2(col1 int, col2 int, col3 int);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);

declare
begin
for i in 1..100000 loop
insert into tab1 values(i,floor(dbms_random.value(1, 1001)),floor(dbms_random.value(1, 1001)) );
insert into tab2 values(i,floor(dbms_random.value(1, 1001)),floor(dbms_random.value(1, 1001)) );
commit;
end loop;
end;
/

select count(*) from tab1;
select count(*) from tab2;

explain plan for
select col1,col2
from tab1
where col2 in (
select col2 from tab2
union
select col3 from tab2
)
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

explain plan for
select col1,col2
from tab1
where col2 in (
select col2 from tab2
union all
select col3 from tab2
)
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

→実行計画相違あり
unionの場合、SORT UNIQUE
union allの場合、HASH UNIQUE
コストはunionのほうが大きい。実行時間はほぼ同じ。

PostgreSQL

(13)

drop table tab1;
drop table tab2;

create table tab1(col1 int, col2 int,col3 int);
create table tab2(col1 int, col2 int,col3 int);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);

do $$
declare
i int;
begin
i = 1;
while i <= 100000 loop
insert into tab1 values(i,floor(random() * 1000)+1,floor(random() * 1000)+1);
insert into tab2 values(i,floor(random() * 1000)+1,floor(random() * 1000)+1);
i = i + 1;
end loop;
end
$$
;

select count(*) from tab1;
select count(*) from tab2;

explain analyze
select col1,col2
from tab1
where col2 in (
select col2 from tab2
union
select col3 from tab2
)
;

explain analyze
select col1,col2
from tab1
where col2 in (
select col2 from tab2
union all
select col3 from tab2
)
;

→実行計画相違あり
unionの場合、Sort → Unique
union allの場合、HashAggregate
コストや実行時間はunionのほうが大きい。

SQL Server

(2019)

drop table tab1;
drop table tab2;

create table tab1(col1 int not null, col2 int, col3 int);
create table tab2(col1 int not null, col2 int, col3 int);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);

set nocount on
declare @i int;
set @i = 1;

while (@i <= 100000)
begin
insert into tab1 values(@i,floor(rand() * 1000)+1,floor(rand() * 1000)+1);
insert into tab2 values(@i,floor(rand() * 1000)+1,floor(rand() * 1000)+1);
set @i = @i + 1;
end

select count(*) from tab1;
select count(*) from tab2;

select col1,col2
from tab1
where col2 in (
select col2 from tab2
union
select col3 from tab2
)
;

select col1,col2
from tab1
where col2 in (
select col2 from tab2
union all
select col3 from tab2
)
;

→実行計画相違なし
SQL Serverの場合は、in句内のunion allをunionと読み替えをしている模様