[sqlite] Query flattening for left joins involving subqueries on the right-hand side

[Clemens Ladisch]

Kirill M?ller wrote:
> On 25.11.2015 16:32, Clemens Ladisch wrote:
>> Kirill M?ller wrote:
>>> For a left join with a subquery on the right-hand side, that subquery
>>> doesn't seem to be flattened.
>>
>> This is rule 3 of <http://www.sqlite.org/optoverview.html#flattening>.
>
> I wonder if this rule might be relaxed a bit.

Only if you relax your requirement that the results must be correct.


In the general case, a left outer join can be rewritten like this:

  SELECT ... FROM A JOIN B ON ...
  UNION ALL
  SELECT ... FROM A WHERE NOT EXISTS (look up in B)

This query would be more likely to be flattenable, but also be slower.


Regards,
Clemens