The Cartesian product operation

The Cartesian product operation is used to combine tuples from two relations into a single one. The number of attributes in the single relation equals the sum of the number of attributes of the two relations. The number of tuples in the single relation equals the product of the number of tuples in the two relations. Let's assume that A and B are two relations, and C = A × B:

The number of attributes of C = the number of attributes of A + the number of attributes of B

The number of tuples of C = the number of tuples of A * the number of tuples of B

The following table shows the cross join of customer and customer service:

The equivalent SQL join for Cartesian product is CROSS JOIN; the query for the customer with customer_id equal to 1, retrieve the customer_id, name, and the customer service IDs can be written in SQL as follows:

SELECT DISTINCT customer_id, first_name, last_name, service_id FROM customer AS c CROSS JOIN customer_service AS cs WHERE c.customer_id=cs.customer_id AND c.customer_id = 1;

In the preceding example, one can see the relationship between relational algebra and the SQL language. For example, we have used SELECT, RENAME, PROJECT, and Cartesian product. The preceding example shows how relational algebra could be used to optimize query execution. This example could be executed in several ways:

Execution plan 1:

1. SELECT the customer where customer_id = 1
2. SELECT the customer service where customer_id = 1

1. CROSS JOIN the relations resulting from Step 1 and Step 2
2. PROJECT customer_id, first_name, last_name, and service_id from the relation resulting from Step 3

Execution plan 2:

1. CROSS JOIN customer and customer_service
2. SELECT all the tuples where Customer_service.customer_id=customer.customer_id and customer.customer_id = 1
3. PROJECT customer_id, first_name, last_name, and service_id from the relation resulting from Step 2

Each execution plan has a cost in terms of CPU, random access memory (RAM), and hard disk operations. The RDBMS picks the one with the lowest cost. In the preceding execution plans, the RENAME and DISTINCT operators were ignored for simplicity.