Recursive JOIN (Connect By)
Recursive JOIN (Connect By) is a SQL statement in the JOIN Types category. Recursive joins traverse hierarchical data. Different databases have different syntax but achieve the same result. The syntax is -- Oracle: CONNECT BY PRIOR. PostgreSQL: WITH RECURSIVE. SQL Server: WITH cte AS (...).. It returns recursive hierarchy. A typical example: -- Oracle CONNECT BY: SELECT employee_id, name, LEVEL, SYS_CONNECT_BY_PATH(name, '/') AS path FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id; -- PostgreSQL RECURSIVE CTE: WITH RECURSIVE emp_tree AS ( SELECT employee_id, name, 1 AS level, name AS path FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.name, t.level + 1, t.path || '/' || e.name FROM employees e JOIN emp_tree t ON e.manager_id… A close relative is INNER JOIN, which returns only rows where there is a match in BOTH tables. The most common JOIN type. A close relative is LEFT JOIN, which returns ALL rows from the left table and matching rows from the right. Unmatched right rows show NULL.