Oracle Subqueries

Swadhin Ray
CERTIFIED EXPERT
Published:
Subquery in Oracle:

Sub queries are one of advance queries in oracle.
Types of advance queries:
•      Sub Queries
•      Hierarchical Queries
•      Set Operators

Sub queries are know as the query called from another query or another subquery. It can be nested. Rather than having two or more queries to produce a result, we can solve this by combining the two queries, placing one query inside the other query called inner query or sub query.

Few points that help us for using Subqueries:
•      Subqueries should always be enclosed by parentheses.
•      It should be placed on the right side of the comparison condition.
•      If we are performing Top-N analysis then ORDER BY clause is required in the subquery.
•      We should use single-row operators with single-row subqueries and multiple-row operators with multiple-row subqueries.

Syntax:
SELECT column names
                ,(SELECT ….QUERY)
          , (SELECT …QUERY)
FROM table
WHERE x IN
    (SELECT  … FROM ..  WHERE y IN
       ( SELECT …));

In the above syntax you can see that in select we have subquery and in where clause we have nested sub queries as one sub query is calling another subquery.
A query is equivalent within another SQL statement of an expression. It can be called as an expression because it gives a result /value/set of values.
It is basically used to break down the complexity of the codes. It can also be used as SQL code tuning tool which can increase the performance and database access speed.


Types of Subqueries:

Single Row Subquery: The subquery which must return single row or a single element.

Example:
SQL> SELECT REGION_NAME FROM REGIONS
                        2  WHERE REGION_ID =
                        3       (SELECT REGION_ID FROM COUNTRIES
                        4             WHERE COUNTRY_NAME ='Canada');
                      
                      REGION_NAME
                      -------------------------
                      Americas

Open in new window


Now in the above syntax you can see that the query calling the sub query used for an exact match for REGION_ID and returns a single element. For example if we have written like the below code then we shall be getting ORA-01427 because the query calling the sub query is having more than one row which get error out.

Example:
SQL> SELECT REGION_NAME FROM REGIONS
                        2  WHERE REGION_ID =
                        3       (SELECT REGION_ID FROM COUNTRIES );
                           (SELECT REGION_ID FROM COUNTRIES )
                            *
                      ERROR at line 3:
                      ORA-01427: single-row subquery returns more than one row

Open in new window



Multiple Row Subquery: In this type of query returns one or more rows.
Here we will see the query calling the subquery having a membership with it and fetches the correct result.

Example:

.
SQL>  SELECT REGION_NAME FROM REGIONS
                        2      WHERE REGION_ID in
                        3           (SELECT REGION_ID FROM COUNTRIES)  ;
                      
                      REGION_NAME
                      -------------------------
                      Europe
                      Americas
                      Asia
                      Middle East and Africa

Open in new window


So from the above query you can see that multiple rows are returned when a subquery is been called from the first query.

Multiple Column Subquery:  In this type of subquery it selects more than one column and verify it for getting the correct result.
Syntax  for multiple subqueries :
		SELECT column, column, …  FROM  table
                      		WHERE (column, column,…) IN 
                      			(SELECT column,column… FROM table 
                      							WHERE condition) ;

Open in new window


Example:
SQL> SELECT FIRST_NAME,
                        2    LAST_NAME,
                        3    JOB_ID
                        4  FROM EMPLOYEES
                        5  WHERE (EMPLOYEE_ID,DEPARTMENT_ID) IN
                        6    (SELECT EMPLOYEE_ID,DEPARTMENT_ID FROM job_history  );
                      
                      FIRST_NAME           LAST_NAME                 JOB_ID
                      -------------------- ------------------------- ----------
                      Payam                Kaufling                  ST_MAN
                      Jonathon             Taylor                    SA_REP
                      Michael              Hartstein                 MK_MAN

Open in new window


There can be two types of column comparisons like:
•      Pair wise comparisons
•      Non pair wise comparisons

Pair wise comparisons:

Here there query is written in such a way that the two columns are checked simultaneously within a single query.

Example:
	SQL> SELECT employee_id,
                        2    manager_id,
                        3    department_id
                        4  FROM employees
                        5  WHERE (manager_id,department_id) IN
                        6    (SELECT manager_id,department_id FROM employees WHERE employee_id = 107
                        7    ) ;
                      
                      EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
                      ----------- ---------- -------------
                              104        103            60
                              105        103            60
                              106        103            60
                              107        103            60 

Open in new window

 
Non pair wise comparisons:
Here there query is written in such a way that each column are checked individually with two queries.
	
                      SQL> SELECT employee_id,
                        2    manager_id,
                        3    department_id
                        4  FROM employees
                        5  WHERE manager_id IN
                        6    (SELECT manager_id FROM employees WHERE employee_id = 107
                        7    )
                        8  AND department_id IN
                        9    (SELECT department_id FROM employees WHERE employee_id = 107
                       10    ) ;
                      
                      EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
                      ----------- ---------- -------------
                              104        103            60
                              105        103            60
                              106        103            60
                              107        103            60

Open in new window


Other types of subqueries are Regular and Correlated:


Regular Subqueries:  These can be defined as having no relationship between the calling query and the subquery. Here in the below example “IN” is used for literal values only which is constructed on the subquery first as an expression list so the whole result of the expression or subquery is applied to the calling query. It is also called as normal or regular or standard subqueries.

