What’s New ?

The Top 10 favtutor Features You Might Have Overlooked

Read More

Correlated Subquery in SQL Explained

  • Feb 19, 2024
  • 8 Minutes Read
  • Why Trust Us
    We uphold a strict editorial policy that emphasizes factual accuracy, relevance, and impartiality. Our content is crafted by top technical writers with deep knowledge in the fields of computer science and data science, ensuring each piece is meticulously reviewed by a team of seasoned editors to guarantee compliance with the highest standards in educational content creation and publishing.
  • By Tanish Mallik
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.


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


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

Table: Example_Table

Correlated Subqueries SQL Example

SQL Query:

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

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



Correlated Subqueries Example 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.


SELECT column1, column2
FROM table
GROUP BY column_name;


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

Table: Products

Non-Correlated Queries SQL Example

SQL Query:

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



Non-Correlated Queries SQL Example 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:


Correlated Subqueries

Non-Correlated Subqueries

Dependency on Queries

Here the outer or the main query is dependent on the inner subquery.

No query is dependent on each other here.

Frequency of Execution

They are executed more than once.

They are executed only once.


They are less efficient as multiple interrelations make the structure complex.

They are more efficient as there is no dependence on any other query.


They are used when the data to be fetched is interrelated with another operation.

It is independent of the outer query and the result is directly from the table.


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.

FavTutor - 24x7 Live Coding Help from Expert Tutors!

About The Author
Tanish Mallik
I am Tanish, a final-year computer science student with extensive expertise in SQL. My background in the field of data science has honed my strong analytical skills, and I also have hands-on experience in developing data integration and optimization pipelines using Python and MSBI. I am eager to contribute innovative ideas and make an impact in the ever-evolving world of technology.