[Svip]

Except, that's potentially super slow if the optimiser does not realise what to do. In its default state, it will make two table look ups for each row in table a. So that's 1+N*2 look ups compared to 3 look ups in my example.

For little data, that's probably fine, but for a big database, it will be slow. However, the optimiser may be able to handle that? I know Sybase's and MSSQL's had trouble with it, but I've heard Postgres' might be able to.

[sixbrx]

First, correctness first. Second, correlated subqueries in selects really aren't noticably slow at all in my experience (Postgres, Oracle) when they're really needed, meaning alternatives are as slow or slower. Especially compared to the alternative of actually doing a cartesian product across independent children of any size. I don't think the optimizer could help much with avoiding that cartesian product to be actually realized, either - the grouping after that join is going to be a big sort in general with results that depend on the distribution of child values on a row-by-row basis. But in any case it would give the wrong answers for both children regardless of speed and memory used!