A subquery (or inner query, nested query) in SQL is a query embedded inside another SQL query. The subquery runs first, and its result is used by the outer query.

? Key Points about Subqueries

• Subqueries can be used in SELECT, INSERT, UPDATE, or DELETE statements.

• They can return a single value, a single row, or a set of rows.

• Subqueries are enclosed in parentheses ( ... ).

• Can be correlated (depend on outer query) or non-correlated (independent).

? Types of Subqueries

? Examples

1. Scalar Subquery in WHERE

Find employees whose salary is greater than the average salary:

SELECT EmployeeID, Name, SalaryFROM EmployeesWHERE Salary > (SELECT AVG(Salary) FROM Employees);

2. Table Subquery in FROM

List departments and the count of employees in each:

SELECT DepartmentID, EmployeeCountFROM (    SELECT DepartmentID, COUNT(*) AS EmployeeCount    FROM Employees    GROUP BY DepartmentID) AS DeptCountsWHERE EmployeeCount > 10;

3. Correlated Subquery

Get employees who earn more than the average salary in their department:

SELECT e.EmployeeID, e.Name, e.Salary, e.DepartmentIDFROM Employees eWHERE e.Salary > (    SELECT AVG(Salary)    FROM Employees    WHERE DepartmentID = e.DepartmentID);

4. Using Subquery in SELECT

Show employees and their department name using a subquery:

SELECT Name,       (SELECT DepartmentName FROM Departments WHERE Departments.DepartmentID = Employees.DepartmentID) AS DeptNameFROM Employees;

?? Tips

• Subqueries can impact performance; sometimes JOINs are faster.

• Use aliases for clarity, especially in subqueries used in FROM.

• Avoid returning multiple rows in scalar subqueries unless using IN or EXISTS.

Would you like me to help write a specific subquery for your dataset?