SQLite | Joins | Joining a Table to Itself (SELF JOIN)

Tags:

• SQLite
• Database

Inner and outer joins usually combine multiple tables, but a table can also be joined to itself. This is called a self join. This article explains how to use one.

Using a Self Join

A self join applies an inner or outer join to the same table. Because the same table appears twice, assign a different alias to each occurrence.

SELECT alias1.column_name, ...
  FROM table_name alias1 INNER JOIN table_name alias2
  ON alias1.column_name = alias2.column_name;

Unlike a join between separate tables, the table after FROM and the table after JOIN are the same. Aliases distinguish overlapping column names in the join condition and result.

–

Create a table for the example.

create table employee (id integer, name text, boss_id integer);

sqlite> create table employee (id integer, name text, boss_id integer);
sqlite> 

Insert the following data.

insert into employee values (1, 'devkuma', 3);
insert into employee values (2, 'kimkc', 4);
insert into employee values (3, 'araikuma', 5);
insert into employee values (4, 'happykuma', 3);
insert into employee values (5, 'raccoon', 0);

sqlite> insert into employee values (1, 'devkuma', 3);
sqlite> insert into employee values (2, 'kimkc', 4);
sqlite> insert into employee values (3, 'araikuma', 5);
sqlite> insert into employee values (4, 'happykuma', 3);
sqlite> insert into employee values (5, 'raccoon', 0);
sqlite> 

The employee table contains an identifier in id, an employee name in name, and the identifier of that employee’s manager in boss_id. Managers are stored in the same table, so each boss_id refers to another row’s id.

Use a self join to retrieve each manager’s name. The following query applies a left outer join to two aliases of employee.

select staff.id, staff.name, boss.name from employee staff
  left outer join employee boss
  on staff.boss_id = boss.id;

sqlite> select staff.id, staff.name, boss.name from employee staff
   ...>   left outer join employee boss
   ...>   on staff.boss_id = boss.id;
id          name        name      
----------  ----------  ----------
1           devkuma     araikuma  
2           kimkc       happykuma 
3           araikuma    raccoon   
4           happykuma   araikuma  
5           raccoon               
sqlite> 

You can assign an alias to a result column with AS. See Assigning Aliases to Result Columns (AS Clause) for details.

select staff.id, staff.name, boss.name as bossname
  from employee staff
  left outer join employee boss
  on staff.boss_id = boss.id;

sqlite> select staff.id, staff.name, boss.name as bossname
   ...>   from employee staff
   ...>   left outer join employee boss
   ...>   on staff.boss_id = boss.id;
id          name        bossname  
----------  ----------  ----------
1           devkuma     araikuma  
2           kimkc       happykuma 
3           araikuma    raccoon   
4           happykuma   araikuma  
5           raccoon               
sqlite> 

This example uses an outer join, but the same self-join technique also applies to an inner join.