Inserting Sub-Queries in SELECT Statements in Oracle

This is the second article in a series concentrating on working with sub-queries in Oracle. My previous article (first in this series) explained the “dual” table in detail and gave a few practical examples of sub-queries. In this article, I shall introduce sub-queries at various interesting places within the same SELECT statement.

All the queries in this series have been tested only with Oracle 10g (V10.2). A few of the features may not be available in previous versions. For a detailed listing of features, please go through the Oracle documentation.

Sub-Queries with multiple columns in Oracle

Let us try to work with the following query:

SELECT
*
FROM emp
WHERE (sal,mgr) = (3000,7566)

The above would never work and results in giving you the “invalid relational operator” error. This is because you can compare only one value at a time and not more than one. The following is a small trick to overcome the problem:

SELECT
*
FROM emp
WHERE (sal,mgr) = (SELECT 3000,7566 FROM dual)

I just made both of those two values part of the sub-query and it works fine!  Both of those values are not from any table, so I used “dual.”  Now you can observe the power of the “dual” table.  If you want to learn more about “dual,” please refer to my first article in this series.

Let us work with a more practical sub-query rather than with a “dual” table as follows:

SELECT
*
FROM emp
WHERE (sal,mgr) =
(SELECT sal,mgr FROM emp
             WHERE sal = (SELECT MIN(sal) FROM EMP
                          WHERE sal > (SELECT MIN(sal) FROM
emp)))

The above returns all the employees who are earning the same salary and working under the same manager of the employee earning the second least salary!

{mospagebreak title=Sub-Queries returning single and multiple values in Oracle}

Let us consider the following statement:

SELECT
*
FROM emp
WHERE sal = 800

When you execute the above statement, the condition in the WHERE clause works with only a single value.  Let us rewrite the above statement with a plain sub-query as follows:

SELECT
*
FROM emp
WHERE sal = (SELECT MIN(sal) FROM emp)

From the above statement you can understand that the sub-query returns one and only one value (or single value) and the query works fine.

If we would like to work with more than one value in a condition (of equality), you may need to work something like the following:

SELECT
*
FROM emp
WHERE empno IN (7902,7788)

Let us frame the above with a sub-query as follows:

SELECT

*

FROM emp

WHERE empno IN

(SELECT empno FROM emp

       WHERE sal = (SELECT MAX(sal) FROM EMP

                    WHERE sal < (SELECT MAX(sal) FROM emp)))

The above query gives us all the employees earning the second highest salary!  In the above case, the second level sub-query returns more than one value (or two employee numbers).  Therefore, I provided the IN operator as part of the condition.  If you replace the IN with “=” in the above query, it will return with an error stating “single row sub-query returns more than one row.”  When you receive such an error, try to replace “=” with “IN” wherever appropriate.

{mospagebreak title=Sub-Queries as part of the BETWEEN operator in Oracle}

BETWEEN is a unique operator in Oracle.  Let me show you a small example of this:

SELECT
*
FROM emp
WHERE sal BETWEEN 1000 and 3000

It is very easy to understand the above query as it returns all the employees who earn salaries between 1000 and 3000 (both inclusive). Let us rewrite the above query as follows:

SELECT
*
FROM emp
WHERE sal BETWEEN
(SELECT 1000 FROM dual) AND 3000

I just replaced 1000 with a simple sub-query using “dual.” If you are new to the “dual” table, please refer to my first article in this series. Let us work a bit more practically as follows:

SELECT
*
FROM emp
WHERE sal BETWEEN 
    (SELECT MIN(sal) FROM emp) and 2000

We can even work a bit differently.  Let us go through the following query:

SELECT
Grade
FROM salgrade
WHERE 
    (SELECT MAX(sal) FROM emp)
    BETWEEN losal AND hisal

The above gives you the salary grade of the highest salary!

{mospagebreak title=Derived tables (or inline views) with Sub-Queries in Oracle}

Can we write sub-queries in (or as part of) the FROM clause? The answer is YES and you can use it very efficiently in some scenarios. 

Let us consider the following statement:

SELECT empno,ename,sal,sal*12 AS AnnSal
FROM emp
WHERE AnnSal > 30000

If I execute the above statement, it will result in an error saying that “AnnSal” is an “Invalid Identifier.”  The reason is that “AnnSal” is not a column existing in the table “emp.”  It is simply an alias (or some reasonable name). We are not allowed to work with a column alias in any of the conditions present in WHERE clause.

Let us modify the above statement to make it work. Try the following now:

SELECT empno,ename,sal,AnnSal
FROM (
      SELECT empno,ename,sal,sal*12 AS AnnSal
      FROM emp
      )
