Oracle SQL Subselect Statements

Posted on Mar 25, 2006 in Technical Tips | 0 comments



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.
Bookmark and Share

Related Information:

  1. FreeQL – Free Oracle SQL Select Statements
    M&S Consulting has begun a...
  2. Oracle APEX Tutorial 4 – Form Layout – Part 1 – Video Training
    APEX gives you a lot...
  3. Oracle DBMS_SCHEDULER vs DBMS_JOB (Create, Run, Monitor, Remove)
     DBMS_SCHEDULER is a newer,...
  4. Oracle TO_CHAR Function – SQL Syntax Examples (Most With Dates, TO_DATE)
    This article provides common examples...

Leave a Reply