Cross Join in SQLite

SQLite Cross Join To join each row of the first table (of x number of rows) with each row of the second table (of y number of rows), the Cross join is used in SQLite. The resultant thus contains x*y number of rows.

Syntax:

SELECT columns 
FROM table1 
CROSS JOIN table2

Example: STUDENTS Table:

STUDENT_ID	STUDENT_NAME	STUDENT_SUBJECT
1	Tom	French
2	Jerry	Physics
3	Bruno	English

TEACHERS Table:

ID	NAME	SALARY	SUBJECT
1	Jim	10000	Geology
2	John	20000	Geology
3	Watson	15000	Physics
4	Holmes	25000	Chemistry
5	Tony	30000	Physics

SELECT * 
FROM STUDENTS 
CROSS JOIN TEACHERS;

Output:

STUDENT_ID	STUDENT_NAME	STUDENT_SUBJECT	ID	NAME	SALARY	SUBJECT
1	Tom	French	1	Jim	10000	English
1	Tom	French	2	John	20000	Geology
1	Tom	French	3	Watson	15000	French
1	Tom	French	4	Holmes	25000	Chemistry
1	Tom	French	5	Tony	30000	Physics
2	Jerry	French	1	Jim	10000	English
2	Jerry	French	2	John	20000	Geology
2	Jerry	French	3	Watson	15000	French
2	Jerry	French	4	Holmes	25000	Chemistry
2	Jerry	French	5	Tony	30000	Physics
3	Bruno	French	1	Jim	10000	English
3	Bruno	French	2	John	20000	Geology
3	Bruno	French	3	Watson	15000	French
3	Bruno	French	4	Holmes	25000	Chemistry
3	Bruno	French	5	Tony	30000	Physics

Explanation: In the above example, the STUDENTS table and the TEACHERS table are the two already existing tables in the database. Here, each row of the STUDENTS table is joined with each row of the TEACHERS table. The STUDENTS table have 3 rows and the TEACHERS table have 5 rows, the resultant table thus have 3*5 number of rows, i.e., 15 rows. Also, all the columns of the STUDENTS table and all the columns of the TEACHERS table are fetched without any conditions. The resultant table thus have 3 columns + 4 columns = 7 columns.

Please follow and like us: