Outer Join in SQLite

SQLite Outer Join Unlike SQL, the SQLite supports only one type of OUter JOin and that is the Left Outer Join.

SQLite Left Outer Join: The SQLite left outer join fetches all the rows from the specified fields of the left-hand table. However, for the right-hand table, it joins only those rows where the join condition is satisfied, the SQLite left outer join is used.

Syntax 1:

SELECT columns
FROM table1 
LEFT OUTER JOIN table2 
ON conditions;

Syntax 2:

SELECT columns
FROM table1 
LEFT OUTER JOIN table2 
USING columns;

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 ID, STUDENT_NAME, NAME, SUBJECT
FROM STUDENTS 
LEFT OUTER JOIN TEACHERS  
ON STUDENTS.STUDENT_SUBJECT = TEACHERS.SUBJECT;

Output:

ID	STUDENT_NAME	NAME	SUBJECT
3	Tom	Watson	French
	Jerry
1	Bruno	Jim	English

Explanation: In the above example, all the records from the ID, NAME and the SUBJECT Columns of the TEACHERS table is fetched where the value of the STUDENT_SUBJECT column of the STUDENTS table is equal to the SUBJECT column of the TEACHERS table and all the records of the STUDENT_NAME column of the STUDENTS table is fetched.

Please follow and like us: