MySQL Subquery Tutorial: A subquery (or nested query) is a query inside another query that helps fetch specific data dynamically. Subqueries are commonly used to filter results, perform calculations, and return data to the main (outer) query.
Although subqueries are easy to write and understand, they may impact performance. In many cases, JOINs can be a better alternative.
👉 What is a Subquery?
MySQL Subquery Tutorial: A subquery is a SELECT query inside another SQL query. The inner query runs first and returns a value (or multiple values) to the outer query, which then uses it to produce the final result.
Basic Syntax:
sql
SELECT column_name FROM table_name WHERE column_name = (SELECT column_name FROM another_table WHERE condition);
Key Features:
✔️ A subquery is enclosed within parentheses ().
✔️ The inner query is executed first, and the outer query uses its result.
✔️ Subqueries are used in SELECT, INSERT, UPDATE, DELETE statements.
👉Types of MySQL Subqueries
MySQL Subquery Tutorial: MySQL supports three types of subqueries:
| Type | Returns | Example Use Case |
| Scalar | A single value (1 row, 1 column) | Finding the highest-paid customer |
| Row | A single row (multiple columns) | Fetching details of the latest movie |
| Table | Multiple rows and columns | Finding customers who haven’t returned movies |
👉 MySQL Subquery Examples
Example 1: Fetching Data with a Subquery (Scalar Subquery)
Scenario: The MyFlix Video Library wants to find the movie category with the least number of titles.
sql
SELECT category_name FROM categories
WHERE category_id = (SELECT MIN(category_id) FROM movies);
✔️ How it Works:
- The inner query finds the smallest category_id.
- The outer query returns the corresponding category_name.
Use Case: Fetching a single specific value.
Example 2: Finding Customers Who Haven’t Returned Movies (Table Subquery)
Scenario: The library needs to call customers who haven’t returned rented movies.
sql
SELECT full_names, contact_number FROM members
WHERE membership_number IN (
SELECT membership_number FROM movierentals WHERE return_date IS NULL
);
✔️ How it Works:
- The inner query retrieves membership_number values where return_date is NULL.
- The outer query fetches full names and contact numbers of those customers.
Use Case: Fetching multiple results.
Example 3: Finding the Highest-Paying Member (Triple Nested Subquery)
MySQL Subquery Tutorial: Scenario: The management wants to reward the highest-paying member.
sql
SELECT full_names FROM members
WHERE membership_number = (
SELECT membership_number FROM payments
WHERE amount_paid = (SELECT MAX(amount_paid) FROM payments)
);
✔️ How it Works:
- The innermost query finds the highest amount paid.
- The middle query fetches the membership_number of the customer who paid that amount.
- The outer query retrieves the full_names of that customer.
Use Case: Complex filtering with nested queries.
👉Subqueries vs. Joins – Which One to Use?
MySQL Subquery Tutorial: While subqueries are simpler to write, they can be slower than JOINs.
| Feature | Subqueries | JOINs |
| Readability | Easier to read | More complex |
| Performance | Slower for large datasets | Faster |
| Flexibility | Can be used in SELECT, UPDATE, DELETE | Best for combining tables |
Best Practice: Use JOINs when dealing with large tables to boost performance (up to 500x faster in some cases). Use subqueries only if necessary.
👉Using Subqueries in INSERT, UPDATE, DELETE
INSERT Example – Copying Data from One Table to Another
sql
MySQL Subquery Tutorial: INSERT INTO archived_members (membership_number, full_names)
SELECT membership_number, full_names FROM members
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);
✔️ Copies customer records into the archived_members table.
UPDATE Example – Adjusting Prices Based on a Condition
sql
UPDATE movies
SET price = price * 1.1
MySQL Subquery Tutorial: WHERE category_id = (SELECT category_id FROM categories WHERE category_name = ‘Action’);
✔️ Increases movie prices by 10% for the “Action” category.
DELETE Example – Removing Inactive Users
sql
DELETE FROM members
WHERE membership_number IN (SELECT membership_number FROM payments WHERE amount_paid = 0);
✔️ Deletes users who have never made a payment.
👉 Summary – Key Takeaways
🔹MySQL Subquery Tutorial: Subqueries are queries within queries that help fetch dynamic data.
🔹 Types of subqueries:
- Scalar Subquery – Returns a single value.
- Row Subquery – Returns a single row with multiple columns.
- Table Subquery – Returns multiple rows and columns.
🔹 Subqueries can be used in SELECT, INSERT, UPDATE, DELETE queries.
🔹 JOINs are generally faster than subqueries and should be used when possible.
By mastering MySQL subqueries, you can write efficient and powerful queries for real-world applications!
Click To Open
👉 Tutorial-2: MYSQL UNION
👉Tutorial-3: MYSQL JOINS Tutorial
👉Tutorial-4: MYSQL VIEWS
👉Tutorial-5: MYSQL INDEX Tutorial
👉Tutorial-6: Application with MySQL
👉Tutorial-7: SQL vs MySQL
👉Tutorial-8: 50 Essential SQL Questions
