Day 17: Subqueries In SQL

Day 17 Of Learning

Image By Author

Note: I havent used subqueries myself till today so its new for me too, however I’m learning and I’ll definitely clear all of your confusions, if any.

Hey Guys,
Hope you’re good.

Today, we’ll be looking at subqueries in SQL. I said before that the concepts in the beginning will be simple but they will slowly keep getting complicated. However, no need to worry because there are some people between us who find subqueries easy as well, it all comes with practice.

Almost 2 more weeks of SQL left but don’t worry I have another series for you after that which I’ll reveal on the 30th day of SQL.

So, let’s get started.

What Are Subqueries?

Subqueries in SQL are basically queries within a query. They can also be called nested queries or inner queries. A subquery can be used within parentheses and is used to retrieve data required by the outer or main query.

Let me give you a visual representation

Image By Author

We can see above that the subquery exists within our complete query and the data output from our subquery is retrieved by the main query for use in the overall query.

Use Of Subqueries In SQL

Subqueries can be used in SELECT, FROM, WHERE, IN, and EXISTS statements. It helps us convert our code into smaller, more manageable parts.

Let’s get started with our code for today. We’ll be moving back to the Video Games dataset as we have finished working on JOINs.

So, let’s get started.

Image By Author

We have used a subquery to calculate avg of the overall sales of our video games store while our main query uses our sub query data as avg_sales.

Now let’s try using a sub query from the FROM and WHERE statements

Image By Author

I’ve used 2 subqueries in the query above to show you the difference between using the same avg function in 2 separate queries. I’ve used a subquery to calculate avg sales in the SELECT statement and at the same time I have used a sub query in the FROM statement.

Note that the avg_sales is calculating the averages based on the row while avg_sale has calculated the same result for all the rows as it has done a column level calculation instead of calculating row-wise. This is the difference between using a subquery in a SELECT statement and a FROM statement.

SELECT statement = avg_sale
FROM statement = avg_sales

Now, let’s introduce a subquery in the WHERE statement. I’ll be adding another subquery to this query so that you can see the overall result.

Image By Author

I have used 3 subqueries the details of which are given below:

1. Subquery in SELECT

2. Subquery in FROM

3. Subquery in WHERE

It is clear that you can use as many subqueries as possible in your main query and for many different reasons.

I have used a subquery to create a new column called avg_sale that shows me the average for the overall sales in the video game store. I have used a subquery to create a row wise average for my genre and used another subquery to filter out genres with a critic score greater than the average. You can always try to create as many subqueries in your query as you can but try not to over - complicate it.

Pros And Cons Of Subqueries

Pros:

• Improved Readability: Subqueries can make complex queries more readable and structured.

• Logic Breakdown: They allow breaking down a problem into smaller, manageable queries.

• Flexibility: Subqueries can be used in different parts of a query (SELECT, FROM, WHERE, etc.).

• Eliminates the Need for Joins: In some cases, subqueries can replace JOIN operations, simplifying query structure.

Cons:

• Performance Issues: Subqueries can be slow for large datasets, as they often require separate execution for each row in the outer query.

• Optimization Challenges: Some database engines struggle to optimize subqueries effectively, leading to inefficient execution plans.

• Not Always Necessary: Many subqueries can be rewritten as JOIN queries, which are often more efficient.

I have tried my best to explain subqueries, however as I said above that it is my first time working with subqueries, if there are any mistakes please correct them instead of criticising my queries.

Let’s move on to the exercise

Exercise

Here’s what you need to do today:

1. Load the Hospital Records dataset

2. Create a subquery from SELECT

3. Create a subquery from FROM

4. What is the difference? Tell us in the chat.

I’ll be creating a new thread in the chat for Subqueries and you guys can tell me what you saw in the output for both the SELECT and FROM statements.

---

I am a BI Engineer working at a Data And AI Firm. I’m trying to help newbies in Data land their first data job and impact as many fellow data professionals as I can. Here’s my contact info:

1. ajmal7809@gmail.com

2. Linkedin

3. bideveloper_ (Discord)