Subqueries

1What is a Subquery?✨

A subquery is a query nested inside another query. It allows you to perform more complex data retrieval by using the result of one query as input for another. Subqueries can be very useful in SQL for filtering data, calculations, or aggregating results.

2Why Use Subqueries?🎯

• ✓ Simplifies complex queries
• ✓ Enables filtering based on calculated results
• ✓ Provides flexibility in data retrieval
• ✓ Supports both scalar and multi-row results

3Basic Subquery Example💡

Here’s a simple example of a subquery. This query finds all employees who earn more than the average salary:

4SELECT *🚀

• FROM employees
• WHERE salary > (SELECT AVG(salary) FROM employees);

This subquery calculates the average salary and uses it to filter the employees.

5Using Subqueries in FROM Clause🔍

You can also use subqueries in the FROM clause. This allows you to treat the result of a subquery as a table. For example:

6SELECT department, AVG(salary) AS avg_salary⭐

FROM (SELECT * FROM employees WHERE active = true) AS active_employees 
GROUP BY department;

This query calculates the average salary of active employees by department.

7Correlated Subqueries🧩

A correlated subquery references columns from the outer query. It runs once for each row processed by the outer query. For instance:

8SELECT e1.name🎈

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

This finds employees earning more than the average salary in their department.

9Best Practices for Subqueries✨

• Use subqueries when necessary, but avoid overuse for performance.
• Ensure readability; sometimes JOINs are clearer.
• Test subqueries independently to ensure accuracy before combining them.

10What You'll Learn Next🎯

In the upcoming lessons, we will explore JOINs in depth, learning how to combine tables effectively and efficiently. You'll see how combining data can enhance your querying capabilities!