What is Subquery? | Types of Subqueries with examples

In my previous article I have given multiple SQL queries as well as given details about SQL interview questions as well. In this article I would like to focus on  Subquery with examples. The subqueries are important part of SQL and correlated subqueries are now a day used to add multiple important logics related to SQL in the application.

What we will see in this article ?

1.What is subquery?

2.Types of Subqueries

What is Subquery with its multiple types :

Subquery is query within query. The output of outer query is assigned to the column which is used in where condition of outer query. The subquery output is returning only one output value and based on that output value the outer query is executed. Subqueries are used in various real life scenarios like report development, Application logic development,Performance tuning of query.

“Subquery is Query within a query in which the value of inner query is assigned to the

outer query and outer query is executed…”

• Types of Subqueries:

1.Single Row Subquery

2.Multirow Subquery

3.Correlated Subquery

4.Nested SubQueries

5.Scalar Subqueries

CLICK HERE TO GET 20 MOST IMPORTANT QUESTIONS FOR TECH MAHINDRA

1.Single Row Subquery with real life example:

When Query within a query or subquery returns only one row then these type of queries are called as single row subqueries. Single row comparison operator is used to compare the two queries.The most widely used operator for single row subquery is Equal to operator(=).Here We need to make sure that the query is returning only one value.Here we are able to use Max,Min,AVG like fuctions which will return only one value.

Real life Scenario:

Write a query to find highest salaried Employee from Employee table.

Select Employee_No,Employee_Name from Employee

where Salary=(Select max(Salary) from Employee);

In above query the inner query is executed 1st then the value of inner-query is assigned to the outer query.

Step 1:Executed inner query :(Select max(Salary) from Employee);

consider Output is:50000

Step 2: 

Select Employee_No,Employee_Name from Employee

where Salary=50000;

2.Multi Row Subqueries with Real life Scenario:

If the output of Inner query count is more than 1 then these subqueries are called as multi row subqueries.We need to use ANY,IN,EXIST Operator in outer query of multi row subqueries because output of outer query is not a single value.

Real Life Scenario:

Fetch the list of Employees which is assigned to ‘OBIEE’ and ‘Oracle’ Department.

Select Employee_No,Employee_Name from Employee

where Department_Name in

(Select Department_Name from Employee where Department_name in (‘OBIEE’,Oracle’));

The Query is executed in following Steps:

Step 1:

Inner Query Execution:

Select Department_Name from Employee where Department_name in (‘OBIEE’,Oracle’);

Consider the output is ‘OBIEE’ and ‘ORACLE’

Step 2:

Outer Query Execution:

Select Employee_No,Employee_Name from Employee

where Department_Name in (‘OBIEE’,ORACLE’);

CLICK HERE TO GET 20 MOST IMPORTANT INTERVIEW QUESTIONS FOR IBM

3.Correlated Subquery :

Correlated Query is nothing but the subquery whose output is depending on the inner query used in that query.Correlated query is the query which is executed after the outer query is executed.The outer query is always dependent on inner query.The approach of the correlated subquery is bit different than normal subqueries.In normal subqueries the inner queries are executed first and then the outer query is executed but in Correlated Subquery outer query is always dependent on inner query so first outer query is executed then inner query is executed.Correlated Subqueries always uses operator like Exist,Not Exist,IN,Not IN.

“Correlated Queries are also called as Synchronized queries…”

Execution Steps of Correlated Subqueries:

1.Executes the outer Query

2.For Each row of outer query inner subquery is executed once

3.The result of correlated subquery determines whether the fetched row should be the part of our output results

4.The Process is Repeated for all Rows

“It is not recommended to use Correlated Subqueries as it slows down the performance”

Real Life Example:

Fetch the Employees who have not assigned a single department.

Select * from Employee E where Not exist

(Select Department_no From Department D where E.Employee_id=D.Employee_ID);

Execution of query:

Step 1:

Select * from Employee E ;

It will fetch the all employees

Step 2:

The First Record of the Employee second query is executed and output is given to first query.

(Select Department_no From Department D where E.Employee_id=D.Employee_ID);

Step 3:

Step 2 is repeated until and unless all output is been fetched.

Nested Subqueries:

The Subqueries are called as nested subqueries when another subquery is used in where or having condition of the Outer Query.The Execution of Nested suubquery always follows bottom up approach.

Real Life Example:

Select * from Employee

where Employee_No Exist

(Select * from Employee

where Department_Name=

(Select Department_Name from Employee where Department_Name=’OBIEE’));

Execution of Query:

Step 1:

Executed Bottom query:

Select Department_Name from Employee where Department_Name=’OBIEE’;

Step 2:

Executed The Second Query which is above bottom query:

Select * from Employee

where Department_Name=’OBIEE’;

Step 3:

Excecuted the Top Query

Select * from Employee

where Employee_No Exist

(Select * from Employee

where Department_Name=’OBIEE’);

4.Scalar Sub-queries :

Definition of Scalar Subquery:

A scalar sub-query expression is a sub-query that returns exactly one column value from one row. What if the oracle failed to return scalar sub-query?There are some specific conditions.

Usages of Scalar Query :

1.The scalar sub-queries are most used for removing the outer joins.

2.If user want to aggregate multiple tables then scalar sub-queries are useful.

3.Table insertion based on other table values.

Real Life Example:

If user want to find out the Department_name and Number_of_Departments using scalar query you can use following expression :

Select D.Department_name(Select Count(E.Department_name) From Employee E Where E.Department_no=D.Department_no;

Hope you will get idea about the subqueries in SQL.If you have any issues related to this or any information needed kindly comment.If you want this article in PDF format kindly comment here…

HOME