Subqueries Unraveled: Exploring SQL’s Hidden Power

#sql #datascience #data

As you work with tables and databases, sometimes you may require the output from one query, to act as input in another query, in order to get the desired output from the second query. This would require writing two queries. The first is meant to get the figure that will be used in the second query.

This process can be lengthy and might not be as reliable for reasons we are going to look at. To solve this issue is where subqueries come into play.

A subquery is an SQL query that is embedded inside another query. This will bring the idea of an inner query(the one embedded) and an outer query(the larger query). A subquery is commonly nested within the WHERE clause of another query. However, it can also be nested under SELECT, INSERT, UPDATE, or DELETE clauses within another query.

Let us consider an instance:

You have a table “Jobs_market” with columns “Salary” and “Job_group”
You want to get a list of Job groups where the salary is more than the average salary level in the market.
There are two ways you could do this;

Method 1
i ) Write a query to calculate the average salary being paid in the job market:

SELECT AVG(Salary) AS avg_salary
FROM Jobs_market;

This will output the average salary in the job market, say $ 10,000 annually.

ii ) Write a query that checks the job groups where the salary is more than the average salary, $ 10,000

SELECT 
    Job_group, 
    Salary
FROM Jobs_market
WHERE Salary > $ 10,000;

This query will give the job groups where the salary is greater than $10,000, which is the average salary we calculated in the first query.

Method 2
Here, we will use the idea of subqueries. We will embed the query that outputs the average salary within the query that gives the list of job groups that earn above the average salary.

SELECT 
    Job_group, 
    Salary
FROM Jobs_market
WHERE Salary > (
SELECT AVG(Salary) AS avg_salary
FROM Jobs_market
);

The output will be the same as the output in method 1, only that we did not run the queries independently, and did not hardcode the figure from the first query, copy it, and paste the value into the second query.

These are the reasons why method 1 might not be as reliable;

Using a subquery can be done faster and consumes lesser space when writing the query. This will save time and make the SQL script more readable as compared to using multiple queries.
In the case that the salaries being paid in the job market change at a particular time, method 1 will not recognize the changes as it uses a hard-coded value of “$10,000”. The subquery in method 2 however will not use a hard-coded value but will always compute its own average each time the query is run, thus using the real-time values in the salary column.
It is important to note that:
Subqueries are always enclosed in parenthesis( ) as you will notice in our illustration above.
A subquery can be put within a subquery as many times, as they build up to form the larger main query.
Subqueries are executed from the inner-most query, towards the outermost query, to return the desired output. When querying data, ensure that each subquery is working as desired to get the correct final output.

Top comments (6)

Lucas Barret • May 22 '23

Really cool article, are you already written about Common Table expression ?

John • May 22 '23

Hello Lucas, Thank you very much.

I am yet to write about Common Table Expression's powerful abilities to enhance query flexibility and readability. It is an area of interest that I would love and enjoy writing about.