SQLite Left Outer Join

Here we will learn SQLite left outer join with example and how to use SQLite left outer join with multiple tables to return all the records from left table and matching records from another table with example.

SQLite Left Outer Join

In SQLite, by using Inner Join we can get only matching rows from multiple tables based on the conditions defined in select statements. If we use SQLite Outer Join it will select matching rows from multiple tables same as Inner Join and some other rows outside of the relationship.


In simple terms we can say SQLite OUTER JOIN is an addition of INNER JOIN. Generally we have three types of Outer Joins in SQL standard those are LEFT, RIGHT and FULL Outer Joins but SQLite supports only LEFT OUTER JOIN.

Syntax of SQLite Left Outer Join

Following is the syntax of using SQLite Left Outer Join with Select statement.


SELECT t1.col1,t2.col2,...FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.col3=t2.col5;

In the above SQLite Left Outer Join, table1 is a left-hand table, and table2 is a right-hand table. Here the left outer join tries to match every row of table1 table with every row in table2 table based on the join condition (t1.col3 = t2.col5) and it returns matching rows from both the tables and remaining rows of table1 table that doesn't match with table2 table are also included in the result.

Example of SQLite Cross Join

Following is the simple example of SQLite Left Outer Join with Select statement.



In the above example, we applied Left Outer Join on X and Y tables. The Left Outer Join matches all the rows of the X table with all the rows in Y table based on the defined condition X.x = Y.y.


Following is the simple pictorial representation of SQLite LEFT Outer Join to understand easily.


SQLite Left Outer Join Example Pictorial Representation


If you observe above SQLite LEFT OUTER JOIN result it returned matched rows from both the tables (X, Y) and unmatched rows from left (X) table.


Now we will see the example of SQLite Left Outer JOIN with a database table. We need to create two tables dept_master and emp_master and need to insert some data for that use following queries.


To create and insert some data in the dept_master table execute the following query.


CREATE TABLE dept_master


dept_name TEXT);


INSERT INTO dept_master(dept_name)



('Quality Control');


Once we create and insert data execute following query to see the data of dept_master table.


sqlite> SELECT * FROM dep_master;


dept_id     dept_name

----------  ----------

1           Admin

2           Sales

3           Quality Control

4           Marketing

Same way execute the following queries to create and insert some data in the emp_master table.


CREATE TABLE emp_master


first_name TEXT,

last_name TEXT,

salary NUMERIC,

dept_id INTEGER);


INSERT INTO emp_master

values (2,'Shweta','Jariwala', 19300,2),

(3,'Vinay','Jariwala', 35100,3),

(4,'Jagruti','Viras', 9500,2),


(6,'sonal','Menpara', 13000,1),

(7,'Yamini','Patel', 10000,2),

(8,'Khyati','Shah', 49900,3),


Now run the following query to check records of emp_master table.


sqlite> SELECT * FROM emp_master;


emp_id      first_name  last_name   salary      dept_id

----------  ----------  ----------  ----------  ----------

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35000       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        49900       3

9           Shwets      Jariwala    19400       2

Now write SQLite query like as shown following to use Left Outer JOIN with a select statement.


SELECT d.dept_id,dept_name,emp_id,first_name

FROM dept_master d


emp_master e ON d.dept_id=e.dept_id;

If you observe above SQLite Left Outer Join example we defined Left Join condition on the dept_id column of tables.

Result of SQLite Left Outer Join Example

Now we will run and check the output that will be like as shown following.


dept_id     dept_name   emp_id      first_name

----------  ----------  ----------  ----------

1           Admin       6           Sonal

2           Sales       4           Jagruti

2           Sales       2           Shweta

2           Sales       9           Shwets

2           Sales       7           Yamini

3           Quality Co  8           Khyati

3           Quality Co  5           Shweta

3           Quality Co  3           Vinay

4           Marketing

Here dept_master is a left-hand table so all the rows of the dept_master table included in the result even though rows are unmatched and it included only matched rows of emp_master table. 


In emp_master table, we don't have any employee for the marketing department so in result emp_id and first_name are null.


This is how we can use SQLite Left Outer Join in our queries based on our requirements.