Corelated Subquries

Correlated subqueries are used for row-by-row processing. Each subquery is executed once for every row of the outer query.

A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement.

SELECT column1, column2, ....
FROM table1 outer
WHERE column1 operator
                    (SELECT column1, column2
                     FROM table2
                     WHERE expr1 = 
                               outer.expr2);

A correlated subquery is one way of reading every row in a table and comparing values in each row against related data. It is used whenever a subquery must return a different result or set of results for each candidate row considered by the main query. In other words, you can use a correlated subquery to answer a multipart question whose answer depends on the value in each row processed by the parent statement.

Nested Subqueries Versus Correlated Subqueries :

With a normal nested subquery, the inner SELECT query runs first and executes once, returning values to be used by the main query. A correlated subquery, however, executes once for each candidate row considered by the outer query. In other words, the inner query is driven by the outer query.
NOTE : You can also use the ANY and ALL operator in a correlated subquery.
EXAMPLE of Correlated Subqueries : Find all the employees who earn more than the average salary in their department.

SELECT last_name, salary, department_id
 FROM employees outer
 WHERE salary >
                (SELECT AVG(salary)
                 FROM employees
                 WHERE department_id =
                        outer.department_id);

Other use of correlation are in UPDATE and DELETE

CORRELATED UPDATE :

UPDATE table1 alias1
 SET column = (SELECT expression 
               FROM table2 alias2
               WHERE alias1.column =
                     alias2.column);

Use a correlated subquery to update rows in one table based on rows from another table.

CORRELATED DELETE :

DELETE FROM table1 alias1
 WHERE column1 operator
               (SELECT expression
                FROM table2 alias2
                WHERE alias1.column = alias2.column);

Use a correlated subquery to delete rows in one table based on the rows from another table.

Using the EXISTS Operator :

The EXISTS operator tests for existence of rows in the results set of the subquery. If a subquery row value is found the condition is flagged TRUE and the search does not continue in the inner query, and if it is not found then the condition is flagged FALSE and the search continues in the inner query.
EXAMPLE of using EXIST operator :
Find employees who have at least one person reporting to them.

SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS ( SELECT ’X’
FROM employees
WHERE manager_id =
outer.employee_id);

https://www.geeksforgeeks.org/sql-correlated-subqueries/

Cross Apply

Microsoft introduced the APPLY operator in SQL Server 2005. The APPLY operator is similar to the T-SQL JOIN clause as it also allows you to join two tables – for example, you can join an outer table with an inner table. The APPLY operator is a good option when, on one side, we have a table-evaluated expression that we want to evaluate for each row from the table we have on another side. So, the right-side table is processed for each row of the left-side table. The left-side table is evaluated first, and then the right-side table is evaluated against each row of the left-side table to generate the final result set. The final result set includes all columns from both tables.

The APPLY operator has two variations:

• CROSS APPLY
• OUTER APPLY

CROSS APPLY

CROSS APPLY is similar to INNER JOIN, but can also be used to join table-evaluated functions with SQL Tables. CROSS APPLY’s final output consists of records matching between the output of a table-evaluated function and an SQL Table.

OUTER APPLY

OUTER APPLY resembles LEFT JOIN, but has an ability to join table-evaluated functions with SQL Tables. OUTER APPLY’s final output contains all records from the left-side table or table-evaluated function, even if they don’t match with the records in the right-side table or table-valued function.

-----

Starting with SQL Server 2005, you can use the APPLY operator in a Transact-SQL query to join a table to a table-valued function so the function is evoked for each row returned from the table. For example, you might create a query that returns a list of employees from the Employee table. For each employee, you also want to return a list of the individual job positions that person has held in the company.

You can create a function that retrieves the employees’ positions and then evoke that function for each row returned from the Employee table. Your result set will then include a row for each position that an employee has held. For instance, if John has worked as a salesman and a regional supervisor, the result set will include two rows for John, one for each position. Any data returned from the Employee table will be repeated for each row, but the data returned by the function will be specific to each row in the function’s results.

https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-server-apply-basics/

https://codingsight.com/advanced-sql-cross-apply-and-outer-apply/