Cross Join in SQLite

by

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.