Krivalar Tutorials SQL CROSS JOIN
- In SQL, CROSS JOIN is used to combine the two different tables.
- In CROSS JOIN, each row of the first table is combined with each row of the second table.
- The resulting table of the cross join will contain the Cartesian product.
- The Cartesian product is nothing but a product of the number of rows from the first table and the number of rows from the second table.
- The CROSS JOIN is also known as the Cartesian Join.
- For example, if both the first and second tables contain four records, then the CROSS JOIN of these two tables will return 16 records(4X4=16).
CROSS JOIN - Synax
SELECT T1.column-name1, T1.column-name2, T2.column-name1, T2.column-name2......
FROM Table-name1 T1,Table-name2 T2
[OR]
SELECT T1.column-name1, T1.column-name2, T2.column-name1, T2.column-name2......
FROM Table-name1 T1
CROSS JOIN Table-name2 T2
Example with CROSS JOIN
Consider the below two demo tables for understanding the CROSS JOIN.
- Student_ID Table:
+--------+-------+---------+------+ | RollNo | ID_NO | Name | Dept | +--------+-------+---------+------+ | 1 | 101 | Arun | CSE | | 2 | 102 | Varun | IT | | 3 | 103 | Ara | ECE | | 4 | 104 | Markdin | ECE | | 5 | 105 | Kannan | IT | | 6 | 106 | Aruna | cse | | 7 | 107 | Jose | ECE | | 8 | 108 | Jithu | CSE | | 9 | 109 | Sai | ECE | +--------+-------+---------+------+- Book Table:
+------+--------------------+------------+----------+ | BKNo | Book_Name | Department | Quantity | +------+--------------------+------------+----------+ | 1 | Software Engg | CSE | 10 | | 2 | Electronics Engg | ECE | 5 | | 3 | Electrical Engg | EEE | 10 | | 4 | Thermal Engg | MECH | 5 | | 5 | Operation Research | MATH | 15 | | 6 | Engg.Maths | MATH | 15 | | 7 | Building | CIVIL | 10 | | 8 | Visual Pgm | IT | 10 | | 9 | Database | CSE | 15 | | 10 | Database | IT | 7 | +------+--------------------+------------+----------+
Following is the CROSS JOIN query statement used to join these two tables:
mysql> SELECT T1.RollNO, T1.ID_NO, T1.Name, T2.Book_Name -> FROM Student_ID T1 -> CROSS JOIN Book T2 ORDER BY RollNo;When you execute this above query statement, you will get the following result-set
+--------+-------+---------+--------------------+ | RollNO | ID_NO | Name | Book_Name | +--------+-------+---------+--------------------+ | 1 | 101 | Arun | Database | | 1 | 101 | Arun | Electrical Engg | | 1 | 101 | Arun | Software Engg | | 1 | 101 | Arun | Building | | 1 | 101 | Arun | Operation Research | | 1 | 101 | Arun | Thermal Engg | | 1 | 101 | Arun | Electronics Engg | | 1 | 101 | Arun | Engg.Maths | | 1 | 101 | Arun | Visual Pgm | | 1 | 101 | Arun | Database | | 2 | 102 | Varun | Software Engg | | 2 | 102 | Varun | Database | | 2 | 102 | Varun | Electrical Engg | | 2 | 102 | Varun | Building | | 2 | 102 | Varun | Electronics Engg | | 2 | 102 | Varun | Thermal Engg | | 2 | 102 | Varun | Engg.Maths | | 2 | 102 | Varun | Operation Research | | 2 | 102 | Varun | Visual Pgm | | 2 | 102 | Varun | Database | | 3 | 103 | Ara | Building | | 3 | 103 | Ara | Electrical Engg | | 3 | 103 | Ara | Software Engg | | 3 | 103 | Ara | Engg.Maths | | 3 | 103 | Ara | Database | | 3 | 103 | Ara | Electronics Engg | | 3 | 103 | Ara | Operation Research | | 3 | 103 | Ara | Thermal Engg | | 3 | 103 | Ara | Visual Pgm | | 3 | 103 | Ara | Database | | 4 | 104 | Markdin | Visual Pgm | | 4 | 104 | Markdin | Thermal Engg | | 4 | 104 | Markdin | Operation Research | | 4 | 104 | Markdin | Software Engg | | 4 | 104 | Markdin | Electronics Engg | | 4 | 104 | Markdin | Database | | 4 | 104 | Markdin | Building | | 4 | 104 | Markdin | Database | | 4 | 104 | Markdin | Electrical Engg | | 4 | 104 | Markdin | Engg.Maths | | 5 | 105 | Kannan | Operation Research | | 5 | 105 | Kannan | Building | | 5 | 105 | Kannan | Software Engg | | 5 | 105 | Kannan | Thermal Engg | | 5 | 105 | Kannan | Database | | 5 | 105 | Kannan | Electrical Engg | | 5 | 105 | Kannan | Engg.Maths | | 5 | 105 | Kannan | Database | | 5 | 105 | Kannan | Electronics Engg | | 5 | 105 | Kannan | Visual Pgm | | 6 | 106 | Aruna | Electronics Engg | | 6 | 106 | Aruna | Visual Pgm | | 6 | 106 | Aruna | Software Engg | | 6 | 106 | Aruna | Electrical Engg | | 6 | 106 | Aruna | Building | | 6 | 106 | Aruna | Database | | 6 | 106 | Aruna | Thermal Engg | | 6 | 106 | Aruna | Database | | 6 | 106 | Aruna | Engg.Maths | | 6 | 106 | Aruna | Operation Research | | 7 | 107 | Jose | Building | | 7 | 107 | Jose | Electronics Engg | | 7 | 107 | Jose | Engg.Maths | | 7 | 107 | Jose | Database | | 7 | 107 | Jose | Software Engg | | 7 | 107 | Jose | Electrical Engg | | 7 | 107 | Jose | Database | | 7 | 107 | Jose | Visual Pgm | | 7 | 107 | Jose | Thermal Engg | | 7 | 107 | Jose | Operation Research | | 8 | 108 | Jithu | Building | | 8 | 108 | Jithu | Database | | 8 | 108 | Jithu | Electronics Engg | | 8 | 108 | Jithu | Software Engg | | 8 | 108 | Jithu | Engg.Maths | | 8 | 108 | Jithu | Operation Research | | 8 | 108 | Jithu | Database | | 8 | 108 | Jithu | Visual Pgm | | 8 | 108 | Jithu | Thermal Engg | | 8 | 108 | Jithu | Electrical Engg | | 9 | 109 | Sai | Building | | 9 | 109 | Sai | Database | | 9 | 109 | Sai | Engg.Maths | | 9 | 109 | Sai | Electrical Engg | | 9 | 109 | Sai | Electronics Engg | | 9 | 109 | Sai | Database | | 9 | 109 | Sai | Visual Pgm | | 9 | 109 | Sai | Operation Research | | 9 | 109 | Sai | Thermal Engg | | 9 | 109 | Sai | Software Engg | +--------+-------+---------+--------------------+ 90 rows in set (0.00 sec) - Book Table:
