SQLite Forum

Breadth-first graph traversal
Login
As far as I can tell the <code>SEARCH … BY</code> construction was used in DB2 and copied by Oracle.  I can't find it in any other implementation.  SQL Server instead implements breadth-first searches using a construction <code>CREATE CLUSTERED INDEX …</code>.

It's possible that <code>SEARCH … BY</code> was introduced in SQL:2016.  However, copies of that (now outdated) specification are expensive and can't be reproduced, so documenting code written to that standard is awkward.

SQLite, PostgreSQL, MySQL, and some other implementations of SQL, allow breadth-first graph traversal using a recursive Common Table Expression.  You can see how it's done here

<https://sqlite.org/lang_with.html#controlling_depth_first_versus_breadth_first_search_of_a_tree_using_order_by>

However if you're not familiar with CTEs I suggest you start at the top of the page and work down, because they're one of the most complicated things SQLite does.