Subqueries in 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
WHERE REGION_ID =
(SELECT REGION_ID FROM COUNTRIES
WHERE COUNTRY_NAME ='Canada');

REGION_NAME
-------------------------
Americas

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
WHERE REGION_ID =
(SELECT REGION_ID FROM COUNTRIES );
(SELECT REGION_ID FROM COUNTRIES )
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

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
WHERE REGION_ID in
(SELECT REGION_ID FROM COUNTRIES) ;

REGION_NAME
-------------------------
Europe
Americas
Asia
Middle East and Africa

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

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

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,
manager_id,
department_id
FROM employees
WHERE (manager_id,department_id) IN
(SELECT manager_id,department_id FROM employees WHERE employee_id = 107
) ;

EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
----------- ---------- -------------
104 103 60
105 103 60
106 103 60
107 103 60

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,
manager_id,
department_id
FROM employees
WHERE manager_id IN
(SELECT manager_id FROM employees WHERE employee_id = 107
)
AND department_id IN
(SELECT department_id FROM employees WHERE employee_id = 107
) ;

EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
----------- ---------- -------------
104 103 60
105 103 60
106 103 60
107 103 60

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
WHERE department_id IN (10,20,30,40);

DEPARTMENT_NAME
------------------------------
Administration
Marketing
Purchasing
Human Resources

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,
first_name,
last_name,
(
case
when department_id =
(SELECT department_id FROM departments WHERE location_id=1800
)
then 'Toronto'
else 'others'
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

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
FROM DEPARTMENTS DEPT
WHERE EXISTS
(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.

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

Example:

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

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) [ = , != , > , =, , =, <= ] 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

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
WHERE e.employee_id NOT IN (SELECT NVL(m.manager_id,0)
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
…… ……..

Advertisements

One thought on “Subqueries in Oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s