SQLite tree structures and queries to get sub-trees

SQLite is wonderful!

Just today I found that SQLite support queries with a recurse to get the inner sub-trees:

CREATE TABLE els ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE ,pid INTEGER DEFAULT NULL REFERENCES els ,name TEXT ); INSERT INTO els (id, pid, name) VALUES (1, NULL, ’top’); INSERT INTO els (id, pid, name) VALUES (2, 1, ‘x2’); INSERT INTO els (id, pid, name) VALUES (3, 1, ‘x1’); INSERT INTO els (id, pid, name) VALUES (4, 3, ‘y2’); INSERT INTO els (id, pid, name) VALUES (5, 3, ‘y1’); INSERT INTO els (id, pid, name) VALUES (6, 1, ‘x3’); INSERT INTO els (id, pid, name) VALUES (7, 6, ‘w2’); INSERT INTO els (id, pid, name) VALUES (8, 6, ‘w1’); INSERT INTO els (id, pid, name) VALUES (9, 7, ‘z1’); INSERT INTO els (id, pid, name) VALUES (10, 7, ‘z2’);

Tree

top
|-x1
| |-y1
| |-y2
|-x2
|-x3
  |-w1
  |-w2
    |-z1
    |-z2

We can write SQL query to get all children of element x3 (id=6):

WITH RECURSIVE
  els_cte(id,pid,name) AS (
    SELECT id,pid,name
	FROM els
	WHERE pid=6
    UNION ALL
	SELECT x.id,x.pid,x.name
	    FROM els AS x
	    INNER JOIN els_cte AS y ON (x.pid=y.id)
  )
SELECT id,pid,name
    FROM els_cte;

Results are: