Prerequisite – Relational Algebra
We already are aware of the fact that relations are nothing but a set of tuples, and here we will have 2 sets of tuples.
On applying CARTESIAN PRODUCT on two relations that is on two sets of tuples, it will take every tuple one by one from the left set(relation) and will pair it up with all the tuples in the right set(relation).
So, the CROSS PRODUCT of two relation A(R1, R2, R3, …, Rp) with degree p, and B(S1, S2, S3, …, Sn) with degree n, is a relation C(R1, R2, R3, …, Rp, S1, S2, S3, …, Sn) with degree p + n attributes.
CROSS PRODUCT is a binary set operation means, at a time we can apply the operation on two relations. But the two relations on which we are performing the operations do not have the same type of tuples, which means Union compatibility (or Type compatibility) of the two relations is not necessary.
A ✕ S
where A and S are the relations,
the symbol ‘✕’ is used to denote the CROSS PRODUCT operator.
Consider two relations STUDENT(SNO, FNAME, LNAME) and DETAIL(ROLLNO, AGE) below:
On applying CROSS PRODUCT on STUDENT and DETAIL:
STUDENT ✕ DETAILS
We can observe that the number of tuples in STUDENT relation is 2, and the number of tuples in DETAIL is 2. So the number of tuples in the resulting relation on performing CROSS PRODUCT is 2*2 = 4.
Important points on CARTESIAN PRODUCT(CROSS PRODUCT) Operation:
- The cardinality (number of tuples) of resulting relation from a Cross Product operation is equal to the number of attributes(say m) in the first relation multiplied by the number of attributes in the second relation(say n).
Cardinality = m*n
- The Cross Product of two relation A(R1, R2, R3, …, Rp) with degree p, and B(S1, S2, S3, …, Sn) with degree n, is a relation C(R1, R2, R3, …, Rp, S1, S2, S3, …, Sn) with degree p + n attributes.
Degree = p+n
- In SQL, CARTESIAN PRODUCT(CROSS PRODUCT) can be applied using CROSS JOIN.
- In general, we don’t use cartesian Product unnecessarily, which means without proper meaning we don’t use Cartesian Product. Generally, we use Cartesian Product followed by a Selection operation and comparison on the operators as shown below :
σ A=D (A ✕ B)
The above query gives meaningful results.
And this combination of Select and Cross Product operation is so popular that JOIN operation is inspired by this combination.
- CROSS PRODUCT is a binary set operation means, at a time we can apply the operation on two relations.