👉What is a Subquery in MySQL?
MySQL SubQuery Tutorial: A subquery is a SELECT query nested inside another query. It helps retrieve data dynamically for complex filtering and calculations. The inner query determines results for the outer query.
👉Types of Subqueries in MySQL
1. Scalar Subqueries (Single Value Return)
MySQL SubQuery Tutorial: A scalar subquery returns a single value (one row, one column).
🔹 Example: Find the category with the fewest movie titles.
sql
SELECT category_name
FROM categories
WHERE category_id = (SELECT MIN(category_id) FROM movies);
✅ How it works:
- The inner query (SELECT MIN(category_id) FROM movies) finds the smallest category ID.
- The outer query retrieves the category name for that ID.
2. Row Subqueries (Single Row, Multiple Columns)
MySQL SubQuery Tutorial: A row subquery returns a single row but can have multiple columns.
🔹 Example: Find the member’s name and phone number who has rented a movie but hasn’t returned it.
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 finds members who have not returned their rented movies.
- The outer query fetches the names and contact details of those members.
3. Table Subqueries (Multiple Rows and Columns)
MySQL SubQuery Tutorial: A table subquery returns multiple rows and columns.
🔹 Example: Find 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 maximum amount paid.
- The middle query finds which member paid that amount.
- The outer query retrieves the member’s name.
👉Subqueries vs. Joins: Which One to Use?
| Feature | Subqueries | Joins |
| Ease of Use | Simple and readable | More complex but powerful |
| Performance | Can be slow, especially on large tables | Faster because it avoids nested queries |
| Flexibility | Works well for nested conditions | Better for combining multiple tables efficiently |
| Recommended Use | When data dependency is required | When retrieving data from multiple tables |
Pro Tip:
Using JOINs instead of subqueries can improve performance up to 500 times! Always prefer JOINs unless subqueries are necessary.
👉Key Takeaways: When to Use Subqueries?
✔ MySQL SubQuery Tutorial: Subqueries are queries inside another query, used for filtering and calculations.
✔ Three types: Scalar (single value), Row (single row, multiple columns), Table (multiple rows & columns).
✔ Can be used in SELECT, INSERT, UPDATE, and DELETE queries.
✔ JOINs are faster and should be used over subqueries for performance optimization.
Master MySQL subqueries to write efficient and powerful queries!
Click To Open
👉Tutorial-2: MYSQL JOINS Tutorial
👉Tutorial-3: MYSQL UNION
👉Tutorial-4: MYSQL Views
👉Tutorial-5: MYSQL Index
