MySQL Subquery

The MySQL subquery (also known as a nested query or inner query) is a query nested inside another query. Subqueries are enclosed in parentheses and can be used within various parts of a SQL statement, such as the SELECT, FROM, WHERE, or HAVING clauses. Subqueries are often used to retrieve data that will be used in the main query to filter or manipulate results. They are a powerful tool for querying complex data.

Here’s a basic structure of a subquery:

SELECT column1, column2
FROM table1
WHERE column3 operator (SELECT column4 FROM table2 WHERE condition);

In this structure:

• column1 and column2 are columns you want to retrieve from table1.
• column3 is a column from table1 used in a comparison.
• operator is a comparison operator (e.g., =, >, <, IN, etc.).
• The subquery (SELECT column4 FROM table2 WHERE condition) is enclosed in parentheses and retrieves data from table2 based on a condition.

Here are some common use cases for subqueries in MySQL:

1. Filtering Rows with Subquery in WHERE Clause:
   You can use a subquery in the WHERE clause to filter rows based on conditions involving data from another table.

   SELECT product_name
   FROM products
   WHERE category_id = (SELECT category_id FROM categories WHERE category_name = 'Electronics');

2. Subquery in the SELECT Clause:
   Subqueries can be used to calculate and return a single value in the SELECT clause.

   SELECT product_name, (SELECT AVG(price) FROM products) AS avg_price
   FROM products;

3. Subquery in the FROM Clause:
   Subqueries in the FROM clause can create temporary result sets to be used in the main query.

   SELECT category_name, COUNT(*) AS product_count
   FROM (SELECT * FROM products WHERE price > 100) AS expensive_products
   GROUP BY category_name;

4. Subquery in the HAVING Clause:
   Subqueries can be used in the HAVING clause to filter the results of aggregate functions.

   SELECT category_name, AVG(price) AS avg_price
   FROM products
   GROUP BY category_name
   HAVING AVG(price) > (SELECT AVG(price) FROM products);

5. Correlated Subqueries:
   In correlated subqueries, the subquery refers to a column from the outer query. These subqueries are used when you need to compare data between the main query and the subquery.

   SELECT employee_name
   FROM employees e
   WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

Subqueries in MySQL can be very flexible and powerful, but they can also impact query performance if not used carefully. It’s important to understand the execution plan and optimize your queries when working with subqueries in complex scenarios.