Sub Query Vs Corelated Query in SQL

In SQL, both subqueries and correlated queries are used to retrieve data from the database. However, they have different ways of executing and returning data.

A subquery is a query that is embedded within another query. It is executed first, and its result is then used by the outer query to retrieve data. A subquery can return a single value or a set of values, and it can be used in various clauses, such as the WHERE clause, the HAVING clause, or the SELECT clause.

For example, consider the following subquery that returns the average salary of all employees:

SELECT AVG(salary) 

FROM employees;

A correlated query, on the other hand, is a query that refers to a column from the outer query. It is executed for each row in the outer query, and its result is used to filter or retrieve data from the inner query. A correlated query can also return a single value or a set of values, and it is commonly used in the WHERE clause.

For example, consider the following correlated query that returns the employees who have a salary greater than the average salary of their department:

SELECT * FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department_id = e.department_id ); 

Here, the inner query is correlated to the outer query by the department_id column, and it is executed for each row in the outer query.

The main difference between subqueries and correlated queries is that subqueries are executed first and their result is used by the outer query, while correlated queries are executed for each row in the outer query.

Subqueries are generally more efficient for retrieving a large set of data, while correlated queries are more efficient for filtering data based on a condition that cannot be expressed using a simple WHERE clause.

Here are some examples of subqueries and correlated queries in SQL:

Subquery example:

Suppose we have two tables, employees and departments, and we want to find the average salary of employees in the sales department. We can use a subquery in the following way:

SELECT AVG(salary) 

FROM employees 

WHERE department_id = ( SELECT department_id 

                                               FROM departments 

                                                WHERE department_name = 'Sales' );

In this example, the inner query retrieves the department_id of the sales department, which is then used by the outer query to retrieve the average salary of employees in that department.

Correlated query example:

Suppose we have the same two tables, employees and departments, and we want to find all employees who have a salary greater than the average salary of their department. We can use a correlated subquery in the following way:

SELECT *
FROM employees e 

    WHERE salary > ( SELECT AVG(salary) FROM employees 

                                           WHERE department_id = e.department_id );

In this example, the inner query is correlated to the outer query by the department_id column, and it is executed for each row in the outer query. The result is a list of employees who have a salary greater than the average salary of their department.

In conclusion, subqueries and correlated queries are both important tools for retrieving data in SQL, but they have different ways of executing and returning data. Subqueries are executed first and their result is used by the outer query, while correlated queries are executed for each row in the outer query.

Thank You,

Ashwani