Cartesian Join | Cartesian Join with Real Life Examples | Cross Join

Cartesian Join :

In my previous article i have explained about the different joins with real life examples. In this article i would like to give you Cartesian Join with real life example.Cross Join between two table gives us the data which joins the each and every row of one table to another table.The Cross Join is also called as Cross join.

Cartesian Join with Real Life Examples :

In this section i would like to explain the Cross Join with real life examples.When the condition is missing in the query it will give the Cross join product of different tables.This kind of join happens when no matching join conditions are specified.

Joins in SQL

There are following important bullet points of cross Join :

1.Missing ‘Where’ Condition :

The Where condition is missing or wrong in cross join and it behaves like the cross product.

Here the Number of the result-set is nothing but the product of number of rows of two tables.

Real Life Example :

Lets say that Table_1 have 10 records and Table_2 have 10 records then its Cartesian join  will return 10*10=100 records.

2.The Cartesian join query must have at least (N-1) join conditions to prevent a Cartesian product.Here The N is number of table in the query.

3.The joining condition in Cartesian product is always true or it is always missing.

Syntax : Where Condition missing

The following syntax is Cartesian Join or Cross join,

Select A.column_1,A.Column_2,B.Column_1……..

From Table_1 A,Table_2 B ;

The above syntax is for Cartesian join which does not have the where condition.

Syntax 2 : Using Cross Join Keyword

Select A.column_1,A.Column_2,B.Column_1……..

From Table_1 A cross join Table_2 B ;

The above syntax is using Cross Join Keyword.

The cross join is really very rarely used join.I would like to give you two different examples of Cartesian join one with Cross join and other with missing Where condition.

Example 1 : Student and Course table.

If Student table contains 2 records and Course table contains 2 records,

Select a.name,b.course from student a,Course b;

The above query will return the 4 records without using the ‘where’ conditions.

Example 2 : Employee table and Production table

If Employee table contains 1 record and Production table contains 2 records then following will be the query with cross join,

Select a.name,b.Standard from Employee a cross join Production b;

The above query will return the 2 records with using cross join.

These are two different examples of cross join. The cross product is very less used join but it is also important join.Mainly the Cartesian join is used in E-Commerce websites like flip-cart to check the customer-product relationship.Hope you like this article on cross join.If you like the article or if you have any issues with the same kindly comment in to comments section.