MYSQL - CROSS JOIN Query

MySQL CROSS JOIN is used to combine all possibilities of the two or more tables and returns the result that contains every row from all contributing tables. The CROSS JOIN clause returns the Cartesian product of rows from the joined tables. The CROSS JOIN is also known as CARTESIAN JOIN, which provides the Cartesian product of all associated tables.  if each table has n and m rows respectively, the result set will have n x m rows.

MYSQL Syntax :

SELECT field_name1, field_name2, ....
FROM table1
CROSS JOIN table2
ON Join_Condition;

Cross Join Query
MYSQL CROSS JOIN – Venn Diagram

MYSQL Query Example :
To fetch all records from both tables, execute the following query :

SELECT s.stud_id,s.stud_name,m.maths,m.science FROM tblstudent s CROSS JOIN tblmarks m

 

Cross Join Query
stud_id stud_name maths science
1 manish patel 45 78
1 manish patel 65 76
1 manish patel 87 74
3 Raju modi 45 78
3 Raju modi 65 76
3 Raju modi 87 74
4 jacky jain 45 78
4 jacky jain 65 76
4 jacky jain 87 74

Explain : 

  1. Tables tblstudent and tblmarks are joined by CROSS JOIN clause.
  2. CROSS Join query returns all records which are matched in both table tblstudent and tblmarks.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

50454