In this article, we are going to talk about something called SQL Subquery. Think of them like smaller parts inside a bigger thing. Just like a puzzle has pieces, our SQL queries can have subqueries.
We’ll start from the basics, answering questions like “What is a subquery?” and “Why do we need them?” We’ll also look at different types of subqueries and where we can use them in our SQL queries.
Understanding SQl Subquery is important because they help us do more with our database. They are like special tools that make our queries work better. We’ll go through examples and see how to use subqueries with different parts of our SQL commands. So, whether you’re new to SQL or want to know more, join us as we explore the world of SQL subqueries together.
An SQL Subquery is like a tiny question inside a bigger one. It helps break down complex tasks by getting specific information and then using that answer in the main question. It’s a helpful trick in SQL for handling data more effectively.
There are 3 types of SQL Subquery. They are :
Let’s learn each of them one by one :
The query that we learned at the beginning is a perfect example of an Ordinary Subquery.
Let’s take some more examples :
We have the Table: ORDERS as –
Subqueries with SELECT Statement
Question: Show the category-wise percentage of Sales done.
We will write the query as :
SELECT Category , SUM(Sales)/(SELECT SUM(Sales) FROM ORDERS) AS PERC_SALES FROM ORDERS GROUP BY Category
How does the query work?
First, the inner query gets executed.
SELECT SUM(Sales) FROM ORDERS
Then using this value the outer query executes.
FINAL OUTPUT :
Subqueries with WHERE Clause
Question: Show the records where the sales are more than the overall average sales.
We will write the query as:
SELECT * FROM ORDERS WHERE Sales > (SELECT AVG(Sales) FROM ORDERS)
How does the query work?
First, the inner query gets executed.
SELECT AVG(Sales) FROM ORDERS
It outputs :
Then using this value the outer query executes.
FINAL OUTPUT :
Subqueries with HAVING Clause
Question: Show the items where average sales for each item is more than the overall average.
We will write the query as:
SELECT Product , AVG(Sales) FROM ORDERS GROUP BY Product HAVING AVG(Sales) > (SELECT AVG(Sales) FROM ORDERS)
How does the query work?
By the time you must be knowing that: First the inner query gets executed.
SELECT AVG(Sales) FROM ORDERS
It outputs :
Then using this value the outer query executes.
FINAL OUTPUT :
Important features of ORDINARY SUBQUERIES :
Let’s take a simple example :
To get all the records in the table we can do,
SELECT * FROM ORDERSWe can replace the table(ORDERS) here with a subquery as well (SELECT * FROM ORDERS)
This is known as an inline view.
SELECT * FROM
( SELECT * FROM ORDERS) TTOUTPUT :
Let’s move on to a more complex question.
Question: Display the record with the 2nd lowest sales.
We will write the query as:
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY Sales) AS row_num FROM ORDERS) AS T WHERE row_num=2
How does the query work?
By the time you must be knowing that: First, the inner query gets executed.
SELECT *, ROW_NUMBER() OVER(ORDER BY Sales) AS row_num FROM ORDERS
It outputs :
Then using this value the outer query executes. The OUTPUT table above is used as a table to retrieve the second lowest sales record.
FINAL OUTPUT :
Important features of INLINE VIEW :
But why do we actually need to put a SELECT Statement in the FROM Clause?
Here we are creating a column named row_num in the inner query and we want to filter it which is done with the help of the outer query.
Let us learn this with the help of an example :
Question: Display all the products whose sales are more than the average sales in their category.
We will write the query as:
SELECT Order_Id , Product, Category
FROM ORDERS ord
WHERE Sales >= (SELECT AVG(Sales) FROM ORDERS WHERE Category = ord.Category)How does the query work?
The subquery is executed once for every row of the outer query.
FINAL OUTPUT :
Important features of CORRELATED SUBQUERIES:
A Subquery can be used with the SELECT, INSERT, UPDATE, and DELETE statements.
Using SQL subqueries has some Good advantages:
So, using SQL subqueries makes your queries simpler, clearer, and more efficient!
This dataset will be used for all the examples that we will be taking in this article.
We have a table named ORDERS that contains the records of the sales made in a particular period of time for various products under multiple categories.
It contains columns like: Order_Id, Category , Product, Sales
Let’s begin our learning with a situation!
We have the Table: ORDERS as –
Suppose that I want to see only those entries where the sales made is more than the average sales of all the entries received till now.
So my question to you is: Can you show the records where the sales are more than the overall average sales.
We can try something like :
SELECT * FROM ORDERS WHERE Sales > AVG(Sales)
But we get an ERROR!
“An aggregate may not appear in the WHERE clause unless it is in a subquery contained in an outer reference”
Hold on a second, What does the word subquery mean here?
It is a query within a query
We can perform this task easily using something known as a ‘SUBQUERY’
How?
We will write the query as:
SELECT * FROM ORDERS WHERE Sales > (SELECT AVG(Sales) FROM ORDERS)
SELECT AVG(Sales) FROM ORDERS is our Subquery here(also known as the inner query)
SELECT AVG(Sales) FROM ORDERS
It outputs :
This output is then used as an input for our main query(also known as the outer query)
SELECT * FROM ORDERS WHERE Sales > (SELECT AVG(Sales) FROM ORDERS)
We get the final output as :
Let’s officially define what a Subquery is :
A Subquery is a query within another SQL query.
Note that it must always be enclosed within parentheses.
When we want to do some operations or perform some calculations at a different level as we did in the inner query explained above (We calculated the overall average sales)
and other operations at a different level like we did in the outer query explained above (We compared the individual sales with the overall sales for each record).
In a nutshell, When we want to perform operations at different levels then we use a Subquery.
SELECT, INSERT, UPDATE, and DELETE statements.
In this article, we checked out SQL subquery, a helpful tool for making our data questions easier. We discussed different types, such as ordinary, inline view, and correlated subqueries. These tools break down big tasks, making our data searches better. Knowing when to use them is critical to getting the most out of our information.
If you still have any questions on subqueries in SQL, do let me know in the comments below.
We will get on a quick chat there. To clear your SQL concepts further go follow this article.
You can connect with me on LinkedIn: https://www.linkedin.com/in/ayushi-gupta25/
Lorem ipsum dolor sit amet, consectetur adipiscing elit,
