SQLite Self Join

Here we will learn sqlite self join with example and how to use sqlite self join with Where clause in Select statement to join table itself with examples.

SQLite Self Join

In SQLite Self Join is used to join the same table with itself. To use SQLite Self Join we need to create different alias names for the same table to perform operations based on our requirements.


Suppose if we need to compare columns in same table then Self Join will help us to achieve our functionality. For example, we have a table called Employees with three columns Employee Id, Employee Name, Manager Id and we want to get the Employees who are managers in this situation we need to join Employees table with itself.

Syntax of SQLite Self Join

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


SELECT x.column_name, y.column_name...

FROM table1 x, table1 y

WHERE x.column_name1 = y.column_name1;

If you observe above SQLite Self join syntax we given alias name to table1 as x and y and used same field of table1 table for comparison.

Example of SQLite Self Join

We will see how to use SQLite Self join with an example for that create emp_master table and insert some data by using the following queries.


CREATE TABLE emp_master


first_name TEXT,

last_name TEXT,

salary NUMERIC,

dept_id INTEGER,

manager_id INTEGER);


INSERT INTO emp_master

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

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

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


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

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

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


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     manager_id

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

2           Shweta      Jariwala    19300       2           7

3           Vinay       Jariwala    35000       3           2

4           Jagruti     Viras       9500        2           7

5           Shweta      Rana        12000       3           2

6           Sonal       Menpara     13000       1           3

7           Yamini      Patel       10000       2           7

8           Khyati      Shah        49900       3           2

9           Shwets      Jariwala    19400       2           7  

Here, the manager_id column is a self-reference to emp_id in the emp_master table. Now write SQLite query like as shown following to use Self Join with a select statement to get employees manager details.


SELECT x.emp_id, x.first_name as Employee, y.emp_id as 'Manager

ID', y.first_name as 'Manager Name'

FROM emp_master x, emp_master y

WHERE x.manager_id = y.emp_id;

If you observe the above example we used the emp_master table twice with different alias names (x, y) to get a list of employees with their manager’s details.

Result of SQLite Self Join Query 

Following is the result of the above SQLite self join query.


emp_id      Employee    Manager ID  Manager Name

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

2           Shweta      7           Yamini

3           Vinay       2           Shweta

4           Jagruti     7           Yamini

5           Shweta      2           Shweta

6           Sonal       3           Vinay

7           Yamini      7           Yamini

8           Khyati      2           Shweta

9           Shwets      7           Yamini 

This is how we can use SQLite self join query to get details based on our requirements.