The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Subqueries

Next ►
►

Subqueries, also known as inner queries or nested queries, are queries embedded within another SQL query. They are used to perform operations that require multiple steps, such as filtering data based on the results of another query. Subqueries can be used in various parts of a SQL statement, including the SELECT, FROM, WHERE, and HAVING clauses.

Key Concepts

  1. Definition: A subquery is a query nested inside another query.
  2. Types of Subqueries:
    • Single-row subqueries: Return a single row.
    • Multiple-row subqueries: Return multiple rows.
    • Multiple-column subqueries: Return multiple columns.
  3. Placement: Subqueries can be placed in different parts of a SQL statement:
    • In the SELECT clause.
    • In the FROM clause (also known as derived tables).
    • In the WHERE clause.
    • In the HAVING clause.

Practical Examples

Example 1: Subquery in the SELECT Clause

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    (SELECT department_name 
     FROM departments 
     WHERE departments.department_id = employees.department_id) AS department_name
FROM 
    employees;

Explanation:

  • This query retrieves the employee_id, first_name, and last_name from the employees table.
  • It also includes a subquery in the SELECT clause to fetch the department_name from the departments table based on the department_id of each employee.

Example 2: Subquery in the FROM Clause

SELECT 
    department_id, 
    AVG(salary) AS average_salary
FROM 
    (SELECT 
         department_id, 
         salary 
     FROM 
         employees 
     WHERE 
         salary > 50000) AS high_earners
GROUP BY 
    department_id;

Explanation:

  • This query calculates the average salary of employees earning more than $50,000.
  • The subquery in the FROM clause selects department_id and salary from the employees table where the salary is greater than $50,000.
  • The outer query then calculates the average salary for each department.

Example 3: Subquery in the WHERE Clause

SELECT 
    first_name, 
    last_name 
FROM 
    employees 
WHERE 
    department_id = (SELECT 
                         department_id 
                     FROM 
                         departments 
                     WHERE 
                         department_name = 'Sales');

Explanation:

  • This query retrieves the first_name and last_name of employees who work in the 'Sales' department.
  • The subquery in the WHERE clause fetches the department_id of the 'Sales' department from the departments table.
  • The outer query then selects employees with that department_id.

Example 4: Subquery in the HAVING Clause

SELECT 
    department_id, 
    COUNT(*) AS employee_count
FROM 
    employees
GROUP BY 
    department_id
HAVING 
    COUNT(*) > (SELECT 
                    AVG(employee_count) 
                FROM 
                    (SELECT 
                         department_id, 
                         COUNT(*) AS employee_count 
                     FROM 
                         employees 
                     GROUP BY 
                         department_id) AS dept_counts);

Explanation:

  • This query retrieves departments with an employee count greater than the average employee count across all departments.
  • The subquery in the HAVING clause calculates the average employee count.
  • The outer query groups employees by department_id and filters groups with a count greater than this average.

Practical Exercises

Exercise 1: Subquery in the SELECT Clause

Task: Write a query to list all employees along with their manager's name.

Solution:

SELECT 
    e.employee_id, 
    e.first_name AS employee_first_name, 
    e.last_name AS employee_last_name, 
    (SELECT 
         m.first_name || ' ' || m.last_name 
     FROM 
         employees m 
     WHERE 
         m.employee_id = e.manager_id) AS manager_name
FROM 
    employees e;

Exercise 2: Subquery in the WHERE Clause

Task: Write a query to find all employees who work in the 'IT' department.

Solution:

SELECT 
    first_name, 
    last_name 
FROM 
    employees 
WHERE 
    department_id = (SELECT 
                         department_id 
                     FROM 
                         departments 
                     WHERE 
                         department_name = 'IT');

Exercise 3: Subquery in the FROM Clause

Task: Write a query to find the average salary of employees in each department where the average salary is greater than $60,000.

Solution:

SELECT 
    department_id, 
    AVG(salary) AS average_salary
FROM 
    (SELECT 
         department_id, 
         salary 
     FROM 
         employees) AS dept_salaries
GROUP BY 
    department_id
HAVING 
    AVG(salary) > 60000;

Exercise 4: Subquery in the HAVING Clause

Task: Write a query to find departments with more employees than the average number of employees per department.

Solution:

SELECT 
    department_id, 
    COUNT(*) AS employee_count
FROM 
    employees
GROUP BY 
    department_id
HAVING 
    COUNT(*) > (SELECT 
                    AVG(employee_count) 
                FROM 
                    (SELECT 
                         department_id, 
                         COUNT(*) AS employee_count 
                     FROM 
                         employees 
                     GROUP BY 
                         department_id) AS dept_counts);

Common Mistakes and Tips

  • Misplacing Subqueries: Ensure subqueries are placed in the correct part of the SQL statement.
  • Performance Considerations: Subqueries can be resource-intensive. Consider using joins or other optimization techniques if performance is an issue.
  • Single vs. Multiple Rows: Be aware of whether your subquery returns a single value or multiple values, and use appropriate operators (=, IN, etc.).

Conclusion

Subqueries are a powerful tool in SQL that allow you to perform complex queries by nesting one query inside another. They can be used in various parts of a SQL statement to filter, aggregate, and transform data. Understanding how to effectively use subqueries will enhance your ability to write sophisticated SQL queries and solve complex data retrieval problems.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved