Difference between correlated and non correlated subqueries.

 "It is the accepted question in oracle SQL and plsql interview question."

INTRODUCTION:

Subqueries can be classified into the two parts i.e. Correlated and non-correlated subquery. In both mention subquery, there is the combination of outer and inner query. Same select statement are used for both the sub-queries. Execution of the sub query can include from and where clause, group by, having and order by clause. It contains the select statement inside the another select statement. It is used with the update and delete statements.

Correlated subquery is also called as the simple subquery. Not frequently, both subqueries type is providing the same results. Subquery is also called as the nested query. SQL query is divided into two parts i.e. inner subquery and outer subquery. Outer query is called as the parent query. Inner query is called as the child query.

Subqueries can be defined as the query which is nested in the other query. Subquery can be used to update the database by using insert, update and delete  sql statements. Subquery which is used for selecting the records as per where or having clause is called as inner query. Subquery inside another subquery is called as the nested subqueries. 

In order to get or retrieve the data from one table and condition based on another table, we can use join or subquery but in order to get or retrieve the data from more than one tables then we have to use join instead of subquery.

In the where clause, we can use upto 255 subqueries. In the from clause and select clause, there has been no limit specified.

CORRELATED SUBQUERY:

When Subquery refers to the main query for execution, it is called as correlated subquery. It refers the column from the outer query. It is repeatedly executing the subquery for each row of the outer query using the current row value to provide an output. Subquery result is used for evaluation purpose of the outer query. It is processed row by row. It is evaluated by the parent statement i.e. insert, update and delete. ANY and ALL operator is used in the correlated subquery. 

Process:

From outer query, candidate get is obtained.

The inner query is executed with the help of candidate key value.

Inner query value is used to qualify or disqualify the candidate key of the outer query.

It is also called as synchronized query. The word correlate means that mutual relationship or connection between the two things which is dependent on one another.

EXISTS:

It is used to check the presence or existence of any record in the subquery and will return true if the subquery returns one or more row records. It is generally used in the correlated subqueries, and it is used with the WHERE clause to filter the rows on the specific condition. You can conditionally retrieve the data based on the existence of related records in the same or another table.

SYNTAX:

select column_name from table_name where column_name EXISTS (Subquery);

IN:

It is used for small range of values upto 1000.IN operator is less preferred than exists operator in the large and complex subquery result set.

SYNTAX:

select column_name from table_name where column_name IN (Subquery);

NOT EXISTS:

It is just opposite of exist operator.

SYNTAX:

select column_name from table_name where column_name NOT EXISTS (Subquery);

NON- CORRELATED SUBQUERY:

When subquery is executed once and its result is used to extract the data from the main query, then it is called as non-correlated subquery. It is called as the simple subquery and it is classified into single row subquery,multi row subquery and multi column subquery.

Scalar subquery is defined as subquery which returns the single value as output and it is mostly used with aggregate functions i.e count,min,max,sum and avg.

The below provided comparison operator is used in the subquery, then it is called as single row or scalar subquery.

=	equal
>	Greater than
<	Less than
>=	Greater than equal to
<=	Less than equal to
<>	Not equal
!=	Not equal

Multi-row subquery: It is the type of subquery in SQL which returns the multiple rows of the data.

The below provided operators is used in the subquery, then it is called as multi row subquery.

IN	Equal to any value present in the list.
NOT IN	Not equal to any value in the list.
ANY	It will return any row that will match with value in the list. It is used with the WHERE or HAVING clause to filter the rows on the specific condition and will return true when at least one value after comparison. SYNTAX:select column_name from table_name where column_name operator ANY(Subquery); Operator should be any one i.e. <,>,<>,!=,=,>=and<=
ALL	It will return all row that will match all the values in the list. It will return true when all value in the set will match after comparison. It is used with the WHERE or HAVING clause to filter the rows on the specific condition. SYNTAX:select column_name from table_name where column_name operator ALL(Subquery); Operator should be any one i.e. <,>,<>,!=,=,>=and<=

DIFFERENCE:

CORRELATED SUBQUERY	NONCORRELATED SUBQUERY
It is dependent on the outer query.	It is independent on the outer query.
It executes the outer query before the inner query.	It executes the inner query before the outer query.
Less efficient in terms of performance for large datasets.	More efficient.
It is not used with IN and not in clause.	It is used with IN and not in clause.
It is slower queries.	It is faster queries.
It depends on the data of the outer query.	It does not depend on the data of the outer query.
It is more flexible than non-correlated subqueries.	It is less flexible than non-correlated subqueries.
Due to complex to understand, it is used in specific	Due to easier to understand, it is more used.
It is evaluated in top to bottom manner.	It is evaluated in bottom to up manner.
Inner query is executed for each row of outer query.	Outer query is executed for each row of inner query.