CROSS JOIN In SQL

In this article, we will see how to use  'CROSS JOIN In SQL'

When each row of Table 1 is merged within each row of Table 2, then it's called a CROSS JOIN or Cartesian Join or in simple words, we can say it like CROSS JOIN always returns the Cartesian product of the sets of the record from (another two or more tables) joined table.

The syntax for the CARTESIAN JOIN or the CROSS JOIN:

We can write it with comma separated table name in order to achieve the same result or we can just write CROSS JOIN just like INNER JOIN without where clause,

SELECT TABLE1.Name, TABLE1.Age, TABLE2.ProjectName,TABLE2.DoA

FROM [EmpTable] AS TABLE1, [Projects] AS TABLE2

                  OR

SELECT TABLE1.Name, TABLE1.Age, TABLE2.ProjectName, TABLE2.DoA

FROM [EmpTable] AS TABLE1 CROSS JOIN [Projects] AS TABLE2

Let's Consider the following two tables:

Table1 : EmpTable 

Table2 : Projects

Now it's time to see the result, as we already know in CROSS JOIN, each row from Table 1 merged (joins/combined) with all rows of Table 2 (other tables / joined tables ) if Table 1 is having X number of rows and Table 2 having some Y number of rows then the result will be 

X * Y number of rows.

I hope this will be helpful to understand the CROSS JOIN in SQL.

for practice, you can download the scripts via google drive.

[Download SQL Scripts via Google Drive]