subquery in SELECT list vs join
580950Jun 9 2007 — edited Jun 11 2007If I'm not mistaken, both queries below are functionally the same. Both return all rows from table1 and only show a value for table2.F7 if the first 4 characters of F7 equal F2.
My thoughts are this... It's nice that a subquery can be used in a select list, but it looks like, with all the sql I've written which uses that approach, I could have just as well used the join approach. Is this always true? Can someone show me as example where using a subquery in the select list is hands-down the better approach?
SELECT table1.F2,
(SELECT table2.F7 FROM table2 WHERE substr(table2.F7,1,4) = table1.F2)
FROM table1
SELECT table1.F2, table2.F7
FROM table1, table2
WHERE table1.F2 = substr(table2.F7,1,4)(+)
Let me add that, in my top example, multiple rows will never be returned by the subquery. If that were the case, then the join approach would be the only option.
Message was edited by:
Nick2012