How to write SubQuery in SQL? Correlated vs Noncorrelated Sub Query Example

Hello guys, if you are struggling to write a sub query in SQL and need some guidance then you have come to the right place. In this article, I will not only teach you what is subquery, how it works, and which problem it solves but also teach you how to write both correlated and non-correlated sub-queries with real-world examples. SubQuery in SQL is a query inside another query. Some time to get particular information from a database you may need to fire two separate SQL queries, subQuery is a way to combine or join them in a single query. SQL query which is on the inner part of the main query is called the inner query while the outer part of the main query is called the outer query.

For example in below SQL query

SELECT name FROM City WHERE pincode IN (SELECT pincode FROM pin WHERE zone='west')

section not highlighted is OUTER query while section highlighted with grey is INNER query.

In this SQL tutorial, we will see both Correlated and non-correlated sub-query and their examples, some differences between correlated and noncorrelated subqueries, and finally, subquery vs join which is a classic debatable topic in SQL.

By the way, this SQL tutorial is next in series of SQL and database articles in Javarevisited like truncate vs delete and 10 examples of  SELECT queries. If you are new here then you may find those examples interesting.

If you are new to the SQL world, it's better to start with an online SQL course. That will help you to learn SQL better and quicker, and these kinds of articles will also make more sense once you have some SQL knowledge under your belt.

SubQuery Rules in SQL

Like any other concept in SQL, subquery also has some rules and you can only embed one query inside another by following rules :

1. subquery can be used in the insert statement.

2. subquery can be used in the select statement as a column.

3. subquery should always return either a scalar value if used with where clause or value from a column if used with IN or NOT IN clause.

Before going to understand the non-correlated and correlated subquery, let’s see the table and data which we are going to use in this example. Until you have an understanding of how the table looks like and what kind of data it stores it's a little difficult to understand queries. In this subquery example, we will use two tables Stock and Market. Stock holds different stocks and the Market holds all stock exchanges in the world.

mysql> select * from stock;
+---------+-------------------------+--------------------+
| RIC     | COMPANY                 | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| 6758.T  | Sony                    | T                  |
| GOOG.O  | Google Inc              | O                  |
| GS.N    | Goldman Sachs Group Inc | N                  |
| INDIGO  | INDIGO Airlines         | NULL               |
| INFY.BO | InfoSys                 | BO                 |
| VOD.L   | Vodafone Group PLC      | L                  |
+---------+-------------------------+--------------------+
6 rows in set (0.00 sec)

mysql> select  from Market;
+------+-------------------------+---------------+
| RIC  | NAME                    | COUNTRY       |
+------+-------------------------+---------------+
| T    | Tokyo Stock Exchange    | Japan         |
| O    | NASDAQ                  | United States |
| N    | New York Stock Exchange | United States |
| BO   | Bombay Stock Exchange   | India         |
+------+-------------------------+---------------+
4 rows in set (0.00 sec)

Noncorrelated subquery in SQL

There are two kinds of subquery in SQL one is called non-correlated and the other is called a correlated subquery. In a non-correlated subquery, the inner query doesn't depend on the outer query and can run as a stand-alone query. Subquery used along with IN or NOT IN SQL clause is a good example of Noncorrelated subquery in SQL. Let's a noncorrelated subquery example to understand it better.

NonCorrelated Subquery Example:

Let’s see the query  “Find all stocks from Japan”, If we analyze this query we know that stock names are stored in the Stock table while Country name is stored in the Market table, so we need to fire two queries first to get RIC for the Japanese market and then all stocks which are listed on that Market. we can combine these two queries into one SQL query by using subquery as shown in the below example:

mysql> SELECT COMPANY FROM Stock
       WHERE LISTED_ON_EXCHANGE = (SELECT RIC FROM Market WHERE COUNTRY='Japan');
+---------+
| COMPANY |
+---------+
| Sony    |
+---------+
1 row IN SET (0.02 sec)

Here part which is inside bracket is called inner query or subquery. As you see in this example of subquery, inner query can run alone and its not depended on outer query and that's why its called NonCorrelated query.

NonCorrelated Subquery Example with IN Clause SQL

NonCorrelated subquery are used along-with IN and NOT IN clause. here is an example of subquery with IN clause in SQL.

SQL query: Find all stocks from United States and India

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                 |
+-------------------------+

When Subquery is used along-with IN or NOT IN Clause it returns result from one column instead of Scaler value.

Correlated SubQuery in SQL

Correlated subqueries are the one in which inner query or subquery reference outer query. Outer query needs to be executed before inner query. One of the most common example of correlated subquery is using keywords exits and not exits. An important point to note is that correlated subqueries are slower queries and one should avoid it as much as possible.

Example of Correlated Subquery in SQL

Here is an example of Correlated subquery “Return all markets which has at least one stock listed on it.”

mysql> SELECT m.NAME FROM Market m WHERE m.RIC = (SELECT s.LISTED_ON_EXCHANGE FROM Stock s WHERE s.LISTED_ON_EXCHANGE=m.RIC);

+-------------------------+
| NAME                    |
+-------------------------+
| Tokyo Stock Exchange    |
| NASDAQ                  |
| New York Stock Exchange |
| Bombay Stock Exchange   |
+-------------------------+
4 rows IN SET (0.00 sec)

Here inner query will execute for every Market as RIC will be changed for every market.

Difference between Correlated and NonCorrelated Subquery

Now we have seen correlated and noncorrelated subqueries and their example it much easier to understand the difference between correlated vs noncorrelated queries. By the way, this is also one of the popular SQL interview questions, and its good to know few differences:

1.In the case of correlated subquery inner query depends on the outer query while in the case of a noncorrelated query inner query or subquery doesn't depend on outer query and is run on its own.

2.In the case of the correlated subquery, the outer query is executed before the inner query or subquery while in the case of the NonCorrelated subquery inner query executes before the outer query.

3.Correlated Sub-queries are slower than non-correlated subqueries and should be avoided in favor of SQL joins.

4.Common example of the correlated subquery is using exits and not exists keyword while non-correlated query mostly uses IN or NOT IN keywords.

SubQuery vs Join in SQL

Any information which you retrieve from the database using subquery can be retrieved by using different types of joins also. SQL is flexible and it provides different ways of doing the same thing. Some people find SQL Joins confusing and subquery specially noncorrelated more intuitive but in terms of performance SQL Joins are more efficient than subqueries.

Important points about SubQuery in DBMS

1.Almost whatever you want to do with subquery can also be done using join, it is just a matter of choice subquery seems more intuitive to many users.

2.Subquery normally returns a scaler value as a result or result from one column if used along with IN Clause.

3.You can use subqueries in four places: subquery as a column in select clause,

4.In the case of correlated subquery outer query gets processed before the inner query.

That's all about subquery in SQL. It's an important concept to learn and understand, as both correlated and non-correlated subquery is essential to solve SQL query-related problems. They are not just important from the SQL interview point of view but also from the Data Analysis point of view. If you want to become a programmer or Data scientist then you should learn and master SQL concepts like correlated subqueries. 

Other SQL and Database related articles from Javarevisited :

How to manage transaction in Database

Frequently used MySQL Server Command Part 1

Frequently used MySQL Server command part 2

How to use mysqldump utility for database backup

Different types of JDBC drivers in Java

4 JDBC performance tips Java programmer should know