>> select r.id,
>> (select a.node from nodes a where r.node_id = a.id) node,
>> route_index,
>> (select b.node from nodes b where r.link_id = b.id) link_node,
>> (select c.quality_label from route_qualities c where r.quality
>> = c.QUALITY)
>> from routes r
>> where exists(select * from nodes a where r.node_id = a.id) and
>> exists(select * from nodes b where r.link_id = b.id) and
>> exists(select * from route_qualities c where r.quality =
>> c.QUALITY)
>> order by 2, 3;
>
>Why apparent duplication of the "where" clauses? Everything from
>"select r.id" to "from routes r" makes sense to me - I don't understand
>the reason for the whole "where exists" section.

Simple Daniel. When using [INNER] JOIN, you eliminate all rows of ROUTES
that does not match a row in NODES or ROUTE_QUALITIES. With subselects
without EXISTS, even rows where node_id IS NULL will be returned.
Without EXISTS, the subselects would be equal to LEFT JOIN.

>If subselects don't offer a performance advantage, and since I find them
>far more confusing than join syntax - I guess I'll forget about them
>until I have a problem they're designed to solve.

Keeping things simple is a good idea. I very rarely use subselects
myself, I more often use CTEs (common table expressions) or EXECUTE
BLOCK to simplify (or make possible or quicker) complex queries.

Set