I get the same number of read records. It does not seem to improve the performance. You are right, my example is too simplified.

My real T2 table is a complex query. It returns few records, but it reads thousands of records to provide the result. For this reason, I need to avoid that Firebird evaluates it for each T1 record.

My problem is: I have 2 independent SQL consults. I need that Firebird returns me the two CTEs in only one result table.

What I would do programmatically would be:

1) Calculate T1
2) Calculate T2
3) Loop in T1, joining a T2 row if T1.ID=T2.ID


In the above algorithm, I only do one query for T1, and one query for T2.
Using LEFT JOIN in firebird over two CTEs, I see that Firebird evaluates T2 for each T1 record.

How can rewrite the SQL to get a join table over two independent sql consults without penalize the performance?

Thank you