Db2 Developer-C 11.1
Firebird 3.0
MariaDB 10.2 (unavailable)
MariaDB 10.3
MariaDB 10.4
MariaDB 10.5
MySQL 5.5
MySQL 5.6
MySQL 5.7
MySQL 8.0
Oracle 11g Release 2
Oracle 18c
Postgres 8.4 (unavailable)
Postgres 9.4 (unavailable)
Postgres 9.5
Postgres 9.6
Postgres 10
Postgres 11
Postgres 12
Postgres 13
SQLite 3.8 (unavailable)
SQLite 3.16 (unavailable)
SQLite 3.27
SQL Server 2012 (unavailable)
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2017 (Linux)
SQL Server 2019
SQL Server 2019 (Linux)
No sample DB
run
clear
markdown
compare
help
best fiddles
feedback
dbanow.uk
a leap of faith?
Fiddle Statistics
MySQL 8.0
SQLite 3.27
Oracle 18c
Firebird 3.0
MariaDB 10.4
MySQL 5.7
MySQL 5.6
MySQL 5.5
MariaDB 10.3
Db2 Developer-C 11.1
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2017 (Linux)
SQL Server 2019 (Linux)
SQL Server 2019
Postgres 12
Postgres 9.5
Postgres 11
Postgres 10
Postgres 9.6
MariaDB 10.5
Oracle 11g Release 2
Postgres 13
≡
clear
markdown
donate
comments/suggestions/bugs
a leap of faith?
bible365
diddy dollings
By using db<>fiddle, you agree to license everything you submit by
Creative Commons CC0
.
1579784 fiddles created (21355 in the last week).
create table a ( id int not null, x int ) ;
✓
hidden batch(es)
create index a_ix on a (id, x) ;
✓
hidden batch(es)
create table b ( id int not null, x int ) ;
✓
hidden batch(es)
create index b_ix on b (id, x) ;
✓
hidden batch(es)
insert into a (id, x) values (1, 1), (1, 1), (1, 1), (3, 3), (3, 3), (4, 4), (4, 4), (4, 4), (4, 4), (4, 4), (4, 4) ;
11 rows affected
hidden batch(es)
insert into b (id, x) values (1, 1), (3, 3), (3, 3), (3, 3), (4, 4), (4, 4) ;
6 rows affected
hidden batch(es)
select * from a except select * from b ;
id
x
…
hidden batch(es)
explain (analyze,buffers) select * from a except select * from b ;
QUERY PLAN
HashSetOp Except (cost=0.00..133.00 rows=226 width=12) (actual time=0.018..0.018 rows=0 loops=1)
Buffers: shared hit=2
-> Append (cost=0.00..110.40 rows=4520 width=12) (actual time=0.004..0.013 rows=17 loops=1)
Buffers: shared hit=2
-> Subquery Scan on "*SELECT* 1" (cost=0.00..55.20 rows=2260 width=12) (actual time=0.003..0.007 rows=11 loops=1)
Buffers: shared hit=1
-> Seq Scan on a (cost=0.00..32.60 rows=2260 width=8) (actual time=0.002..0.004 rows=11 loops=1)
Buffers: shared hit=1
-> Subquery Scan on "*SELECT* 2" (cost=0.00..55.20 rows=2260 width=12) (actual time=0.001..0.003 rows=6 loops=1)
Buffers: shared hit=1
-> Seq Scan on b (cost=0.00..32.60 rows=2260 width=8) (actual time=0.001..0.001 rows=6 loops=1)
Buffers: shared hit=1
Planning time: 0.041 ms
Execution time: 0.041 ms
…
hidden batch(es)
select * from a except all select * from b ;
id
x
1
1
1
1
4
4
4
4
4
4
4
4
…
hidden batch(es)
explain (analyze,buffers) select * from a except all select * from b ;
QUERY PLAN
HashSetOp Except All (cost=0.00..133.00 rows=2260 width=12) (actual time=0.017..0.018 rows=6 loops=1)
Buffers: shared hit=2
-> Append (cost=0.00..110.40 rows=4520 width=12) (actual time=0.003..0.012 rows=17 loops=1)
Buffers: shared hit=2
-> Subquery Scan on "*SELECT* 1" (cost=0.00..55.20 rows=2260 width=12) (actual time=0.003..0.007 rows=11 loops=1)
Buffers: shared hit=1
-> Seq Scan on a (cost=0.00..32.60 rows=2260 width=8) (actual time=0.003..0.004 rows=11 loops=1)
Buffers: shared hit=1
-> Subquery Scan on "*SELECT* 2" (cost=0.00..55.20 rows=2260 width=12) (actual time=0.001..0.003 rows=6 loops=1)
Buffers: shared hit=1
-> Seq Scan on b (cost=0.00..32.60 rows=2260 width=8) (actual time=0.001..0.001 rows=6 loops=1)
Buffers: shared hit=1
Planning time: 0.034 ms
Execution time: 0.034 ms
…
hidden batch(es)