What is Subquery in SQL? Correlated and Non-Correalted SubQuery Example

If you are wondering what is correlated and non-correlated subqueries in SQL and looking to understand the difference between them then you have come to the right place. Earlier, I have shared free SQL and Database courses and today, I am going to talk about one of the common SQL concepts of subqueries, a query inside another query. There are two types of subqueries in SQL, correlated subquery and self-contained subquery, also known as nested, non-correlated, uncorrelated, or simply a subquery. The main difference between them is, self-contained (non-correlated) subqueries are independent of the outer query, whereas correlated subquery has a reference to an element from the table in the outer query.

In other words, In the case of the nested subquery, both primary and subquery can run independently. But, in the case of the correlated subquery, you can not run them individually because subquery refers to rows from the outer query.

Another key difference to know is that in a nested or non-correlated subquery, the subquery is executed first, and the result is inserted into the WHERE clause of the outer query. Correlated subqueries are just the opposite of this, where the OUTER query is executed first, and the subquery is executed for every row returned by the outer query.

One of the best examples to understand the difference between correlated and nested sub query is, writing an SQL query to find the Nth highest salary of an employee which can also be used to find the second highest salary. This is also one of the most common SQL queries from interviews if you like you can find more such queries here.

Btw, I expect that you are familiar with SQL and know different SQL commands and their meaning in a SQL query. If you are not, it's better you gain some experience with SQL by joining a good course like The Complete SQL Bootcamp course by Josh Portilla, a Data Scientist,  on Udemy. 

Now, let's see some of the key differences between correlated and uncorrelated subqueries in SQL.

Difference between self-contained and correlated subquery in SQL?

Without wasting any more of your time, let's deep dive into the main difference between a correlated and non-correlated subquery in SQL:

1) In the case of nested, the subquery is independent of the main query, and both can run independently, but in the case of a correlated subquery, you cannot run them individually.

2) A correlated subquery is slow because subquery runs for every row returned by the main query, but sometimes essential to find a solution.

3) In a non-correlated or nested subquery, the sub-query is analyzed and executed first, but in a correlated subquery, the main query is analyzed first, and based upon its result, the subquery is initiated.

4) In the case of non-correlated, the subquery is executed only once, and the result is used by the outer query, but in the case of correlated, the main query is executed first, and the subquery is executed repeatedly for every row returned by the main query.

5) In a correlated subquery, the column value used in the inner subquery refers to the column value present in the outer query forming a correlated subquery.

6) It's called correlated because the inner query is related to the outer query because the inner query references column of the outer subquery.  You can further check The Complete SQL Bootcamp course on Udemy to learn more about subqueries and see a few more live examples.

An Example of Correlated and Uncorrelated subquery in SQL

One of the most common examples of a correlated subquery is to find the Nth highest salaried employees like 2nd, 3rd, or 4th highest, you can write a correlated subquery like this :

SELECT name, salary 
FROM #Employee e1
WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM #Employee e2
WHERE e2.salary > e1.salary)SELECT name, salary 
FROM #Employee e1
WHERE 2-1 = (SELECT COUNT(DISTINCT salary) FROM #Employee e2
WHERE e2.salary > e1.salary)

Result:
name salary
Peter 5000

And, here is an example of a  non-correlated or normal subquery in SQL

mysql> SELECT COMPANY FROM Stock WHERE LISTED_ON_EXCHANGE IN (SELECT RIC FROM Market WHERE COUNTRY='United States' OR COUNTRY= 'INDIA');
+-------------------------+
| COMPANY                 |
+-------------------------+
| Google Inc              |
| Goldman Sachs GROUP Inc |
| InfoSys                 |
+-------------------------+

In this case, the inner query is executed first and then the result is used to execute the outer query. If you want to see more live examples, I suggest you check SQL for Newbs: Data Analysis for Beginners course on Udemy. It's an awesome hands-on course to really take your SQL skill to the next level.

When to use Correlated subquery in SQL?

Correlated subqueries are useful and improve SQL performance when the main query returns a few rows. The predicate in WHERE clause can use index. The correlated inner queries are performed through an index scan. This is very important if the table or tables against which the inner query is executed is large and the index scan has to retrieve only a smaller percentage of rows.

1) With EXISTS keyword
Subquery generally occurs with the WHERE clause, but it can also appear with the FROM and HAVING clauses.


Here is a nice summary of the difference between a correlated and non-correlated subquery in SQL:

That's all about the difference between correlated and nested subquery in SQL.  The nested query is also called the non-correlated or uncorrelated subquery. It's good to know the difference between these two important techniques of querying data from a database. It's also important to understand the performance implication of correlated subquery as it could be significant if your table is large and contains millions of rows, but, sometimes that's the only way to solve some tricky problems.

Related SQL Queries and Interview Questions

• 21 Frequently asked SQL Queries from Interviews (read here)
• What is the difference between View and Materialized View in the Oracle database? (answer)
• A difference between self and equijoin in SQL? (answer)
• The difference between WHERE and HAVING clause in SQL? (answer)
• How to find duplicate records in a table? (query)
• The difference between TRUNCATE and DELETE in SQL? (answer)
• What is the difference between Primary and Foreign keys in a table? (answer)
• 5 Websites to learn SQL for FREE (websites)
• 5 Free Courses to Learn MySQL database (courses)
• A difference between UNION and UNION ALL in SQL? (answer)
• A difference between LEFT and RIGHT OUTER JOIN in SQL? (answer)
• 5 Books to Learn SQL Better (books)
• How to join more than two tables in a single query (article)
• Difference between WHERE and HAVING clause (answer)
• 10 SQL queries from Interviews (queries)
• Top 5 SQL books for Advanced Programmers (books)
• Difference between SQL, T-SQL, and PL/SQL? (answer)
• Top 5 Online Courses to Learn SQL and Database (courses)

Thanks for reading this article and let me know how do you write SQL queries? which style do you use, or do you have your own style?

P. S. - If you are looking for a free course to start learning SQL and Database basics then I suggest you go through Introduction to Databases and SQL Querying course on Udemy. It's completely free, all you have is to create is a Udemy account and you can access the whole course.