SQL Subqueries and Derived Tables

1. Subqueries

A subquery is a query that is embedded within another SQL query. It can be used in various clauses such as SELECT, FROM, WHERE, and HAVING.

Syntax

Edit Copy

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

Example

Consider a employees table and a departments table:

• employees: id, name, department_id
• departments: id, department_name

Edit Copy

SELECT name
FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE department_name = 'Sales');

This query retrieves the names of employees who work in the Sales department.

2. Derived Tables

A derived table is a subquery used in the FROM clause. It allows you to create a temporary result set that can be referenced in the outer query.

Syntax

Edit Copy

SELECT alias.column1, alias.column2
FROM (SELECT column1, column2 FROM table WHERE condition) AS alias;

Example

Using the same employees table, let's find the average salary per department:

Edit Copy

SELECT d.department_name, avg_salaries.avg_salary
FROM (SELECT department_id, AVG(salary) AS avg_salary
      FROM employees
      GROUP BY department_id) AS avg_salaries
JOIN departments d ON avg_salaries.department_id = d.id;

This query retrieves each department's name along with the average salary of its employees.

3. Combining Subqueries and Derived Tables

Subqueries and derived tables can be used together for complex data retrieval scenarios. For instance, you may want to filter results from a derived table using a subquery.

Example

Edit Copy

SELECT department_name, total_employees
FROM (SELECT d.department_name, COUNT(e.id) AS total_employees
      FROM departments d
      LEFT JOIN employees e ON d.id = e.department_id
      GROUP BY d.id) AS department_counts
WHERE total_employees > 10;

This query retrieves department names with more than 10 employees.

4. Conclusion

Subqueries and derived tables are powerful tools in SQL for complex data analysis and retrieval. By nesting queries and creating temporary result sets, you can perform sophisticated data operations efficiently.