Consider lifting up correlated scalar subqueries to JOINs where possible, for better query performance

For example, this would transform the following:

```sql
SELECT a.col1, (SELECT b.col2 FROM b WHERE b.x = a.x)
FROM a;
```

... to the following:

```sql
SELECT a.col1, b.col2
FROM a LEFT JOIN b ON b.x = a.x;
```

Notes:
* Check that this makes sense for other databases as well (likely)
* There's a slight semantic difference between the two: the former errors when the scalar subquery returns more than one row, but the latter returns multiple rows (JOIN). We can perform this transformation e.g. only in cases where we're sure that the subquery returns one row (e.g. there's LIMIT 1 inside, or there's a predicate over a uniquely-constrained column).

See https://www.cybertec-postgresql.com/en/subqueries-and-performance-in-postgresql for more details

/cc @laurenz (post author)