Oracle Subqueries: oracle

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

1:
2:
3:
4:
5:
6:
7:
8:

Select all 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

1:
2:
3:
4:
5:
6:
7:

Select all 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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

Select all 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) ;

1:
2:
3:
4:

Select all 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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

Select all 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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

Select all 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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:

Select all 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

1:
2:
3:
4:
5:
6:
7:
8:
9:

Select all 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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:

Select all 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.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

Select all 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 ) ;

1:
2:
3:
4:

Select all Open in new window

Example:

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

1:
2:
3:

Select all 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

1:
2:
3:
4:
5:
6:
7:
8:

Select all 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
……                  ……..

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:

Select all Open in new window

Oracle Subqueries

oracle

Oracle Database

About The Author

More Articles From slobaray

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top Oracle Database Experts