MySQL Subqueries

What is a Subquery?

• A subquery is a query nested inside another SQL query.
• It is used to retrieve data that will be used in the main query.

Purpose of Subqueries

• Subqueries help in executing complex queries by breaking them down into smaller parts.
• They can be utilized in various SQL clauses such as SELECT, INSERT, UPDATE, and DELETE.

Types of Subqueries

1. Single-row subquery: Returns a single row and can be used with operators like =, >, <, etc.
2. Multiple-row subquery: Returns multiple rows and can be used with operators like IN, ANY, ALL.
3. Correlated subquery: A subquery that refers to columns from the outer query, executed once for each row processed by the outer query.

Key Concepts

• Nested Queries: Subqueries are often written inside parentheses.
• Aliasing: You can give an alias to the result of a subquery for easier reference.
• Performance: While subqueries can simplify complex queries, they might not always be the most efficient way to retrieve data.

Example of Subquery

Here are examples to illustrate how subqueries work:

Example 1: Single-row Subquery

SELECT name 
FROM employees 
WHERE id = (SELECT employee_id FROM orders WHERE order_id = 102);

This query selects the name of the employee who made the order with order_id 102.

Example 2: Multiple-row Subquery

SELECT name 
FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');

This query retrieves the names of employees who work in departments located in New York.

Example 3: Correlated Subquery

SELECT e1.name 
FROM employees e1 
WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2);

This query finds employees whose salary is greater than the average salary of all employees.

Conclusion

• Subqueries are a powerful feature in MySQL that allow for more complex data retrieval.
• Understanding how to use subqueries effectively can greatly enhance your SQL querying skills.