Understanding the Difference: Subquery vs. Correlated Query

In SQL, the order of execution can vary depending on the type of subquery used and the structure of the SQL statement. Let's break down the order of execution for different scenarios:

Ordinary Subqueries:

In ordinary subqueries, the subquery is executed first, and then the outer query is executed.
This means that the result of the subquery is used as input for the outer query.
The performance impact here is usually dependent on the complexity and size of the subquery, as it needs to be executed for each row processed by the outer query.

Correlated Subqueries:

In correlated subqueries, the outer query is executed first, and then the inner query is executed for each row returned by the outer query.
The inner query can reference columns or values from the outer query, making its execution dependent on the results of the outer query.
Correlated subqueries can have a significant impact on performance, especially if the inner query is complex or if it processes a large number of rows for each iteration of the outer query.
Careful optimization is needed to ensure efficient execution, as correlated subqueries can lead to performance bottlenecks if not used judiciously.

Correlated vs. Uncorrelated Subqueries:

A correlated subquery depends on the results of the outer query, as it references columns or values from the outer query.
An uncorrelated subquery operates independently of the outer query, retrieving data without relying on external references.
Correlated subqueries are often used when the inner query needs to filter its results based on values from the outer query, while uncorrelated subqueries are standalone and do not have such dependencies.

When Correlated Query gets Trigger?

• In a correlated subquery, the inner query is triggered for each row processed by the outer query. This correlation occurs when we establish a link between the outer query and the inner query, typically by referencing columns from the outer query within the inner query. Each time the outer query processes a row, the inner query is executed in relation to that specific row's values, hence the term "correlated." This dependency on the outer query's results distinguishes correlated subqueries from uncorrelated ones, which operate independently of their surrounding query context.

When does Subquery get Trigger?

• In a non-correlated subquery, also known as an independent subquery, the inner query is executed independently of the outer query. This means that the inner query is processed only once, irrespective of the number of rows processed by the outer query. Unlike correlated subqueries, there is no dependency on the outer query's results. Each time the outer query is executed, the inner subquery retrieves its data without reference to specific values from the outer query's current row. This independence allows the subquery to be evaluated just once, potentially making it more efficient compared to correlated subqueries, especially when dealing with large datasets.

Where Subqery can use?

SQL subqueries can be utilized within various SQL statements including SELECT, INSERT, UPDATE, and DELETE. They can be nested within clauses like SELECT, FROM, WHERE, JOIN, and HAVING. Additionally, SQL queries can be combined with operators such as =, <, >, >=, <=, IN, NOT IN, EXISTS, NOT EXISTS, among others.

Subquery Example Code

SELECT id, name FROM users WHERE LENGTH(name) > 
(
SELECT AVG (LENGTH(name)) FROM users
) 

Where correlated can used?

SQL correlated can be utilized within various SQL statements including SELECT, INSERT, UPDATE, and DELETE. They can be nested within clauses like SELECT, FROM, WHERE, JOIN, and HAVING. Additionally, SQL queries can be combined with operators such as =, <, >, >=, <= among others.

Correlated Subquery Example Code

SELECT name FROM employees e1
WHERE  salary >  ( 
SELECT AVG (salary)
FROM  employees e2
WHERE e1.team_id = e2.team_id
);