>> 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 ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ------------------------------------ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