WHERE AnnSal > 30000

The above statement is totally different from the previous one. Within the above statement, the outer query doesn’t rely on any specific physical table. The output (or result set) of the inner query is considered as a table for the outer query! The inner query is very similar to that of a view which doesn’t have any physical view name, and it gets created and destroyed on the fly.

So, according to the inner query, it retrieves four columns (empno, ename, sal, AnnSal). The outer query can work with all four columns as if they are directly from a physical table. 

As you are trying to define/derive your own table of information from an existing physical table, you call it as a derived table (or inline view). Even the derived tables can be nested to any number of levels with further sub-derived tables as part of FROM clauses.

{mospagebreak title=Sub-Queries with CASE structure in Oracle SELECT statements}

Now let us go through an interesting topic on CASE structure.  Let us see how a CASE structure works.  Consider the following statement:

SELECT
  empno,
  ename,
  CASE job
    WHEN ‘SALESMAN’ THEN ‘SALES’
    WHEN ‘MANAGER’ THEN ‘MGMT’
    ELSE job
  END AS jobfunction
  sal
FROM emp

When the above query is executed, it returns four columns (empno, ename, jobfunction, sal). The only eye-catching issue from the above is the following structure:

  CASE job
    WHEN ‘SALESMAN’ THEN ‘SALES’
    WHEN ‘MANAGER’ THEN ‘MGMT’
    ELSE job
  END AS jobfunction

The above dictates something very similar to the following:

  • If the value of “job” is “salesman” return “sales.”
  • If the above condition fails and if the value of “job” is “manager” return “mgmt.”
  • If both of the above conditions fail then return the same value of “job.”
  • All the values must be returned in a new column with the heading “jobfunction.”

You need to observe that I specified the column (job) along with CASE. The conditions of WHEN work with the values available within that column. We can even work with different relational (and SQL) operators within the WHEN condition as shown in the following example:

SELECT
  empno,
  ename,
  CASE 
    WHEN comm IS NULL OR comm=0 THEN ‘-NA-‘
    ELSE TO_CHAR(comm)
  END AS comm,
  sal
FROM emp

In the above case, the conditions are directly used within the WHEN statement and you need not specify any column with the CASE.

Finally, you can even work with sub-queries within the CASE structure as follows:

SELECT
  empno,
  ename,
  CASE 
    WHEN sal >= (SELECT avg(sal) FROM emp) THEN ‘HIGH’
    ELSE ‘LOW’
  END AS pay,
  sal
FROM emp

The above returns a new column named “pay,” which contains either “HIGH” or “LOW” based on their salary compared to the average salary of all employees.

{mospagebreak title=Sub-Queries as (or part of) columns in Oracle SELECT statements}

Before getting into sub-queries as part of columns, let us look at the following small query:

SELECT
    MAX(sal) AS highest,
    MIN(sal) AS least,
    COUN(*) AS employees,
    SUM(sal) AS total
FROM emp

Everyone can easily understand that the above query returns only one row containing four values of aggregation. Let us rewrite the above query using sub-queries to get the same output.

SELECT
  (SELECT MAX(sal) FROM emp) AS highest,
  (SELECT MIN(sal) FROM emp) AS least,
  (SELECT COUNT(*) FROM emp) AS employees,
  (SELECT SUM(sal) FROM emp) AS total
FROM dual

You can observe that I replaced all aggregate functions with sub-queries!  Another important issue to concentrate on in the above query is the “dual” table.  As the sub-queries in the above statement are working individually by themselves, I need not work with any table and thus I used the “dual” table.  If you want to learn more about the “dual” table, please go through my first article in this same series.

Now, let us look at an interesting query which deals with sub-queries at both the column level and the CASE level.  The following is the query:

SELECT
  empno,
  ename,
  sal AS salary,
  ROUND((sal -(SELECT AVG(sal) FROM emp)),2) AS avgcompare,
  CASE 
    WHEN sal >= (SELECT AVG(sal) FROM emp) THEN ‘HIGH’
    ELSE ‘LOW’
  END AS paying
FROM emp

The following is the sample output of the above query:

EMPNO   ENAME      SALARY   AVGCOMPARE   PAYING

——- ———- ——– ———— ——

7839    KING       5000     2926.79      HIGH  

7698    BLAKE      2850     776.79       HIGH  

.

.

7654    MARTIN     1250     -823.21      LOW   

7499    ALLEN      1600     -473.21      LOW   

.

Any bugs, doubts, suggestions, feedback etc. are highly appreciated at http://jagchat.spaces.live.com

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye