Do you think IIT Guwahati certified course can help you in your career?
No
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.
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:
The departments' table:
MySQL
MySQL
SELECT e.firstName, e.Salary
FROM Employees e
WHERE e.Salary > (
SELECT AVG(Salary)
FROM Employees
WHERE DepartmentID = e.DepartmentID
);
You can also try this code with Online MySQL Compiler
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.
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
);
You can also try this code with Online MySQL Compiler
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
);
You can also try this code with Online MySQL Compiler
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.