Correlated subqueries and regular (non-correlated) subqueries

Correlated subqueries and regular (non-correlated) subqueries are two different types of SQL subqueries used for different purposes. 

Here's a comparison of correlated and non-correlated subqueries:

Correlated Subquery:

1. Definition:

   - A correlated subquery is a subquery that depends on values from the outer query. It means that the subquery is executed once for each row processed by the outer query.

2. Usage:

   - Correlated subqueries are used when you need to reference values from the outer query within the subquery. This is often necessary when you want to filter or aggregate data based on values in the current row of the outer query.

3. Performance:

   - Correlated subqueries can be less efficient because they involve executing the subquery multiple times, once for each row processed by the outer query. This can lead to performance issues with large datasets.

4. Example:

   - An example of a correlated subquery is finding all employees whose salary is greater than the average salary in their department. Here, you need to compare each employee's salary to the average salary of their department, so the subquery is correlated to the department in the outer query.

SELECT EmployeeName

   FROM Employees e1

   WHERE Salary > (

       SELECT AVG(Salary)

       FROM Employees e2

       WHERE e2.Department = e1.Department

   );

Non-Correlated Subquery:

1. Definition:

   - A non-correlated subquery is a subquery that is independent of the outer query. It can be executed once and its result is used as a constant or parameter in the outer query.

2. Usage:

   - Non-correlated subqueries are used when you want to retrieve a single value or set of values that are not related to the current row of the outer query. They are often used in conditions, aggregations, or to retrieve data that is not tied to the outer query.

3. Performance:

   - Non-correlated subqueries are generally more efficient because they are executed only once, and their result is used as a fixed value or parameter in the outer query.

4. Example:

   - An example of a non-correlated subquery is finding the highest salary in the entire company.

SELECT EmployeeName

   FROM Employees

   WHERE Salary = (

       SELECT MAX(Salary)

       FROM Employees

   );

Correlated subqueries are used when you need to reference values from the outer query, while non-correlated subqueries are used when you need a constant or independent result. It's important to consider performance implications when using correlated subqueries, especially with large datasets.