For example:
SQL> SELECT DEPARTMENT_NAME FROM DEPARTMENTS
                        2      WHERE department_id IN (10,20,30,40);
                      
                      DEPARTMENT_NAME
                      ------------------------------
                      Administration
                      Marketing
                      Purchasing
                      Human Resources

Open in new window


Scalar Subquery Expressions:

Subquery that returns exactly one column value from one row.
The subquery which return a scalar values or single value within the SQL statements are know to be Scalar subqueries.It is also know as selecting a select inside the statement. It can use all types of clauses but except GROUP BY clause and we can also use conditions/expressions of scalar subqueries as a part of DECODE and CASE.


Example:

 
SQL> select employee_id,
                        2    first_name,
                        3    last_name,
                        4    (
                        5    case
                        6      when department_id =
                        7        (SELECT department_id FROM departments WHERE location_id=1800
                        8        )
                        9      then 'Toronto'
                       10      else 'others'
                       11    END) location FROM employees ;
                      
                      EMPLOYEE_ID FIRST_NAME           LAST_NAME                 LOCATIO
                      ----------- -------------------- ------------------------- -------
                              100 Steven               King                      others
                              101 Neena                Kochhar                   others
                              102 Lex                  De Haan                   others
                              103 Alexander            Hunold                    others
                              104 Bruce                Ernst                     others
                              105 David                Austin                    others
                              106 Valli                Pataballa                 others
                              107 Diana                Lorentz                   others

Open in new window



Correlated Subqueries: These types of queries have a distinct relationship between the calling query and the subquery. The relationship can be in one or more tables in the calling query using exists clause. In the below example you can see that the department ID is been matched from the subquery to the calling query by using exists clause.
The most important part is the EXIST clause is much faster then IN clause. If we ask why then “IN”   always per executes the expressions in the subquery as what we saw in the above example and in “EXISTS” will actually pass the index value to one of those expression if it is present as per the below example so where it matched the indexes for the department ID between the calling query and the subquery where as “IN” do not do this but executes the entire the subquery first, “IN” is very good to use in literal values and it can also use the indexes when you file is small and if you table data is larger then it’s better to use EXISTS rather than IN.

Example for correlated subquery:

SQL>   SELECT DEPARTMENT_NAME
                        2  FROM DEPARTMENTS DEPT
                        3  WHERE EXISTS
                        4    (SELECT department_id FROM EMPLOYEES WHERE department_id =dept.department_id  );
                      
                      DEPARTMENT_NAME
                      ------------------------------
                      Administration
                      Marketing
                      Purchasing
                      Human Resources
                      Shipping
                      IT
                      Public Relations
                      Sales
                      Executive
                      Finance
                      Accounting
                      
                      11 rows selected.

Open in new window


Correlated UPDATE in subquery:
To update rows in one table based on rows from another table
Syntax:

	
                      UPDATE table1 alias1	
                      	SET column = ( SELECT expression FROM table2 alias2
                      				WHERE alias1.column = alias2.column ) ;

Open in new window

Example:
	
                      UPDATE employee e SET  department_name = (select department_name
                      			FROM departments d WHERE e.department_id = d. department_id);

Open in new window

Similarly Correlated Delete can be used

Conditional Comparisons with Subqueries:
This includes all type of conditional operators used by the calling query to the subquery like [ = , != , > , < , >=, <= ] operators or using “LIKE ” condition , “[NOT ] IN” etc  

Syntax:
(Subquery) [ = , != , > , < , >=, <= ] (Subquery)
(Subquery) LIKE  (Subquery)
(Subquery) [NOT ] IN  (Subquery)
[NOT] EXISTS (Subquery)
(Calling query) BETWEEN (Subquery) AND (Subquery)  : In this the subquery must be single row subquery.
(Subquery) [ = , != , > , < , >=, <= ] ANY  or SOME or ALL (Subquery): this is a multiple row subquery.

NULL values in Subquery:
The below example will shows how null values are been used in a subquery:

SQL> SELECT e.first_name
                        2  FROM employees e
                        3  WHERE e.employee_id NOT IN
                        4    (SELECT m.manager_id FROM employees m
                        5    );
                      
                      -----------------------------
                      no rows selected 

Open in new window


  Syntactically the above query is correct, but one value in the subquery is having a null value and hence the whole subquery returns null as result . Hence this query results/outputs nothing.
To return the correct values we need to write the query as below:
SQL> SELECT e.first_name,e.last_name FROM    employees e
                        2     WHERE  e.employee_id NOT IN (SELECT NVL(m.manager_id,0)
                        3       FROM employees m );
                      
                      FIRST_NAME           LAST_NAME
                      -------------------- -------------------------
                      Clara                Vishney
                      Jason                Mallin
                      Hazel                Philtanker
                      Nanette              Cambrault
                      Alana                Walsh
                      Bruce                Ernst
                      Nandita              Sarchand
                      Elizabeth            Bates
                      Kevin                Feeney
                      Peter                Tucker
                      Curtis               Davies
                      
                      FIRST_NAME           LAST_NAME
                      -------------------- -------------------------
                      Randall              Matos
                      Randall              Perkins
                      Karen                Colmenares
                      ……                  ……..

Open in new window

3
7,321 Views
Swadhin Ray
CERTIFIED EXPERT

Comments (1)

V BSenior Analyst

Commented:
Hi slobaray,

Great article. Voted "Yes".

VB

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.