SQLite Left Outer Join

  Keywords : SQLite left join with where clause examples, Use sqlite left join with multiple tables example, SQLite left join to return matching and all rows from left table with example

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 table same like 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 above SQLite Left Outer Join table1 is 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 return 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 above example we applied Left Outer Join on X and Y tables. The Left Outer Join matches all the rows of X table with all the rows in Y table baed 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 database table. We need 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 dept_master table execute 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 following queries to create and insert some data in 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 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 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 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 marketing deparment 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.