SQLite LEFT JOIN

The SQLite LEFT JOIN is used to return all records from the left table, even if there is no match with the right table.

Syntax

The SQLite LEFT JOIN syntax is as follows:

SELECT column1, column2, ...
FROM left_table
LEFT JOIN right_table ON condition;

Example

Table of students

student_id first_name last_name birthday address_id
1 Paul Smith 2002-10-14 100
2 Charlotte Jones 2003-05-18 101
3 Theodore Hernandez 2003-08-12 102
4 Susan Taylor 2004-07-24 NULL

Table of student_address

address_id city country
100 San Antonio US
101 San Jose US
102 Philadelphia US
103 Austin US
104 Boston US
105 Seattle US

In the following example, the query uses a LEFT JOIN between the students table and student_address. According to the LEFT JOIN definition, the select will return all records from the students table, even if there are students without a completed address, more precisely address_id does not find a corresponding value in the student_address table.

SELECT s.*
FROM students s LEFT JOIN student_address sa 
ON s.address_id = sa.address_id ;

Output

student_id first_name last_name birthday address_id
1 Paul Smith 2002-10-14 100
2 Charlotte Jones 2003-05-18 101
3 Theodore Hernandez 2003-08-12 102
4 Susan Taylor 2004-07-24 NULL