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.

Related Information:

1. Oracle Autoincrement

   Unlike Oracle, other less expensive and less robust databases make autoincrementing a simple task…typically a couple of clicks in some...

2. FreeQL - Free Oracle SQL Select Statements

   M&S Consulting has begun a revolutionary service where we will write Oracle SQL statements for FREE. We have named this...

3. TOAD SQL Editor Useful Shortcuts

   SHIFT-F9 This will run only the SQL that your cursor is on. You do not need to highlight the line...

4. MySQL and SQL Server - Oracle CONNECT BY PRIOR for Recursive, Hierarchical Data

   Recursive queries are something I have used many, many times over the years to build dynamic, n-tiered hierarchies. Oracle’s CONNECT...

5. Oracle Materialized View DDL

   Are you a TOAD fanatic when it comes to “anything-Oracle”? If so, you probably realized that TOAD will not show...

6. Oracle TO_CHAR Function - SQL Syntax Examples (Most With Dates, TO_DATE)

   This article provides common examples of TO_CHAR using dates....

7. Oracle BAM Sucks, For Now

    Our team has been working with BAM since Oracle took it on (we rolled out BAM reports in 2007).Â...

8. Oracle BAM - SQL to List Users and Roles

   I don’t really like what Oracle did with BAM user/roles and authentication. I believe they took Microsoft (SQL Server) tables...