Oracle SQL Subselect Statements

AshokTechnical Tips1 Comment

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'

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.

One Comment on “Oracle SQL Subselect Statements”

  1. Hi,
    One Point to the second method (maybe all I am not hundert procent sure).
    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
    I see hear one big disadventage. The perofrmacne is deeply going down when you create this kind of SQL-Query. It is definitly better store data for every one sub-query in temporary table and then join them. It could be even 100 times faster.

    Best Regards

Leave a Reply to Tadeusz Cancel reply

Your email address will not be published. Required fields are marked *