[OSM-dev] C++ implementation of the API

Joachim Zobel jz-2008 at heute-morgen.de
Fri May 30 19:48:11 BST 2008


Am Freitag, den 30.05.2008, 13:37 +0200 schrieb Steven te Brinke:
> SELECT *
> FROM current_war_tags
> WHERE id IN (
>     SELECT *
>     FROM current_ways
>     WHERE tile IN (...)
>        AND latitude BETWEEN ... AND ...
>        AND longitude BETWEEN ... AND ...
> )
> ORDER BY id;
> 
> However, I do not know how the db handles subqueries. 

As Tom H. said, badly. In a little more Detail:

All subqueries with IN are rewritten to EXISTS an executed for each row
in the main table.

Subqueries with EXIST work as expected.

Subqeries in the FROM clause (that can not be correlated) are executed
only once.

SELECT wt.*
FROM current_war_tags wt
JOIN (
    SELECT *
    FROM current_ways
    WHERE tile IN (...)
       AND latitude BETWEEN ... AND ...
       AND longitude BETWEEN ... AND ...
) w
ON w.id=wt.id
ORDER BY id;

should do what you want.

Non-correlated subselects returning values should also work well.

A short general remark on the attitude towards the database: Love her or
leave her (also she may not be perfect).

Sincerely,
Joachim






More information about the dev mailing list