co-related sub-query with example

Creating Simple Queries, Hierarchical Queries, Sorting Query Results, Joins, Unnesting of Nested Subqueries and Distributed Queries.
Post Reply
Posts: 142
Joined: Fri Nov 23, 2018 2:48 am

co-related sub-query with example

Post by AyubHussain » Tue Dec 04, 2018 6:14 am

Explain co-related sub-query with example?

Posts: 62
Joined: Fri Nov 23, 2018 2:51 am

Re: co-related sub-query with example

Post by dsusmitha » Tue Dec 04, 2018 6:15 am

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.

Posts: 85
Joined: Thu Jan 03, 2019 3:58 am

Re: co-related sub-query with example

Post by srini » Tue Jan 22, 2019 4:10 am

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.

Types of Subqueries:
1.Single Row Subquery
2.Multirow Subquery
3.Correlated Subquery
4.Nested SubQueries
5.Scalar Subqueries

1.Single Row Subquery:
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.
2.Multi Row Subqueries:
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.
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.
4.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 subquery always follows bottom-up approach.
5.Scalar Sub-queries :
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.

Post Reply

Return to “SQL Queries and Subqueries”