MySQL for Beginners — Mastering Subqueries in Simple Steps.(Part-12)

 

Welcome to the MySQL for Beginners series! In this post, we’ll focus on an essential concept called subqueries. A subquery is like having one question inside another question, helping you to get more detailed information from your database. MySQL is a powerful and flexible database management system, and learning it can unlock many opportunities. This post is part of the “MySQL for Beginners” series, and today, we will focus on subqueries — an essential concept for efficiently managing databases. By the end of this blog, you’ll understand how to use subqueries, derived tables, and the EXISTS keyword in MySQL. We’ll even include examples with real data!

What Will This Blog Cover?

• Subqueries: What they are and how to use them with examples.
• Derived Tables: How temporary tables make your queries more efficient.
• EXISTS: A way to check if certain data is present.

1) Subquery

A subquery is a query inside another query. It’s useful when you need the result of one query to filter or calculate something in another query. Imagine wanting to know which students scored above the class average — this is where subqueries come in handy.

Let’s look at an example with a table of students and their scores.

Now, let’s say we want to find out which students scored higher than the class average. We’ll use a subquery for that.

Query:

SELECT student_name, score
FROM students
WHERE score > (SELECT AVG(score) FROM students);

1. The subquery (SELECT AVG(score) FROM students) calculates the average score.
2. The main query checks for students whose scores are higher than this average.

Average Score: To calculate the average score:

SELECT AVG(score) FROM students;

Average score = (85 + 78 + 92 + 67 + 80) / 5 = 80.4

Result :

In this case, both Alice and Charlie scored higher than the average score of 80.4. Subqueries like this help simplify your queries when dealing with comparisons or filtering.

2) Derived Table

A derived table is a temporary table that exists within a query. This is helpful when you need the result of one query to act as a table for another query. Imagine you have sales data and you want to calculate total sales per product, then use those totals in another query.

Now, let’s calculate total sales for each product and join that data with the products table.

Query:

SELECT product_name, total_sales
FROM (SELECT product_id, SUM(sales) AS total_sales FROM sales GROUP BY product_id) AS sales_summary
JOIN products ON products.product_id = sales_summary.product_id;

1. The inner query (SELECT product_id, SUM(sales) AS total_sales FROM sales GROUP BY product_id) calculates the total sales for each product.
2. The outer query uses the result of that subquery (as a derived table) to join with the products table and pull out the product names.

Result :

Derived tables allow you to store temporary results and reuse them in more complex queries without having to permanently store those results.

3) EXISTS

The EXISTS keyword is used to check if some data exists in a subquery. It returns TRUE if the subquery finds any data, otherwise it returns FALSE. It’s a handy way to filter data based on whether certain records are present in a related table.

Example: Checking Enrollments

Let’s say we want to find out which students are enrolled in a specific course, say course ID 101. We’ll use EXISTS to check for these enrollments.

Query:

SELECT student_name
FROM students
WHERE EXISTS (SELECT 1 FROM enrollments WHERE students.student_id = enrollments.student_id AND enrollments.course_id = 101);

1. The subquery checks if a student is enrolled in course 101.
2. If an enrollment exists, the main query retrieves the student’s name.

Result :

In this case, only Alice and Charlie are enrolled in course 101. The EXISTS function is highly efficient because it stops searching as soon as it finds a match, making it useful for improving query performance.

Conclusion:

In this post, we covered subqueries, derived tables, and the EXISTS keyword—three powerful tools to help you manage data more effectively in MySQL. Subqueries let you break down complex tasks into smaller, manageable queries, derived tables store temporary results for further processing, and EXISTS ensures that you’re only working with data that actually exists.

This is part of the MySQL for Beginners series, so stay tuned for more tutorials. If you found this helpful, make sure to follow me on LinkedIn and give this post a 👏👏if you enjoyed it!

“Learning SQL is like learning a new language — it takes practice, but the results are powerful!”

Let’s continue the journey of mastering MySQL together.

More Resources Books📚📘👇:

1. MySQL for Beginners

2. Querying MySQL

3. Learn SQL (using MySQL) in One Day