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 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 attribute of C = the number of attribute in A + the number of attribute of B

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

The following image 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.
3. Cross JOIN the relations resulting from steps 1 and 2.
4. 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.

The SELECT query is written in this way to show how to translate relational algebra to SQL. In modern SQL code, we can project attributes without using DISTINCT. In addition to that, one should use a proper join instead of cross join.

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 as well as distinct operator were ignored for simplicity.