All Sub-queries in SQL
Tags: sql, database, backend, interview
Published: 2024-06-29 13:20:17

**Subqueries, also known as inner queries or nested queries, are a powerful feature in SQL that allow you to perform more complex and flexible queries.**

- Schema
```
CREATE TABLE Department (
    Department_id INT PRIMARY KEY,
    Department_name VARCHAR(50) NOT NULL
);

CREATE TABLE Employee (
    Employee_id INT PRIMARY KEY,
    Employee_name VARCHAR(50) NOT NULL,
    Salary DECIMAL(10,2) NOT NULL,
    Department_id INT,
    FOREIGN KEY (Department_id) REFERENCES Department(Department_id)
);

INSERT INTO Department (Department_id, Department_name) VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Marketing'),
(4, 'Sales'),
(5, 'Finance');

INSERT INTO Employee (Employee_id, Employee_name, Salary, Department_id) VALUES
(1, 'Alice', 5000, 1),
(2, 'Bob', 7000, 1),
(3, 'Carol', 6000, 2),
(4, 'Dave', 6000, 2),
(5, 'Eve', 8000, 3),
(6, 'Frank', 9000, 3),
(7, 'Grace', 3000, 4),
(8, 'Hank', 4000, 4),
(9, 'Irene', 10000, 5),
(10, 'Jack', 9500, 5);
```



- Subqueries in the SELECT Clause
Subqueries can be used in the SELECT clause to return a **single value** that will be included in the result set.

Example:

```
SELECT 
    Employee_id, 
    Employee_name, 
    (SELECT Department_name FROM Department WHERE Department_id = e.Department_id) AS DepartmentName
FROM 
    Employee e;
```

- Subqueries in the FROM Clause
Subqueries can create a temporary table that can be joined with other tables in the FROM clause.

Example:

```
SELECT 
    e.Employee_id, 
    e.Employee_name, 
    dept.Department_name
FROM 
    Employee e
INNER JOIN 
    (SELECT Department_id, Department_name FROM Department) dept
ON 
    e.Department_id = dept.Department_id;
```

- Subqueries in the WHERE Clause
Subqueries can filter rows based on the result of another query.

Example:

```
SELECT 
    Employee_id, 
    Employee_name, 
    Salary
FROM 
    Employee e
WHERE 
    Salary = (SELECT MAX(Salary) FROM Employee WHERE Department_id = e.Department_id);
```

- Subqueries in the HAVING Clause
Subqueries can filter groups based on aggregate functions in the HAVING clause.

Example:

```
SELECT 
    Department_id, 
    AVG(Salary) AS AvgSalary
FROM 
    Employee
GROUP BY 
    Department_id
HAVING 
    AVG(Salary) > (SELECT AVG(Salary) FROM Employee);
```

- Subqueries in the JOIN Condition
Subqueries can be part of the join condition to dynamically determine the join criteria.

Example:

```
SELECT 
    e.Employee_id, 
    e.Employee_name, 
    d.Department_name
FROM 
    Employee e
INNER JOIN 
    Department d ON e.Department_id = d.Department_id
AND 
    e.Salary > (SELECT AVG(Salary) FROM Employee WHERE Department_id = e.Department_id);
```

- Subqueries in the INSERT Statement
Subqueries can provide the values to insert into a table.

Example:

```
INSERT INTO 
    Employee (Employee_id, Employee_name, Salary, Department_id)
SELECT 
    new_employee_id, 
    new_employee_name, 
    new_salary, 
    new_department_id
FROM 
    (SELECT 
         11 AS new_employee_id, 
         'John Doe' AS new_employee_name, 
         5000 AS new_salary, 
         1 AS new_department_id
     ) new_employee;
```

- Subqueries in the UPDATE Statement
Subqueries can determine the values to update in a table.

Example:

```
UPDATE 
    Employee
SET 
    Salary = (SELECT AVG(Salary) FROM Employee WHERE Department_id = Employee.Department_id)
WHERE 
    Employee_id = 1;
```

- Subqueries in the DELETE Statement
Subqueries can determine which rows to delete from a table.

Example:

```
DELETE FROM 
    Employee
WHERE 
    Department_id IN (SELECT Department_id FROM Department WHERE Department_name = 'HR');
```

**Conclusion**
Subqueries are a versatile and essential tool in SQL, allowing for powerful and flexible data retrieval and manipulation. Understanding where and how to use subqueries can significantly enhance your ability to write complex SQL queries efficiently. By mastering the use of subqueries, you can tackle a wide range of data challenges in SQL.