The Project | About Us | Contribute | Donations | License

HOME

Subqueries, also known as inner queries or nested queries, are queries 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, and WHERE clauses. This section will cover how to use subqueries in these different contexts.

Subqueries in the SELECT Clause

Subqueries in the SELECT clause are used to return a single value that can be included in the result set of the main query. This is useful for calculations or retrieving related data.

Example

Suppose we have two tables: employees and departments.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2)
);

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

INSERT INTO employees (id, name, department_id, salary) VALUES
(1, 'Alice', 1, 60000),
(2, 'Bob', 2, 70000),
(3, 'Charlie', 1, 80000);

INSERT INTO departments (id, name) VALUES
(1, 'HR'),
(2, 'Engineering');

We want to retrieve the name of each employee along with the name of their department.

SELECT 
    e.name AS employee_name,
    (SELECT d.name FROM departments d WHERE d.id = e.department_id) AS department_name
FROM 
    employees e;

Explanation

The subquery (SELECT d.name FROM departments d WHERE d.id = e.department_id) retrieves the department name for each employee.
The main query selects the employee's name and the result of the subquery.

Subqueries in the FROM Clause

Subqueries in the FROM clause are used to create a temporary table that can be referenced in the main query. This is useful for complex queries that require intermediate results.

Example

We want to find the average salary of employees in each department.

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

Explanation

The subquery (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) calculates the average salary for each department.
The main query joins the departments table with the result of the subquery to get the department name and the average salary.

Subqueries in the WHERE Clause

Subqueries in the WHERE clause are used to filter the results of the main query based on the results of another query. This is useful for conditions that depend on the results of another query.

Example

We want to find employees who earn more than the average salary of their department.

SELECT 
    e.name AS employee_name,
    e.salary
FROM 
    employees e
WHERE 
    e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

Explanation

The subquery (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) calculates the average salary for the department of each employee.
The main query selects employees whose salary is greater than the average salary of their department.

Practical Exercises

Exercise 1

Task: Retrieve the names of employees who work in the 'Engineering' department.

Solution:

SELECT 
    name
FROM 
    employees
WHERE 
    department_id = (SELECT id FROM departments WHERE name = 'Engineering');

Exercise 2

Task: Find the department with the highest average salary.

Solution:

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

Exercise 3

Task: List the names of employees who earn more than the average salary of all employees.

Solution:

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

Common Mistakes and Tips

Common Mistake: Using subqueries that return multiple rows where a single value is expected.
- Tip: Ensure that subqueries used in the SELECT and WHERE clauses return a single value. Use aggregate functions like MAX, MIN, AVG, etc., if necessary.
Common Mistake: Not aliasing subqueries in the FROM clause.
- Tip: Always use an alias for subqueries in the FROM clause to reference them easily in the main query.

Conclusion

Subqueries are powerful tools in SQL that allow you to perform complex queries by nesting one query within another. They can be used in the SELECT, FROM, and WHERE clauses to retrieve, filter, and manipulate data in various ways. Understanding how to use subqueries effectively will enhance your ability to write sophisticated SQL queries and solve complex data problems.

Using Subqueries in SELECT, FROM, and WHERE Clauses

Subqueries in the SELECT Clause

Example

Explanation

Subqueries in the FROM Clause

Example

Explanation

Subqueries in the WHERE Clause

Example

Explanation

Practical Exercises

Exercise 1

Exercise 2

Exercise 3

Common Mistakes and Tips

Conclusion

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL Queries

Module 3: Working with Multiple Tables

Module 4: Advanced Data Filtering

Module 5: Data Manipulation

Module 6: Advanced SQL Functions

Module 7: Subqueries and Nested Queries

Module 8: Indexes and Performance Optimization

Module 9: Transactions and Concurrency

Module 10: Advanced Topics

Module 11: SQL in Practice

Module 12: Final Project