Correlated Subquery in SQL Explained

SQL is full of queries and different types of statements, knowing the hierarchy and the order of these statements is important to make an efficient codebase. In this article, we will learn about Correlated and Non-correlated subqueries in SQL with examples.

What are Subqueries in SQL?

An SQL query contains interrelated as well as non-related queries, it is important to identify them and their usage. Understanding these queries and applying them properly helps us to learn about them in depth. Establishing relations between the queries keeps the schema updated and provides an efficient way to deal with the problem statement.

Subqueries in SQL are defined as a query beneath another query. These queries are webbed inside each other. Often the outer query is known as the Primary or the Main query, whereas the inner query is known as the subquery.

The flow of queries starts from the innermost subquery and ends at the outermost query. They are nested and used inside a SELECT, UPDATE(other DDL commands) commands. It is important to know about subqueries to be able to create a set of interrelated functions.

Let us look at the types of subqueries in depth:

What are SQL Correlated Subqueries?

Correlated subqueries in SQL are queries within a query with some kind of relation between them, the inner query is referred to the outer query through a statement.

Unlike non-correlated subqueries, correlated subqueries are dependent on the primary query. The connected subqueries create a connection between the external and internal parameters. First, the inner relational query is executed and then the outer query which is dependent on the subquery is processed.

They are essential tools to perform multiple dependent operations in SQL. They allow the user to optimize the code and decrease the complexity of the solution and the code. Interdependence leads to a structural framework of the query allowing to get multiple results at one time. Every time a row is processed in the inner query, it is then executed in the outer or main query as well.

Syntax:

SELECT column1, column2
FROM table_name table_name_alias
WHERE condition operator (SELECT column1, column2
FROM table_name table_name_alias
WHERE
table_name2_alias.column = table_name1_alias.column);

Let us understand more about the correlated subqueries using an example.

Table: Example_Table

SQL Query:

SELECT id, name, amount
FROM example_table e
WHERE amount>

(
SELECT AVG(amount)
FROM example_table sub
WHERE sub.name = e.name
);

Output:

This output contains the avg amount of each name in the example table. First the required columns are selected then the subquery is written to get a calculated average of the amount against each name.

What are Non-Correlated Queries?

Non-correlated or unrelated queries in SQL are quite different from correlated queries. There is no condition for interdependence between the queries. The inner query doesn't need to have a relational dependence on the outer query.

Though it creates a structure and hierarchy there is no relation and the outer query does not have to wait for the inner query to be completely executed. These unrelated queries help to create non-coupled modules in the codes within the table.

Syntax:

SELECT column1, column2
FROM table
WHERE
condition
GROUP BY column_name;

Let us learn more about non-correlated queries through an example.

Table: Products

SQL Query:

SELECT product_id, product_name, price
FROM products
WHERE price > 500.00
GROUP BY product_name;

Output:

In the above example, we fetched data from the Products table with an unrelated or non-correlated query where the price of the product is greater than $500.

Do you know, How often is a non-correlated subquery executed?

An unrelated subquery is generally executed only once if the subquery in the row is the same for the group. Since they do not correlate with any other query there is no need for repetitive execution of the queries.

Key Differences Between Correlated and Non-Correlated Subqueries

Here are our main differences between both types of queries:

Conclusion

In a nutshell, we explored the SQL correlated and uncorrelated subqueries in depth. They are key tools when it comes to real-time data. In this article, we learned how correlated queries depend on each other to be executed, how uncorrelated queries are used, and the real-world application of these queries.