What is Correlated Subquery?

Correlated subquery can be defined as the inner query referenced by the outer query. The outer query reference the inner query for repeated execution. Correlated subqueries will contain the references to the objects present in the parent statement. The operation of correlated subquery depends upon the evaluated row in the parent statement. Correlated subquery cannot run independently without outer query.

Difference Between Inner and Outer Join

Example for correlated Subquery

SELECT *
FROM Employ Empy1
WHERE (01) = (
SELECT COUNT(DISTINCT(Empy2.Sala))
FROM Employ Empy2
WHERE Empy2.Sala > Empy1.Sala)

In the above SQL for correlated subquery, the inner subquery Empy1.Sala uses an alias “Empy1”. The alias “Empy1” was created in the outer query. The correlated subquery used in the SQL, references a value in the WHERE clause which is referenced in the outer query.

In correlated subqueries, the rows present in the outer query are processed first. The outer row processing will be followed by the rows present in the subquery. Every outer row processing in correlated subquery will be followed by the processing of the corresponding inner subquery. According to our example for correlated subquery; whenever the row of Emp1 is processed, it will be followed by the processing of its subquery. The above steps will be repeated for every outer query. The row value of Emp1.Sal will be executed until it satisfies the condition used inside the WHERE clause.

Types of Indexes in SQL

What is Uncorrelated Subquery?

Uncorrelated subquery can be defined as a subquery which does not contain any references to the objects present in the parent statement.

Example for Uncorrelated Subquery

select Salesprsn.Name from Salesprsn
where Salesprsn.ID NOT IN(
select Orders.salesprsn_id from Orderz, Customerz
where Orderz.custe_id = Customerz.ID
and Customez.Name = ‘Karthikh’)

In the SQL statement given above, the queries mentioned below the NOT IN are the subqueries. The reason behind the classification of these queries as uncorrelated is that it can be executed without the dependence of outer query. Uncorrelated don’t have any relationship with its outer query.

• Learn: Aptitude questions and answers

Rules to follow while using subqueries in SQL

• Subqueries can be used while inserting a statement.
• Subqueries can be used as column in the select statement.
• Only scalar values are permitted when subqueries are returned along with WHERE clause.
• A value from column is used when subqueries are returned along with IN or NOT IN clause.

Difference Between Correlated and Uncorrelated Subqueries

• In correlated subquery, the value of subquery depends on the value of outer query.
• In Uncorrelated subquery, the value of subquery does not depend on the value of outery query.
• Outer query is executed first in the correlated subquery while inner query is executed in the uncorrelated subquery.
• Uncorrelated subqueries are faster than correlated subqueries. Since correlated subqueries are slow in execution they are avoided in favour of SQL joins.
• The keywords like “exists and not exists” are generally used along with correlated subqueries. Uncorrelated subqueries use keywords like IN and NOT IN.

Important points to be noted while using subqueries

• Almost every operation of subqueries can be achieved SQL joins.
• Either a scalar value or a value present in one of the columns is returned by the subquery.
• Subqueries can be used as a column in Select clause.

All Subquery operations can be achieved by various SQL joins. But SQL joins are now considered as an obsolete method so many developers opt for subqueries instead of SQL join. However SQL joins are preferred instead of correlated subqueries because of its slow computing process.