# 100CodeExamples - Pitfalls of MINUS

• Script Name 100CodeExamples - Pitfalls of MINUS
• Visibility Unlisted - anyone with the share link can access
• Description Example of how UNION ALL and MINUS' equal precedence can cause problems when comparing two results
• Area SQL General
• Contributor Pesse
• Created Friday April 12, 2019
• Statement 1
``````create table star_wars_characters (
id integer primary key,
name varchar2(100)
)``````
Table created.
• Statement 2
``````create table appearance_in_episode (
character_fk integer not null
references star_wars_characters ( id )
episode_no number(2,0) not null
)``````
Table created.
• Statement 3
``````insert into star_wars_characters
values ( 1, 'Darth Vader' )``````
1 row(s) inserted.
• Statement 4
``````insert into star_wars_characters
values ( 2, 'Luke Skywalker' )``````
1 row(s) inserted.
• Statement 5
``````insert into star_wars_characters
values ( 3, 'Rey' )``````
1 row(s) inserted.
• Statement 6
``insert into appearance_in_episode values ( 1, 3 )``
1 row(s) inserted.
• Statement 7
``insert into appearance_in_episode values ( 1, 4 )``
1 row(s) inserted.
• Statement 8
``insert into appearance_in_episode values ( 1, 5 )``
1 row(s) inserted.
• Statement 9
``insert into appearance_in_episode values ( 1, 6 )``
1 row(s) inserted.
• Statement 10
``insert into appearance_in_episode values ( 2, 4 )``
1 row(s) inserted.
• Statement 11
``insert into appearance_in_episode values ( 2, 5 )``
1 row(s) inserted.
• Statement 12
``insert into appearance_in_episode values ( 2, 7 )``
1 row(s) inserted.
• Statement 13
``insert into appearance_in_episode values ( 2, 9 )``
1 row(s) inserted.
• Statement 14
``insert into appearance_in_episode values ( 3, 8 )``
1 row(s) inserted.
• Statement 15
``insert into appearance_in_episode values ( 3, 9 )``
1 row(s) inserted.
• Statement 16
``````create or replace view all_movie_characters
as
select
sw_char.id,
sw_char.name
from
star_wars_characters sw_char
inner join appearance_in_episode ep
on sw_char.id = ep.character_fk
group by sw_char.id, sw_char.name ``````
View created.
• Statement 17
``````create or replace function appears_in_movie(
i_character_id integer
) return integer result_cache
as
l_count integer;
begin
select count(*)
into l_count
from appearance_in_episode
where character_fk = i_character_id
and episode_no between 4 and 6;
if l_count > 0 then
return 1;
else
return 2;
end if;
end; ``````
Function created.
• Statement 18
``````create or replace view all_movie_character_2
as
select
sw_char.id,
sw_char.name
from
star_wars_characters sw_char
where appears_in_movie(sw_char.id) = 1 ``````
View created.
• Statement 19
``````select * from all_movie_characters
minus
select * from all_movie_character_2
union all
select * from all_movie_character_2
minus
select * from all_movie_characters``````
no data found
• Statement 20
``select * from all_movie_character_2``
IDNAME
2Luke Skywalker

2 rows selected.
• Statement 21
``````select * from all_movie_characters
minus
select * from all_movie_character_2``````
IDNAME
3Rey
• Statement 22
``````select * from all_movie_characters
minus
select * from all_movie_character_2
union all
select * from all_movie_character_2``````
IDNAME
3Rey
2Luke Skywalker

3 rows selected.
• Statement 23
``````select * from all_movie_characters
minus
select * from all_movie_character_2
union all
select * from all_movie_character_2
minus
select * from all_movie_characters``````
no data found
• Statement 24
``````select * from
(
select * from all_movie_characters
minus
select * from all_movie_character_2
)
union all
(
select * from all_movie_character_2
minus
select * from all_movie_characters
)``````
IDNAME
3Rey