CodesJava

Easy learning with example program codes

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	        Spanish
3	        Bruno	        English

TEACHERS Table:

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;

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:
error









Copyright © 2019 CodesJava DMCA.com Protection Status