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.
SELECT columns FROM table1 LEFT OUTER JOIN table2 ON conditions;
SELECT columns FROM table1 LEFT OUTER JOIN table2 USING columns;
STUDENT_ID STUDENT_NAME STUDENT_SUBJECT 1 Tom French 2 Jerry Spanish 3 Bruno English
ID NAME SALARY SUBJECT 1 Jim 10000 English 2 John 20000 Geology 3 Watson 15000 French 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;
ID STUDENT_NAME NAME SUBJECT 3 Tom Watson French Jerry 1 Bruno Jim English
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.