It is becoming more common that I find myself surprised by self-proclaimed, seasoned SQL developers who do not even understand some of the basic "power" that is possible with an Oracle SQL SELECT statement.  I am not talking about analytical functions; I have seen SQL developers lost when it comes to simple subselect statements (a "SELECT" inside a "SELECT"). Most people understand that they can nest a SELECT statement inside the WHERE clause of another SELECT statement, but many do not realize that it is possible inside the FROM clause (perhaps, my most common use) and also from the SELECT clause.  Following are some subselect examples in action.

1) Inside the WHERE clause:

SELECT e.*
  FROM scott.emp e
 WHERE e.deptno IN (SELECT d.deptno
                    FROM scott.dept d
                   WHERE d.dname = 'SALES')

2) Inside the FROM clause:

SELECT e.*
  FROM scott.emp e, (SELECT d.deptno
                       FROM scott.dept d
                      WHERE d.dname = 'SALES') d
 WHERE e.deptno = d.deptno

3) Inside the SELECT clause:

SELECT e.*, (SELECT 'test_value'
               FROM DUAL) test_col
  FROM scott.emp e, scott.dept d
 WHERE e.deptno = d.deptno AND d.dname = 'SALES'
 

Notes:
- As you can see, all of these SELECT statements return the same result set (#3, of course, has an extra column called "test_col").
- Which of these approaches would you use when looking for the employees in the sales department?
- In fact, if you can provide a quantitative analysis of any advantages that each approach has over the others (if any), not only may you impress us, you could also earn yourself a job offer.