Examples of Correlated Subquery in SQL Server
Let us consider the following two demo tables Customers and Orders on which we will execute our queries for better understanding.
Customer Table:
Order Table:
Example 1 : Correlated Subquery Using the Exists Operator
In the following example we will see how we can use the Exists operator along with the correlated subqueries to fetch the required result:
SELECT CustomerName
FROM Customers C
WHERE EXISTS (
SELECT 1
FROM Orders O
WHERE O.CustomerID = C.CustomerID
);
Output:
Explanation: In the above example the outer query selects the name of the customers from the Customers table and the correlated subquery checks if there exists any orders in the Orders table for the current customer from the outer query. If a match is found the name of the customers is shown in the result set from the outer table Customer.
Example 2: Correlated Subquery Using the SELECT Clause
In the following example we will see how we can use the Select clause along with the correlated subqueries to fetch the required result:
SELECT CustomerName
FROM Customers C
WHERE (SELECT COUNT(*)
FROM Orders
WHERE CustomerID = C.CustomerID) > 1;
Output:
Explanation: In the above example the outer query selects the name of the customers from the Customers table and the correlated subquery counts the number of orders for each customer and checks if it is greater than 1. If a match is found the record is displayed in the result set. In the orders table only 1 customer had placed multiple orders whose customerId was 1 which is returned in the result set.
Example 3: Correlated Subquery to Update the data
In the following example we will see how we can update our data in the tables with the help of correlated subqueries:
UPDATE Orders
SET Quantity = Quantity + 1
WHERE CustomerID IN (
SELECT CustomerID
FROM Customers
WHERE State = 'Maharashtra'
);
Output:
Explanation: In the above example the UPDATE statement increases the quantity of orders in the Order table and the correlated subquery identifies customers who belongs to the state Maharashtra and if a match is found then the quantity of orders is updated in the orders table. In our orders table we had 2 customers who belonged to Maharahtra and thus their order quantity was updated by 1 in the result set.
Example 4: Correlated Subquery to Delete the data
In the following example we will see how we can delete the data from the tables with the help of correlated subqueries:
DELETE FROM Customers
WHERE NOT EXISTS (
SELECT 1
FROM Orders
WHERE Customers.CustomerID = Orders.CustomerID
);
Output:
Explanation: In the above example the DELETE statement removes the record from the customers table and the correlated subquery identifies customers who have not placed any order if a match is found then the record of that customer is deleted from the outer customer table. In our table we had only one customer with customerID =5 who has not placed any order and we can see in the output his/her entry was deleted from the customers table.
Example 5: Find nth Value using Correlated Subquery
In the following example we will use the correlated subquery to find out the second oldest person from the table customers.
SELECT CustomerID, CustomerName, Age
FROM Customers C1
WHERE 1 = (
SELECT COUNT(DISTINCT Age)
FROM Customers C2
WHERE C2.Age > C1.Age
);
Output:
Explanation: The outer query C1 selects the customers from the Customer table and the correlated subquery counts the number of distinct ages that are greater than the age of the current customer. The outer query then filters customers where the count is equal to 1 which means the customer found is the second oldest person in the data set. Similary you can use 2 to find the third oldest employee from the table and so on. In our table the oldest person was of age 42 so as we wanted the second oldest person, the details of the person with age 35 were returned in the result set.
SQL Server Correlated Subquery
Correlate subquery is a great tool in SQL servers that allows users to fetch the required data from the tables without performing complex join operations. In SQL, a subquery is a query nested inside another query. A correlated subquery is a specific type of subquery that references columns from the outer query, creating a relationship between the two.
In this article, we will learn about the correlated subqueries in SQL Server step by step with various examples. We will learn how correlated subquery can be used as a substitute for complex join operations. So deep dive into this article and master the correlate subqueries in SQL server.