SQLite Self-Join
Summary: in this tutorial, you will learn about a special type of join called SQLite self-join that allows you to join table to itself.
Note that you should be familiar with INNER JOIN
and LEFT JOIN
clauses before going forward with this tutorial.
Introduction to SQLite self-join
The self-join is a special kind of joins that allow you to join a table to itself using either LEFT JOIN
or INNER JOIN
clause. You use self-join to create a result set that joins the rows with the other rows within the same table.
Because you cannot refer to the same table more than one in a query, you need to use a table alias to assign the table a different name when you use self-join.
The self-join compares values of the same or different columns in the same table. Only one table is involved in the self-join.
You often use self-join to query parents/child relationship stored in a table or to obtain running totals.
SQLite self-join examples
We will use the employees
table in the sample database for demonstration.
The employees
table stores not only employee data but also organizational data. The ReportsTo
column specifies the reporting relationship between employees.
If an employee reports to a manager, the value of the ReportsTo
column of the employee’s row is equal to the value of the EmployeeId
column of the manager’s row. In case an employee does not report to anyone, the ReportsTo
column is NULL
.
To get the information on who is the direct report of whom, you use the following statement:
SELECT m.firstname || ' ' || m.lastname AS 'Manager',
e.firstname || ' ' || e.lastname AS 'Direct report'
FROM employees e
INNER JOIN employees m ON m.employeeid = e.reportsto
ORDER BY manager;
The statement used the INNER JOIN
clause to join the employees
to itself. The employees
table has two roles: employees and managers.
Because we used the INNER JOIN
clause to join the employees
table to itself, the result set does not have the row whose manager column contains a NULL
value.
Note that the concatenation operator ||
concatenates multiple strings into a single string. In the example, we use the concatenation operator to from the full names of the employees by concatenating the first name, space, and last name.
In case you want to query the CEO who does not report to anyone, you need to change the INNER JOIN
clause to LEFT JOIN
clause in the query above.
Andrew Adams
is the CEO because he does not report anyone.
You can use the self-join technique to find the employees located in the same city as the following query:
SELECT DISTINCT
e1.city,
e1.firstName || ' ' || e1.lastname AS fullname
FROM
employees e1
INNER JOIN employees e2 ON e2.city = e1.city
AND (e1.firstname <> e2.firstname AND e1.lastname <> e2.lastname)
ORDER BY
e1.city;
The join condition has two expressions:
e1.city = e2.city
to make sure that both employees located in the same citye.firstname <> e2.firstname AND e1.lastname <> e2.lastname
to ensure thate1
ande2
are not the same employee with the assumption that there aren’t employees who have the same first name and last name.
In this tutorial, we have shown you how to use the SQLite self-join technique to join a table to itself.