The Relational Algebra

DIFFERENCE 

(also called MINUS or EXCEPT).  

 

These are binary operations; that is, each is applied to two sets 

(of tuples).When these operations are adapted to relational databases, the 

two relations on which any of these three operations are applied must have 

the same type of tuples; this condition has been called union compatibility 

or type compatibility.  

 

Two relations R(A1, A2, ..., An) and S(B1, B2, ..., Bn) are said to be union 

compatible 

(or type compatible) if they have the same degree n and if 

dom(Ai) = dom(Bi) for 1 ≤ I ≤ n.  

 

This means that the two relations have the same number of attributes and 

each corresponding pair of attributes has the same domain. 

 

We can define the three operations UNION, INTERSECTION, and SET 

DIFFERENCE on two union-compatible relations R and S as follows: 

•  UNION: The result of this operation, denoted by R ∪ S, is a relation 

that includes all tuples that are either in R or in S or in both R and S. 

Duplicate tuples are eliminated. 

•  INTERSECTION: The result of this operation, denoted by  R∩S, is a 

relation that includes all tuples that are in both R and S. 

•  SET DIFFERENCE (or MINUS): The result of this operation, denoted 

by R – S, is a relation that includes all tuples that are in R but not in 

S. 

We will adopt the convention that the resulting relation has the same 

attribute  name as the first relation R. It is always possible to rename the 

attributes in the result using the rename operator. 

 

Notice that both UNION and INTERSECTION are commutative operations; 

that is, 

R 

∪ S = S ∪ R and R ∩S = S ∩ R 

---

Both UNION and INTERSECTION can be treated as n-ary operations 

applicable to any number of relations because both are also associative 

operations; that is, 

R 

∪ (S ∪ T) = (R ∪ S) ∪ T and (R ∩S ) ∩T = R ∩(S ∩T ) 

 

The MINUS operation is not commutative; that is, in general, 

R – S 

⎯ ≠ S  – R 

Note that INTERSECTION can be expressed in terms of union and set 

difference as follows: 

R ∩S = ((R 

∪ S )   −(R   −S ))   −(S   −R) 

 

In SQL, there are three operations—UNION, INTERSECT, and EXCEPT—

that correspond to the set operations described here. 

 In addition, there are multiset operations (UNION ALL, INTERSECT ALL, 

and EXCEPT ALL) that do not eliminate duplicates.