 |
| SQL CROSS JOIN in DB2 for i SQL |
Cross Join
A cross join is also known as Cartesian Product JOIN. Cross Join returns the result table where each row from the first table is combined with each row from the second table. The number of rows in the result table is the product of number of rows in each table. So let's say first table has 2 rows and second table has 3 rows then, result would have 2*3 = 6 rows.
A cross join can be specified in two ways:
Cross Join using JOIN
Syntax of Cross Join using JOIN
SELECT * FROM Table1 CROSS JOIN Table2
Example using CROSS JOIN using JOIN on table STUDENT and COURSE
Result:
ROLLNO NAME GENDER ROLLNO COURSEID COURSENAME
1 AMIT M 1 C1 IT
1 AMIT M 2 C2 EC
1 AMIT M 6 C3 EL
2 AMAN M 1 C1 IT
2 AMAN M 2 C2 EC
2 AMAN M 6 C3 EL
3 HEMANT M 1 C1 IT
3 HEMANT M 2 C2 EC
3 HEMANT M 6 C3 EL
4 ANNAMALAI M 1 C1 IT
4 ANNAMALAI M 2 C2 EC
4 ANNAMALAI M 6 C3 EL
5 KESHAV M 1 C1 IT
5 KESHAV M 2 C2 EC
5 KESHAV M 6 C3 EL
Cross Join using FROM clause
Syntax of Cross Join using FROM clause
SELECT * FROM Table1, Table2
Example using CROSS JOIN using FROM clause on table STUDENT and COURSE
Result:
ROLLNO NAME GENDER ROLLNO COURSEID COURSENAME
1 AMIT M 1 C1 IT
1 AMIT M 2 C2 EC
1 AMIT M 6 C3 EL
2 AMAN M 1 C1 IT
2 AMAN M 2 C2 EC
2 AMAN M 6 C3 EL
3 HEMANT M 1 C1 IT
3 HEMANT M 2 C2 EC
3 HEMANT M 6 C3 EL
4 ANNAMALAI M 1 C1 IT
4 ANNAMALAI M 2 C2 EC
4 ANNAMALAI M 6 C3 EL
5 KESHAV M 1 C1 IT
5 KESHAV M 2 C2 EC
5 KESHAV M 6 C3 EL