SQL Subquery

Understanding SQL Subqueries

A subquery, also known as an inner query or nested query, is a query within another SQL query. The subquery provides data to the main (outer) query and is typically enclosed in parentheses. Subqueries are useful for breaking complex problems into smaller, more manageable parts.

Types of Subqueries

1. Single-Row Subquery: Returns one row and is often used with comparison operators like =, <, or >.
2. Multi-Row Subquery: Returns multiple rows and is used with operators like IN, ANY, or ALL.
3. Scalar Subquery: Returns a single value, often used in the SELECT or WHERE clause.
4. Correlated Subquery: References columns from the outer query and is executed once for each row processed by the outer query.
5. Nested Subquery: A subquery within another subquery.

Syntax of SQL Subqueries

SELECT column1, column2, ...
FROM table_name
WHERE column_name operator (SELECT column_name FROM table_name WHERE condition);

Key Points to Remember

1. Parentheses: Subqueries are enclosed in parentheses.
2. Placement: Subqueries can be placed in:
   • The SELECT clause.
   • The FROM clause (as a derived table).
   • The WHERE clause.
3. Performance: Subqueries can be slower than joins for large datasets, as they may execute multiple times.
4. Return Types: Subqueries can return single or multiple values depending on their use case.

Examples of SQL Subqueries

1. Subquery in the WHERE Clause

Find employees whose salary is higher than the average salary in the company.

SELECT employee_id, name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Explanation:

• The inner query calculates the average salary.
• The outer query retrieves employees whose salary exceeds the average.

2. Subquery in the FROM Clause

Find the average salary of each department.

SELECT department_id, AVG(salary) AS avg_salary
FROM (SELECT department_id, salary FROM employees) AS subquery
GROUP BY department_id;

Explanation:

• The subquery selects department_id and salary.
• The outer query calculates the average salary for each department.

3. Subquery in the SELECT Clause

Show each employee’s salary as a percentage of the total company salary.

SELECT name, salary, 
       (salary / (SELECT SUM(salary) FROM employees)) * 100 AS salary_percentage
FROM employees;

Explanation:

• The subquery calculates the total company salary.
• The outer query calculates each employee’s salary percentage.

4. Correlated Subquery

Find employees who earn more than the average salary of their respective departments.

SELECT e1.employee_id, e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (SELECT AVG(e2.salary)
                   FROM employees e2
                   WHERE e1.department_id = e2.department_id);

Explanation:

• The subquery calculates the average salary for the department of the current employee (e1).
• The outer query retrieves employees earning more than their department’s average.

5. Subquery with IN Operator

Find departments where employees work on projects.

SELECT DISTINCT department_id
FROM employees
WHERE department_id IN (SELECT department_id FROM projects);

Explanation:

• The subquery selects department IDs from the projects table.
• The outer query retrieves departments from the employees table that matches those IDs.

6. Subquery with EXISTS Operator

Check if a department has employees.

SELECT department_id, department_name
FROM departments d
WHERE EXISTS (SELECT 1
              FROM employees e
              WHERE e.department_id = d.department_id);

Explanation:

• The subquery checks if employees exist for a given department.
• The outer query returns departments with employees.

7. Subquery with ALL

Find employees whose salary is greater than all employees in a specific department.

SELECT name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 3);

Explanation:

• The subquery selects all salaries in department 3.
• The outer query retrieves employees whose salary exceeds all those salaries.

Real-World Applications of SQL Subqueries

1. Data Filtering:
   Use subqueries to filter rows based on complex conditions or aggregated data.

2. Hierarchical Queries:
   Fetch parent-child relationships or hierarchies in data, like managers and employees.

3. Comparative Analysis:
   Perform comparisons between datasets or calculate metrics like rankings and averages.

4. Dynamic Joins:
   Use subqueries as dynamic tables to create temporary, filtered datasets.

5. Data Aggregation:
   Aggregate data at different levels (e.g., company-wide and department-wide).

Advantages of SQL Subqueries

• Modular Design: Break down complex problems into simpler parts.
• Reusability: Subqueries can be reused in different parts of the main query.
• Flexibility: Can be used in various clauses (SELECT, WHERE, FROM).

Disadvantages of SQL Subqueries

1. Performance:
   Subqueries can be slower compared to joins, especially in large datasets.
2. Readability:
   Deeply nested subqueries can be hard to understand and maintain.
3. Not Always Supported:
   Some databases have limitations on the usage of subqueries (e.g., correlated subqueries in MySQL before version 8.0).

Subquery vs. JOIN

Aspect	Subquery	JOIN
Purpose	Break down complex problems	Combine data from multiple tables
Performance	Slower for large datasets	Faster due to optimized execution
Readability	Better for simple tasks	Clearer to combine tables
Execution	Executes inner query first	Executes in a single operation

Conclusion

SQL subqueries are a versatile and powerful feature for solving complex data problems. Whether used for filtering, aggregating, or creating temporary datasets, subqueries allow for flexibility and modularity in query design. However, for large datasets or performance-critical applications, consider alternatives like joins or indexed views.