SQLite Self Join

  Total Views : 47
  Comments : 0
 
  Keywords : SQLite self join with where clause example, SQLite self join to join table itself with example, SQLite self join table with alias name example

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 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 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 comparision.

Example of SQLite Self Join

We will see how to use SQLite Self join with example for that create emp_master table and insert some data by using 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, manager_id column is self reference to emp_id in emp_master table. Now write SQLite query like as shown following to use Self Join with 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 above example we used emp_master table twice with different alias names (x, y) to get list of employees with their manager’s details.

Result of SQLite Self Join Query 

Following is the result of 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.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User