SQL Correlated Subqueries (With Examples)

The correlated subqueries are used in many codes to solve complex problems. Different types of subqueries are available, like nested subqueries, correlated subqueries, single and multiple-row subqueries, and multiple-column subqueries. All have various applications and advantages. In this article, we will see the details of the correlated subqueries. The subqueries are considered correlated when the subquery uses the data from the outer query (parent query). Now, let’s start with the basics.

Also, read the article on subqueries in MySQL.

Introduction to Correlated Subqueries

The correlated subquery is used as an inner query for the parent query/main query. For each row of the main query, the subquery is executed. In simple words, for each row of the parent query, the entire correlated subquery is executed and evaluated. This is a very important way when we need to read every row from the table and compare or evaluate the data. The main query has something like SELECT, UPDATE, and DELETE statements. The whole process is repeated several times that’s why this query is also known as a repetitive subquery.

Let’s try to understand this concept of correlated subqueries with an example.

Execution of Correlated Subqueries in SQL

In this illustration, we are using two tables: the orders table and the customers table. The orders table holds all the orders with order ID, customer ID, and order dates. The customer table holds all the details related to the customers, like customer ID, customer name, etc. Now we have two tables and using this, we can execute the correlated subqueries. The diverse problems, like the orders of every customer, can be tracked. Correlated subquery is a very useful and simple approach to tracing all the rows from the parent table. Let’s see the query to comprehend the idea thoroughly.

Query:

SELECT
    c.CustomerID,
    c.CustomerName,
    (
        SELECT COUNT(*)
        FROM Orderstable o
        WHERE o.CustomerID = c.CustomerID
    ) AS TotalOrders
FROM Customerstable c;Code language: PHP (php)

In this query, we have outlined the orders of every customer from the parent table. The correlated subquery is used to track the orders. Let’s catch the outcomes of this query.

Output:

Real-World Applications of Correlated Subqueries

We can use correlated subqueries for various applications like updating the table, deleting records from the table, filtering data from the table, and calculating aggregates. Now, let’s try to run these examples one by one employing correlated subqueries.

Update Data Using Correlated Subqueries

We can easily update the data of the table or particular element of the table using correlated subquery.

Here is one illustration where we can comprehend this concept. In this example, we are using just one table but updating the data using correlated subquery. The products table holds all the information on the available products. We are attempting to update the price of a specific product employing a correlated subquery. Under the UPDATE statement, we are running the correlated subquery. Let’s see the query for a better understanding.

Query:

UPDATE productstable p1
SET price = (
    SELECT AVG(price) 
    FROM productstable p2 
    WHERE p2.category = p1.category
)
WHERE product_id = 1;
SELECT * FROM productstable;

This correlated subquery will revise the element from the table and the element of ID number 1. The price is updated to the average price. Let’s see whether the outcome is revised or not.

Output:

You can see the results. The price is updated to 1000.

Delete Data Using Correlated Subqueries

In this example, we will delete the content of the sample table using the correlated subqueries. Two tables contain the information of the students like their names, IDs, attendance and other related information. We are trying to delete the names of students who have less attendance/ not attended any class. Let’s try this one with an SQL query.

Query:

DELETE FROM studentstable
WHERE student_id NOT IN (
    SELECT student_id
    FROM classes_attendance
);
SELECT * FROM studentstable;

This query will help you to delete the data of the students who haven’t attended any class from the table.

Output:

The result table does not contain the information of the students who haven’t attended any classes until now. You can update this query with your table and information!

Filtering Data Using Correlated Subqueries

You can effortlessly filter the data from the table using correlated subqueries. This technique can be used to sort out things like student data, library data, or any kind of sports data. Filtering is a must to get answers to some complex questions. To understand the concept let’s implement one query to filter the data of students who got lower marks in exams.

Query:

SELECT student_id, student_name
FROM studentstable s
WHERE EXISTS (
  SELECT 1
  FROM gradestable g
  WHERE g.student_id = s.student_id
  AND g.grade > 79
);

This example implements the query to filter data from the table for the students who got 79+ marks in their exams. The result should contain the information/ IDs of the student who qualified for the exam.

Output:

Here, the results are accurate!

Calculating Aggregates Using Correlated Subqueries

The correlated subquery can employed to calculate the aggregate sum of the data from the table. Given query will help to decode a complex problem using correlated subqueries. Here, I am employing only one table that will help to print the values using correlated subquery. Let’s see the implementation to comprehend the concept.

Query:

SELECT
    product_id,
    (SELECT SUM(amount) FROM sales s2 WHERE s2.product_id = s1.product_id) AS total_sales
FROM sales s1
GROUP BY product_id;Code language: PHP (php)

Output:

You can see the results are correct. Just enter your own data in the code to get the results.

Difference Between Other Subqueries and Correlated Subqueries

The noticeable distinction between the non-correlated and correlated queries is the use of values from the outer query. The outcome of correlated queries relies on the outer query. The non-correlated query is independent of the outer query. In the matter of a non-correlated query, the inner query executes sooner, and then the outer query is implemented. This procedure is the opposite in the correlated subqueries.

Summary

The entire article is about a type of subquery that is the correlated subquery. The correlated subquery is used in multiple complicated situations. This article will give you a quick introduction to the correlated subqueries and the real-world applications. The illustrations based on the correlated subqueries are also explained in detail. The distinction between the correlated and non-correlated subquery is also explained. Hope you will enjoy this article.

Read More: MySQL SELECT INTO Variable

Reference

Do read the official documentation on correlated subqueries for more details.