Set Operators
SQL Language: Set Operators
➢The UNION Operator
➢The INTERSECT Operator
➢The EXCEPT Operator
1
• Set union operator
A UNION B
• It performs the union of the two
relational expressions A and B
UNION • relational expressions A and B may be
generated by SELECT statements
• it requires schema compatibility between A and B
• removal of duplicates
• UNION removes duplicates
• UNION ALL does not remove duplicates
2
UNION: example
• Find the codes of products that are either red or supplied by supplier
S2 (or both)
SP
SId PId Qty
P
S1 P1 300
PId PName Color Size Store
S1 P2 200
P1 Jumper Red 40 London
S1 P3 400
P2 Jeans Green 48 Paris
S1 P4 200
P3 Blouse Blue 48 Rome
S1 P5 100
P4 Blouse Red 44 London
S2 P1 300
P5 Skirt Blue 40 Paris
S2 P2 400
S3 P2 200
S4 P3 200
S4 P4 300
S4 P5 400
S1 P1 300 3
UNION: example
• Find the codes of products that are either red or supplied by supplier
S2 (or both)
SELECT PId
FROM P
WHERE Color = ‘Red’
P
PId PName Color Size Store
P1 Jumper Red 40 London
P2 Jeans Green 48 Paris
PId
P3 Blouse Blue 48 Rome
P1
P4 Blouse Red 44 London
P4
P5 Skirt Blue 40 Paris
4
UNION: example
• Find the codes of the products that are either red or supplied by
supplier S2 (or both)
SP SELECT PId
SId PId Qty
S1 P1 300 FROM SP
S1 P2 200 WHERE Sid = ‘S2’
S1 P3 400
S1 P4 200
S1 P5 100
S2 P6 100
S2 P1 300 PId
S3 P2 400 P6
S4 P2 200 P1
S4 P3 200
S4 P4 300
S1 P5 400
5
UNION: example
• Find the codes of products that are either red or supplied by supplier
S2 (or both)
SELECT PId
FROM P R
WHERE Color = ‘Red’ PId
Removing
P1
Schema P4 the duplicate
Compatibility UNION P6
SELECT PId
FROM SP
WHERE Sid = ‘S2’
6
UNION ALL: example
• Find the codes of products that are either red or supplied by supplier
S2 (or both)
SELECT PId PId
P1
FROM P
P4
WHERE Color = ‘Red’ R
PId
Schema P1
Compatibility UNION ALL P1
P4
P6
SELECT PId PId
FROM SP P1 Duplicates are
P6 not removed
WHERE Sid = ‘S2’
7
• Set intersection operator
A INTERSECT B
INTERSECT • It performs the intersection of the two
relational expressions A and B
• relational expressions A and B may be
generated by SELECT statements
• it requires schema compatibility between A and B
8
INTERSECT: example
• Find the cities where both one or more suppliers and one or more
stores are based
P PId PName Color Size Store
P1 Jumper Red 40 London
P2 Jeans Green 48 Paris
P3 Blouse Blue 48 Rome
P4 Blouse Blue 44 London
P5 Skirt Blue 40 Paris
S SId SName #Employees City
S1 Smith 20 London
S2 Jones 10 Paris
S3 Blake 30 Paris
S4 Clark 20 London
S5 Adams 30 Athens
9
INTERSECT: example
• Find the cities where both one or more suppliers and one or more
stores are based
SELECT City
FROM S
S
SId NameS #Employees City City
F1 Smith 2 London London
F2 Jones 1 Paris Paris
F3 Blake 3 Paris Paris
F4 Clark 2 London London
F5 Adams 3 Athens Athens
10
INTERSECT: example
• Find the cities where both one or more suppliers and one or more
stores are based
SELECT Store
FROM P
P
PId PName Color Size Store Store
P1 Jumper Red 40 London London
P2 Jeans Green 48 Paris Paris
P3 Blouse Blue 48 Rome Rome
P4 Blouse Red 44 London London
P5 Skirt Blue 40 Paris Paris
P6 Shorts Red 42 London London
11
INTERSECT: example
• Find the cities where both one or more suppliers and one or more
stores are based
City
SELECT City London
Paris
FROM S
Paris
London R
INTERSECT Athens
London
Store Paris
London
SELECT Store Paris
FROM P; Rome
London
Paris
London
12
Equivalence with other operators
• The intersection operation may also be performed by means of JOIN
and IN
JOIN IN
• The FROM clause contains the • One of the two relational
relations involved in the
intersection expressions is turned into a
nested query using operator IN
• The WHERE clause contains
join conditions between the • The attributes in the
attributes listed in outer SELECT clause, grouped by
the SELECT clauses of a tuple constructor, make up the
relational expressions A and B left-hand side of the IN operator
13
Example: equivalence with join
• Find the cities where both one or more suppliers and one or more
stores are based
SELECT Store
FROM S, P
WHERE [Link] =[Link];
14
Example: equivalence with IN
• Find the cities where both one or more suppliers and one or more
stores are based
SELECT Store
FROM P
WHERE Store IN (SELECT City
FROM S);
15
• Set difference operator
A EXCEPT B
EXCEPT • It subtracts relational expression B from
relational expression A
• it requires schema compatibility between A and B
16
EXCEPT: example
• Find the cities where one or more suppliers, but no stores are based
P PId PName Color Size Store
P1 Jumper Red 40 London
P2 Jeans Green 48 Paris
P3 Blouse Blue 48 Rome
P4 Blouse Red 44 London
P5 Skirt Blue 40 Paris
P6 Shorts Red 42 London
S SId SName #Employees City
S1 Smith 20 London
S2 Jones 10 Paris
S3 Blake 30 Paris
S4 Clark 20 London
S5 Adams 30 Athens
17
EXCEPT: example
• Find the cities where one or more suppliers, but no stores are based
SELECT City
FROM S
S
SId SName #Employees City City
S1 Smith 20 London London
S2 Jones 10 Paris Paris
S3 Blake 30 Paris Paris
S4 Clark 20 London London
S5 Adams 30 Athens Athens
18
EXCEPT: example
• Find the cities where one or more suppliers, but no stores are based
SELECT Store
FROM P
P
PId PName Color Size Store Store
P1 Jumper Red 40 London London
P2 Jeans Green 48 Paris Paris
P3 Blouse Blue 48 Rome Rome
P4 Blouse Red 44 London London
P5 Skirt Blue 40 Paris Paris
P6 Shorts Red 42 London London
19
EXCEPT: example
• Find the cities where one or more suppliers, but no stores are based
City
SELECT City London
Paris
FROM S Paris
London
Athens
R
EXCEPT
Store Athens
London
SELECT Store Paris
Rome
FROM P; London
Paris
London
20
Equivalence with the NOT IN operator
• The EXCEPT operation may also be performed by means of the NOT
IN operator
• relational expression B is nested within the NOT IN operator
• the attributes in the SELECT clause of relational expression A, together by a
tuple constructor, make up the left-hand side of the NOT IN operator
21
Equivalence with the NOT IN operator: example
• Find the cities where one or more suppliers, but no stores are based
SELECT City
FROM S
WHERE City NOT IN (SELECT Store
FROM P);
22