SQLite SELF-JOIN clause

SQLite SELF-JOIN allows you to join a table to itself. It is a special JOIN type in SQLite.

The SELF-JOIN is facilitated by means of either a SQLite INNER JOIN or SQLite LEFT JOIN clause. A SELF-JOIN is performed in order to join rows from a table with other rows in the same table.

Example:

SELECT name AS 'manager',
       name AS 'report' 
FROM staff e
INNER JOIN staff m ON m.id = e.manager
;

SQLite SELF-JOIN clauses are generally used for parent-child relationships derived from a single table. Another general use of SELF-JOINs are running totals.