Hi, this is normal sql behavior if you do not need to implicit INNER JOIN condition then move filter to left join itself in your situation: instead “where mytable.id is not null” write “ AND mytable.id is not null”
... select aux.n, mytable.id from aux LEFT JOIN mytable on aux.n between mytable.id and mytable.id +1 and mytable.id is not null ... regards, Karol Bieniaszewski From: Germán Balbi bal...@yahoo.com [firebird-support] Sent: Monday, October 2, 2017 7:37 PM To: Firebird-support Subject: [firebird-support] CTE Recursive left join problem Hello everyone I'm having a problem, and I do not understand why. I have a structure similar to:with recursive aux as ( select 0 as n from rdb$database union all select aux.n + 1 from aux where aux.n <= 20 ) select aux.n, mytable.id from aux LEFT JOIN mytable on aux.n between mytable.id and mytable.id +1 where mytable.id is not null Where the left join behaves as join. Any condition that I put in the clause WHERE referred to mytable, converts the LEFT JOIN into JOIN
