All Sub-queries in SQL

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.