# Left outer join embedded mak:lists

Currently each mak:list is generating a query. If two mak:.lists are embedded, they generate two queries whose results are then combined (grouped) inside Makumba (i.e. not in the DB, so this is a bit of a performance penalty)

    <mak:list from="A a, B b" ...>                -----> SELECT ... FROM A a, B b
    	<mak:list from="C c, D d" ...>        -----> SELECT ... FROM A a, B b, C c, D d
    	...
    	</mak:list>
    </mak:list>

If the two queries are joined simply (i.e. if only the second query above would be used), the results of the outer mak:list (a,b) that have no corresponding results in the inner mak:list (c or d) will not show up. For them to show up, a left outer join is needed. Such a join was not possible in mysql before. Once the left outer join is possible, a single-query implementation of embedded mak:lists should be possible, but Makumba's code still uses the simpler technique.
Another issue that introduces more queries is the join over a nullable pointer.

    <mak:list from="A a" ...>                            -----------> SELECT ... FROM A a
    	<mak:value expr="a.nullablePtr.field" >      -----------> SELECT a.nullablePtr.field FROM A a
    </mak:list>

If only the second query would be executed, the a's that have nullablePtr null will not show up. That's why the nullable field needs a query of its own. (So the actual total number of queries is

    	numberOfMakLists+ numberOfNullablePointersUsed

Potential advantages of the left join:

- may simplify the iteration code
- higher performance by elimination of resultset grouping
- less database hits
- no risk for data inconsistency due to changes made to the db in between queries

Brief:

- think how the left outer join would look like. Probably in the example both C and D will need to be joined so? Or just C??? Look at DB theory.

-     SELECT ... FROM A a JOIN B b LEFT JOIN C c LEFT JOIN D d

- think how the present grouping will be taken away, and how mak:list can be re-implemented to loop through the outer-join query results instead of the former org.makumba.view.Grouper
- consider how to use left join for nullable pointers (straightforward I guess)
- consider the problem of DISTINCT. If one query is used, it will force all mak:lists to DISTINCT. is this acceptable? Currently mak:list distinct="true" is not implemented anyway, but do consider how this would affect it.