SQL queries are the meat and potatoes of RDBMS CRUD (create, read, update, delete). But when your application or enterprise database grows, the need for intelligent queries to retrieve conditional, requirement-specific data becomes a must-have.

SQL is relatively comprehensive, encompassing many functionalities—each of which is well-suited for various business uses. One such functionality includes the use of sub-queries.

To make your codes efficient and effective, you can use sub-queries within your SQL codes to fetch data, manipulate existing variables, and achieve multiple goals in one go.

What Are SQL Sub-Queries?

A sub-query is a nested query, which works as a parameter within another main query. The sub-query is an inner query, while the main query is the outer query.

The sub-query supposedly returns the data as an in-parenthesis argument for the main query, while the main query further retrieves the final result.

Sub-queries are embedded within the Select statement or in the Where clause. Such a structure allows the sub-query to behave as a well-described data filtration condition. Unfortunately, sub-queries can only use the Group By commands and not Order By commands, as those are permitted in the main query only.

 

Usually, each sub-query comprises a single column with the Select clause. However, there are scenarios where the main query has multiple columns. A sub-query can be nested within another sub-query, making it into a nesting sub-query.

A sub-query’s stricture is as follows:

Select column_name from table where condition= 
(SELECT conditional_column FROM table) as alias;

For example, suppose you have the following table:

ID	First_name	Second_name	Agency_fee
1	John	Wick	5000
2	Robert	Graham	4000
3	Stephen	Hicks	8000
4	Bob	Marley	1000
5	Mary	Ellen	9000

In this table, to pull the names of people who are earning above the average agency fee, you can write a sub-query, instead of writing multiple lines of code.

 

Here’s how the query looks like:

Select * from agent_details
where Agency_Fee > (select avg(Agency_Fee) from agent_details);

The command before the > sign is the outer query, whereas, everything after the > sign is the inner query.

The inner query will calculate the average agency fee within the sub-query, and the outer query will show all values which are greater than the calculated average value.

How to Use Sub-Queries in SQL?

There are a few different ways in which you can use sub-queries in SQL.

Sub-Queries With Where Clause

One of the most basic structures of a sub-query in SQL is within the Where clause. It’s the simplest way to define what you are searching for. The select statement returns values as per the sub-query condition(s) and uses it as a parameter for the main query.

Query structure:

select * from table_name
where column_name = (select column_name from table_name);

Let’s explain this with an example.

Suppose you want to find the second-highest agency fee from the agency_details table. To do so, there are alternate functions within SQL; nonetheless, the best method is to use a sub-query.

Here’s how you can define the sub-query:

select *, max(Agency_fee)
from agent_details 
where Agency_fee < (select max(Agency_fee) from agent_details); 

The resulting statement will show you 8000, which is the second-highest fee in the given table. When the query runs, the sub-query calculates the maximum value from the list of fee. The highest fee amount (9000) is stored in memory.

Once this part is computed, the second part of the query is calculated, which finds the second-highest fee from the table (since the < sign is used). The end result is 8000, which is the second-highest fee in the table.

Sub-Queries Within the From Clause

Another variation within sub-queries is passing the condition in the from clause. As a similar concept, the inner query is processed first, and the outer query is processed afterwards. The inner query will filter on the data and show results where ID = 3.

Here’s the query for reference:

select a.* from (
select agency_fee from agent_details
where ID= 3) as a;

This is a very basic structure; however, the more complex your data tables, you will get more rows of data, which match your conditions.

Using Sub-Queries With Insert Into Statement

If you want to update an existing table with some new data rows, you can use the Insert Into statement. A sub-query can prove to be quite beneficial, if you want to add values based on a specific condition(s).

Query structure:

insert into table_name 
select * from table_name
where column_name = conditions;
select * from table_name;

Here’s an example on how you can use the insert into statement with the sub-query:

insert into agent_details
select * from agent_details
where agency_fee in (1000, 5000);
select * from agent_details;

Once the query runs, the values matching the condition will be inserted into the existing table again. The select * reference picks up all the columns together, and inserts it into the agent_details table as it is. The in statement is used to define multiple filter conditions at once.

Using Sub-Queries With Update Statement

There are situations wherein you want to update the underlying tables while running the queries. To do so, you can use the update statement along with the querying commands.

This is how you will write the sub-query to update the information in the table in one instance:

update table_name 
set column_name = new_value
where column_name = 
(select column_name from table_name where = );

Here’s an example demonstrating the use of the update statement:

UPDATE agent_details 
SET agency_fee = 35000
WHERE agency_fee = 
(SELECT agency_fee FROM agent_details WHERE First_name='John'); 
select * from agent_details;

The sub-query will filter on the column agency_fee and single out the row(s) where First_Name matches John. The outer query is executed next, wherein the agency fee is updated to 35000 for John Wick.

You can pass a select * statement to check the final results; you will notice the agency fee for John Wick is updated to 35000, as there is only instance matching the conditions defined in the query.

Using Sub-Queries With Delete Statement

Just like the update statement, wherein you are updating the rows of data within an existing table, the delete statement deletes row(s) of data based on a condition.

The delete statement structure is:

delete from table_name where variable/column name = 
(select column_name from table_name where = condition);

Here’s an example:

Delete from agent_details 
where First_name IN 
(select First_name from agent_details where agency_fee = 9000); 
select * from agent_details; 

Using Sub-Queries Within SQL

Sub-queries are an excellent feature within SQL, which can save you from writing endless lines of unnecessary code. When you are able to use the basic functionalities of sub-queries to do your bidding, you would never want to worry about going into the complexities of SQL coding.

It’s always best to enhance your existing SQL knowledge to ensure you are always on top of your game. Rest assured, SQL cheat sheets can give you a good idea on how to brush up on your basics in a single glance.