CARTESIAN PRODUCT in SQL

For Complete YouTube Video: Click Here

In this class, we will try to understand CARTESIAN PRODUCT in SQL.

Table of Contents

You may have learned the concept of cartesian product in primary school.

To have a quick recap, we will use the example below.

The cartesian product is also called cross-product.

A = {1, 2, 3, 4} and B = {a, b}

In a cartesian product, every set element will get produced with each ‘element’ of another set.

The cartesian product of A and B is as shown below.

A*B = { (1, a), (1, b), (2, a), (2, b) (3, a), (3, b), (4, a), (4, b)}

We will consider the following tables and the query to select the values from those tables to understand this.

Write a Query to find the names of the students who belong to the CSE Department?

To get the names of the students, we have to use both the student and departments table.

The query to get the students belonging to the CSE department is as shown below.

SELECT SName FROM student, departments
WHERE DNo = DNumber AND DName = ‘CSE’;

To get the output, the SQL will first do the tables’ cartesian product after the FROM clause.

Every row of the student table will get produced with each row of the department’s table.

The cartesian product is as shown below.

From the above table, the SQL will check the conditions after the WHARE clause.

The conditions are DNo = DNumber AND DName = ‘CSE’.

The rows selected after checking the conditions are as shown below.

From the resultant table, the Sname column will get selected as shown below.

Let us execute the same on the SQL.

The output of the query is as shown below.