Subqueries in SQL | Correlated Subquery | Difference Between SubQuery and Correlated Subquery

Subquery :

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 in SQL
Inner and Outer 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


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


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

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…


About admin 111 Articles
Oracle Consultant with Vast Experience in Oracle BI and SQL PLSQL Development


  1. Thank you very much for this wonderful article 🙂

    Just a small suggestion why don’t you create new subsection under Oracle->SQL Tutorials-> Topics..which will help users who are visiting this site first time

    • Sure Sajid…I will create one section named SQL tutorials for userfriendlyness..Thanks for valuable suggestions…:)

  2. It is the excellent and clear way of explaining about it.Even standard books also don’t clarify
    this separation among these sub queries clearly .Your way of explaining is really helping a lot
    at the time of learning the topic ‘find the n Th highest salary of an employee using correlated query’.

    Thank you so much sir….we are waiting for your valuable explanation……………..

    • Sure Surya…I will write article on finding Nth highest salary using correlated query till this weekend…keep visiting site 🙂

  3. Hello admin,fisrt of all congrats for making this page for all of us.
    Pls also share latest pl/sql interview questions answers for 3-4 yr exp which is really helpful for us or suggest from where we can find the same

10 Trackbacks / Pingbacks

  1. SQL Interview Questions For Tech Mahindra | Important SQL Interview Questions
  2. SQL Interview Questions for IBM | Important SQL Interview Questions
  3. Interview Questions For Oracle | SQL interview Questions
  4. Pivot in SQL with Example | Pivot Clause | Pivot and unpivot in SQL | Pivot in Oracle | SQL Pivot with Real Life Example
  5. Difference between Truncate and Delete, Drop in SQL | Truncate Delete and Drop Commands in SQL | SQL Difference between Truncate,Delete and Drop Commands | Real Life Examples of Truncate Delete and Drop Command
  6. Process Commands in Unix | Unix Filter commands | Touch command with example | ps command with examples | Kill command with examples
  7. Table partition | Table Partitioning in Oracle | Partitioning in SQL | How Partitioning helps improving Performance | Table Partitioning with Examples | Partitioning on existing table
  8. Database Normalization Examples | Normalization in Database with Examples | Database Normalization with Real Life Examples | Designing Normalized Database
  9. Operators in SQL | SQL Operators | Arithmetic Operators | Logical Operators | Comparison Operators | Negation Operators | Operators in SQL with Examples
  10. Interview Questions for TCS | SQL Interview Questions for TCS | Interview Questions Asked in TCS | TCS Interview Questions

Comments are closed.