All for Joomla All for Webmasters

SQL CROSS JOIN

When each row of first table is combined with each row from the second table, known as Cartesian join or cross join. In general words we can say that SQL CROSS JOIN returns the Cartesian product of the sets of rows from the joined table.

We can specify a CROSS JOIN in two ways:

  1. Using the JOIN syntax.
  2. the table in the FROM clause without using a WHERE clause.

SYNTAX of SQL CROSS JOIN:

  1. SELECT * FROM [TABLE1] CROSS JOIN [TABLE2]
  2. OR
  3. SELECT * FROM [ TABLE1] , [TABLE2]

Let us take an example of two tables,

Table1 – MatchScore

Player Department_id Goals
Franklin 1 2
Alan 1 3
Priyanka 2 2
Rajesh 3 5

Table2 – Departments

Department_id Department_name
1 IT
2 HR
3 Marketing

SQL Statement:

  1. SELECT * FROM MatchScore CROSS JOIN Departments

After executing this query , you will find the following result:

Player Department_id Goals Depatment_id Department_name
Franklin 1 2 1 IT
Alan 1 3 1 IT
Priyanka 2 2 1 IT
Rajesh 3 5 1 IT
Franklin 1 2 2 HR
Alan 1 3 2 HR
Priyanka 2 2 2 HR
Rajesh 3 5 2 HR
Franklin 1 2 3 Marketing
Alan 1 3 3 Marketing
Priyanka 2 2 3 Marketing
Rajesh 3 5 3 Marketing
PinIt
submit to reddit

Leave a Reply

Top