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 some of the more complex 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:
1 2 3 4 5
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:
1 2 3 4 5
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:
1 2 3 4
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'
- 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.