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.
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.
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.
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
(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
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),
(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);
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.
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.