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.
What are Correlated Subqueries?
A correlated subquery is a nested inner query whose results depend on the outer query for its values. The inner query gets executed once for each row evaluated by the outer query. In simple words, the result of the subquery will be dependent on the outer query. Don’t confuse correlated subqueries with the nested queries. In nested queries, the inner query is only executed one time whereas correlated subqueries get executed for every row returned by the outer query. The outer query can consist of UPDATE, DELETE, WHERE, and SELECT clauses in case of correlated subqueries.
Key points about correlated subqueries:
- The inner query executes once for every row in the outer query.
- The inner query has a reference to a column in the outer query in its WHERE clause.
- The results of the inner query are used by the outer query.
Syntax:
SELECT columns
FROM outer_table
WHERE condition_operator
(SELECT columns
FROM inner_table
WHERE outer_table.column = inner_table.column);
The syntax of the Correlated subqueries mainly consist of 3 parts:
- In the first part we will have to specify the columns we want to retirive in the final result set from the outer query. Use the SELECT clause to select the required columns and FROM clause to select the outer table from which the main query retrieves rows.
- In the second part we will have to define the conditions for selecting rows from the outer table using the Where clause. The conditions can consist of comparisons logical operators or any valid conditions specified by the user.
- In the third part we will have to define our correlated subquery. This inner query will retrieve the columns from the inner table. The correlation will be established by specifying a condition that links a column from the outer query’s table to a column in the inner query’s table.
Advantages of Using Correlated Subquery
Here are some of the key advantages of using the Correlated subqueries in SQL Server:
- Avoid Complex Joins: Correlated subqueries can be used as a substitute for complex join operations. This can help to simplify the queries and improve the readability.
- Query Optimization: In some cases the correlated subquery allows the database optimizer to efficiently process the data using the indexes which can optimize the overall performance of our query.
- Allows row by row processing: For correlated subquery the inner query is executed for every row of the outer table which provides an easy way to filter data row by row.
- Code reusability: The inner query of the correlated subqueries can be reused across various queries by simply changing the outer query which promotes the code reusabililty factor.
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:
Table-Customers
Order Table:
Table-Orders
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:
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:
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:
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:
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.
Conclusion
In this article, we have learned the working of correlated subqueries in SQL servers. We have learned how we can use these subqueries to fetch the result from the inner tables whose output depends on the outer tables. We understood how these subqueries can be used in place of complex join operations to fetch the required results from multiple tables. We hope this article has helped you to understand correlated subqueries in SQL servers.