Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is a Correlated Subquery?
2.1.
Example
2.2.
MySQL
3.
Nested Subqueries Vs Correlated Subqueries
3.1.
For Nested Subqueries:
3.2.
MySQL
3.3.
For Correlated Subquery:
3.4.
MySQL
3.4.1.
Other Uses of Correlation in UPDATE and DELETE
3.4.1.1.
CORRELATED UPDATE:
3.5.
MySQL
3.5.1.
CORRELATED DELETE:
3.6.
MySQL
4.
Using EXISTS with a Correlated Subquery
4.1.
MySQL
5.
Using NOT EXISTS With A Correlated Subquery
5.1.
MySQL
6.
Correlated Vs Uncorrelated Subqueries In SQL
6.1.
Outlook
7.
Frequently Asked Questions
7.1.
What is a correlated sub query?
7.2.
What is the difference between correlated subquery and nested subquery?
7.3.
Which one is faster subquery or correlated subquery?
7.4.
What are the advantages of correlated subquery in SQL?
8.
Conclusion
Last Updated: Mar 27, 2024
Medium

Correlated Subquery

Author Rinki Deka
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

In the realm of SQL, a subquery is a query nested inside another query. Among these, the correlated subquery is a powerful tool that Sql developers use to write queries that are more complex and dynamic. Unlike a regular subquery, a correlated subquery is executed repeatedly, once for each row that might be selected by the outer query.

correlated subquery

In this blog, we will learn about correlated subqueries. We will also learn about nested subqueries vs correlated subqueries. Later in the blog, we will discuss the differences between them. 

What is a Correlated Subquery?

A correlated subquery is a subquery that uses values from the outer query to complete its execution. This means that the subquery depends on the outer query and cannot be executed independently. It's like a loop within a query that iterates through each row provided by the outer query to determine which rows satisfy the condition.

Following are the three main queries are below: 

  • SELECT
  • UPDATE
  • DELETE

Example

Consider a database with two tables: Employees and Departments. If we want to find employees who earn more than the average salary in their respective departments, we would use a correlated subquery.

Let’s see our employees table first: 

employees table first

The departments' table:

departments' table
  • MySQL

MySQL

SELECT e.firstName, e.Salary

FROM Employees e

WHERE e.Salary > (

 SELECT AVG(Salary)

 FROM Employees

 WHERE DepartmentID = e.DepartmentID

);

Output 

department and salary

Explanation:

In the above example, the inner query calculates the average salary for each department. The outer query then uses this information to select only those employees whose salary exceeds the average of their department.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Nested Subqueries Vs Correlated Subqueries

For Nested Subqueries:

Nested subqueries are independent of the outer query and can be run on their own. They return a value or set of values to the outer query.

  • MySQL

MySQL

SELECT FirstName

FROM Employees

WHERE Salary > (

 SELECT AVG(Salary)

 FROM Employees

);

Output

output

For Correlated Subquery:

A correlated subquery, however, is executed once for each candidate row considered by the outer query. It uses the value from the outer query to complete its execution.

  • MySQL

MySQL

SELECT e.FirstName

FROM Employees e

WHERE EXISTS (

 SELECT 1

 FROM Departments d

 WHERE e.DepartmentID = d.departmentID AND d.Budget > 10000

);

Output

output

Other Uses of Correlation in UPDATE and DELETE

CORRELATED UPDATE:

You can use a correlated subquery to update rows based on a condition relative to another table.

  • MySQL

MySQL

UPDATE Employees e

SET e.Salary = e.Salary * 1.1

WHERE EXISTS (

 SELECT 1

 FROM Departments d

 WHERE e.DepartmentID = d.departmentID AND d.Budget > 1000000

);

Output

output
CORRELATED DELETE:

Similarly, you can delete rows in one table based on a condition from another table.

  • MySQL

MySQL

DELETE FROM Employees e

WHERE EXISTS (

 SELECT 1

 FROM Departments d

 WHERE e.DepartmentID = d.departmentID AND d.Budget < 5000

);

Output

output

Using EXISTS with a Correlated Subquery

The EXISTS keyword in SQL is used to check for the existence of rows in a subquery.

  • MySQL

MySQL

SELECT e.FirstName
FROM Employees e
WHERE EXISTS (
SELECT 1
FROM Departments d
WHERE e.DepartmentID = d.departmentID
);

Output

output

Using NOT EXISTS With A Correlated Subquery

Conversely, NOT EXISTS checks for the absence of rows in a subquery.

Let’s see our sales table

Sales table
  • MySQL

MySQL

SELECT e.FirstName
FROM Employees e
WHERE NOT EXISTS (
SELECT 1
FROM Sales s
WHERE e.employee_ID = s.sale_ID
);

Output

output

Correlated Vs Uncorrelated Subqueries In SQL

Feature Correlated Subquery Uncorrelated Subquery

Feature  Correlated Subquery Uncorrelated Subquery
Execution Runs once for each row in the outer query Runs only once
Dependency Depends on the outer query for its values Independent of the outer query
Use Case  Useful for row-by-row operations Useful for one-time lookups

Outlook

Correlated subqueries are a testament to the flexibility and power of SQL. They allow for dynamic queries that adapt based on the data in each row. Understanding when and how to use correlated subqueries can significantly enhance the capabilities of your SQL queries, allowing for more precise and efficient data manipulation.

Frequently Asked Questions

What is a correlated sub query?

A subquery where the inner query references columns from the outer query, creating a relationship between the two queries.

What is the difference between correlated subquery and nested subquery?

In a correlated subquery, the inner query depends on the outer query's columns. A nested subquery is standalone.

Which one is faster subquery or correlated subquery?

Generally, subqueries are faster than correlated subqueries as they can be evaluated independently.

What are the advantages of correlated subquery in SQL?

Provides access to data from the outer query, allowing for more context-aware and flexible filtering within the subquery.

Conclusion

In this article, we have discussed about Correlated subquery in SQL. This subquery proves to be a powerful tool for leveraging data relationships within a query. While it introduces complexity, it offers context-aware filtering by referencing columns from the outer query.

You can refer to our guided paths on the Coding Ninjas. You can check our course to learn more about DSADBMSCompetitive ProgrammingPythonJavaJavaScript, etc. 

Also, check out some of the Guided Paths on topics such as Data Structure and AlgorithmsCompetitive ProgrammingOperating SystemsComputer Networks, DBMSSystem Design, etc., as well as some Contests, Test Series, and Interview Experiences curated by top Industry Experts.

Previous article
MySQL UNIX TIMESTAMP Function
Next article
MySQL CONCAT() Function
Live masterclass