Difference between Correlated and Non-Correlated SubQuery in SQL

What is difference between correlated and non-correlated subquery in SQL?

A correlated subquery in SQL is a subquery that is dependent on the outer query. This means that the subquery relies on data from the outer query to provide a result. The subquery is executed for each row in the outer query and its result is used in the WHERE clause of the outer query. While, a non-correlated subquery, on the other hand, is independent of the outer query and is executed only once. The result of the subquery is stored in a temporary table, which is then used by the outer query.

The key difference between the two types of subqueries is that a correlated subquery depends on the data in the outer query, while a non-correlated subquery does not. This means that a correlated subquery is generally more flexible, as it can take into account the current state of the data, while a non-correlated subquery provides a fixed result.

What are other differences between correlated and non-correlated subquery?

Here are a couple of more differences between two:

1. Performance

A correlated subquery is executed for each row of the outer query, which can be slower than a non-correlated subquery that is executed once.

2. Flexibility

As mentioned, a correlated subquery can take into account the current state of the data, making it more flexible than a non-correlated subquery.

3. Usage
Non-correlated subqueries are generally easier to understand and are used more frequently, while correlated subqueries are more advanced and typically used in specific scenarios.

4. Readability

Non-correlated subqueries can make a SQL query more readable by breaking it down into smaller, more manageable parts, while correlated subqueries can make the query more complex.

5. Nested queries

Non-correlated subqueries can be nested within other subqueries, while correlated subqueries cannot.

6. Returned data
A non-correlated subquery returns a single value or set of values, while a correlated subquery returns a set of values for each row of the outer query.

7. Result comparison

Non-correlated subqueries are used to compare the result of the subquery with the outer query, while correlated subqueries compare values from the same row of the outer query.

correlated and non-correlated subqueries Examples in SQL

Here is an example of a non-correlated subquery:

SELECT * 
FROM orders 
WHERE order_date > 
  (SELECT MAX(order_date) 
   FROM orders 
   WHERE customer_id = 'CUST01');

In this example, the non-correlated subquery retrieves the maximum order date for a specific customer (CUST01) and returns a single value. This value is then used in the WHERE clause of the outer query to retrieve all orders with an order date greater than the maximum order date for CUST01. 

The subquery is executed once, and the result is stored in a temporary table for use by the outer query.

And, here is an example of a correlated subquery:

SELECT customer_id, order_date, 
  (SELECT SUM(order_total) 
   FROM orders 
   WHERE customer_id = o.customer_id) AS total_spent 
FROM orders o;

In this example, the correlated subquery retrieves the total spent by each customer by summing the order totals for each customer. The subquery is dependent on the outer query and is executed for each row of the outer query. The result of the subquery is then used in the main query to retrieve the customer ID, order date, and total spent for each customer.

Also, here is a nice table which highlights the difference between correlated and non-correlated subquery in SQL

Common Interview questions asked about correlated and non-correaltd subqueries?

Here are some common interview questions related to correlated and non-correlated subqueries in SQL:

• What is the difference between a correlated and non-correlated subquery in SQL?
• When would you use a correlated subquery in SQL?
• Can you provide an example of a non-correlated subquery in SQL?
• How do you optimize a correlated subquery in SQL?
• What are the potential performance implications of using a correlated subquery in SQL?
• How do you use a subquery in a FROM clause in SQL?
• What are the different types of subqueries in SQL?
• Can you give an example of using a subquery in a HAVING clause in SQL?
• How do you use a subquery in a SELECT clause in SQL?
• What is the difference between a subquery and a join in SQL?

You can answer most of these question after completing this article but if you still have doubts, feel free to ask in comments. 

When would you use a correlated subquery?

A correlated subquery is used when the subquery depends on the value from the outer query. It is used to return data based on the condition that is being passed from the outer query.

Here are some scenarios where you may use a correlated subquery:

• When you need to find a value in one table based on a value in another table.
• When you want to retrieve data from one table based on the data in another table.
• When you need to perform an operation on data in one table based on data in another table.
• When you need to retrieve data that meets a certain criteria in one table and use that data in another table.

For example, if you have a table of employees and a table of departments, and you want to retrieve the name of the department for each employee, you can use a correlated subquery to accomplish this.

How do you optimize a correlated subquery?

Here are some tips to optimize a correlated subquery in SQL:

1. Use indexes

Make sure that the columns used in the join condition and the subquery are indexed, as this will make the query execution faster.

2. Avoid using subqueries in the SELECT clause

Use a join instead, as it is generally more efficient.

3.  Use appropriate join types

For example, use an INNER JOIN instead of a LEFT JOIN when only matching data is required.

4. Limit the data returned by the subquery

Only retrieve the data that is required for the operation, as this will reduce the amount of data that needs to be processed.

5. Avoid using subqueries in complex conditions

Use a join or a temporary table instead, as these can be more efficient.

6. Avoid using multiple subqueries

Use a single join or a single subquery instead, as this will reduce the number of queries that need to be executed.

7. Use the EXISTS operator instead of IN operator

The EXISTS operator is more efficient when the subquery returns a large number of rows.

These are some general guidelines to optimize a correlated subquery in SQL, however, the exact optimization techniques will depend on the specific database management system you are using and the structure of your data.

What are the potential performance implications of using a correlated subquery in SQL?

Using a correlated subquery in SQL can have some potential performance implications, such as:

1. Slow execution time

As correlated subqueries require the execution of a separate query for each row, they can be slow when dealing with large datasets.

2. Increased resource utilization

As multiple queries are executed, correlated subqueries can consume more resources, such as CPU and memory, than other query techniques.

3. Increased I/O operations

Correlated subqueries can result in an increase in disk I/O operations, as multiple queries need to be executed.

4. Increased network overhead

If the database is remote, the use of correlated subqueries can increase the network overhead, as multiple queries need to be executed and the data needs to be transferred back and forth.

5. Complexity

Correlated subqueries can be more complex than other query techniques, making it harder to understand, maintain, and optimize the query.

It's important to consider these performance implications when using a correlated subquery in SQL. In some cases, it might be more appropriate to use a join, temporary table, or other query technique instead.

That's all about difference between Correlated and Non-Correlated or normal subquery in SQL. In conclusion, understanding the difference between correlated and non-correlated subqueries is essential for mastering SQL query optimization and improving overall database performance. 

Non-correlated subqueries, also known as independent subqueries, are executed only once and provide results that are used by the outer query. 

They are generally more efficient as they do not depend on the outer query's results. On the other hand, correlated subqueries are executed repeatedly for each row processed by the outer query, making them potentially less efficient. However, they offer the advantage of being able to reference columns from the outer query, providing a level of interdependence. 

The choice between correlated and non-correlated subqueries depends on the specific requirements of the query and the database optimization goals. A solid understanding of these concepts empowers SQL developers to write efficient and effective queries tailored to the complexity of the tasks at hand.

Other SQL Articles and Tutorials You May Like

• How to work with Nulls in SQL?
• How to concatenate columns in SQL Server
• What is Virtual Column in MySQL?
• 5 Common SQL Errors and their Solutions
• Top 35 MySQL Interview Questions with Answers
• How to store hierarchical data in SQL?
• How to use LEFT, RIGHT, and CROSS JOIN in SQL
• SQL Cross Join Example and Performance
• How to use WHERE, LIKE, and BETWEEN in SQL
• What is Primary Key in SQL? How to choose?

Thanks for reading this SQL Interview question so far. If you like the solution and my explanation of this Nth highest salary problem in SQL then please share with your friends and colleagues.