Sub-Queries in SQL

Subqueries in SQL

A subquery (also known as an inner query or nested query) is a query that is nested inside another query. It is used to perform operations that require intermediate results before the final output. Subqueries are typically used in SELECT, INSERT, UPDATE, and DELETE statements.

Subqueries allow you to perform complex queries in multiple steps, making them a powerful tool for SQL operations. There are two main types of subqueries: Single-row subqueries and Multi-row subqueries.

---

Types of Subqueries

1. Single-Row Subqueries
2. Multi-Row Subqueries
3. Correlated Subqueries
4. Scalar Subqueries
5. Inline Views

---

1. Single-Row Subquery

A single-row subquery returns a single value (one row and one column). It is often used with comparison operators like =, >, <, <=, >=, and <>.

Syntax:

SELECT column_name
FROM table_name
WHERE column_name = (SELECT column_name FROM table_name WHERE condition);

Example: Suppose we have the Employees table:

EmployeeID	Name	Salary
1	Alice	50000
2	Bob	60000
3	Charlie	70000

To find the employees whose salary is greater than Alice's salary:

SELECT Name
FROM Employees
WHERE Salary > (SELECT Salary FROM Employees WHERE Name = 'Alice');

Explanation:

• The subquery (SELECT Salary FROM Employees WHERE Name = 'Alice') retrieves Alice's salary.
• The outer query then compares the salary of each employee with Alice's salary to return employees with higher salaries.

Result:

Name
Bob
Charlie

---

2. Multi-Row Subquery

A multi-row subquery returns multiple rows and is typically used with operators such as IN, ANY, or ALL to compare multiple values.

Syntax:

SELECT column_name
FROM table_name
WHERE column_name IN (SELECT column_name FROM table_name WHERE condition);

Example: To find the names of employees who work in either the 'HR' or 'IT' department (assuming we have a Departments table):

DepartmentID	DepartmentName
1	HR
2	IT
3	Marketing

SELECT Name
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE DepartmentName IN ('HR', 'IT'));

Explanation:

• The subquery (SELECT DepartmentID FROM Departments WHERE DepartmentName IN ('HR', 'IT')) returns the department IDs for 'HR' and 'IT'.
• The outer query retrieves employee names whose DepartmentID matches the returned values.

Result:

Name
Alice
Bob

---

3. Correlated Subquery

A correlated subquery is a subquery that references columns from the outer query. Unlike a normal subquery, the correlated subquery is executed once for each row processed by the outer query.

Syntax:

SELECT column_name
FROM table_name t1
WHERE column_name = (SELECT column_name FROM table_name t2 WHERE t1.column_name = t2.column_name);

Example: Suppose we have two tables, Employees and Departments, where each employee has a DepartmentID and a ManagerID (which points to another employee):

• Employees:

  EmployeeID	Name	DepartmentID	ManagerID
  1	Alice	1	NULL
  2	Bob	1	1
  3	Charlie	2	1
  4	David	2	3

• Departments:

  DepartmentID	DepartmentName
  1	HR
  2	IT

To find the employees who manage others, we can use a correlated subquery:

SELECT Name
FROM Employees e1
WHERE EXISTS (
    SELECT 1
    FROM Employees e2
    WHERE e1.EmployeeID = e2.ManagerID
);

Explanation:

• The subquery SELECT 1 FROM Employees e2 WHERE e1.EmployeeID = e2.ManagerID checks if there is any employee (e2) whose ManagerID matches the EmployeeID of the outer query (e1).
• The EXISTS condition returns true if the subquery finds a match, indicating that the outer query's employee manages others.

Result:

Name
Alice
Charlie

---

4. Scalar Subquery

A scalar subquery returns a single value (one row and one column), just like a single-row subquery. However, it is used in places where an expression is expected, such as in the SELECT list, WHERE clause, or HAVING clause.

Example: To list employees and compare their salary with the average salary in the company:

SELECT Name, Salary, 
       (SELECT AVG(Salary) FROM Employees) AS AverageSalary
FROM Employees;

Explanation:

• The scalar subquery (SELECT AVG(Salary) FROM Employees) calculates the average salary of all employees.
• The outer query retrieves the employee's name, salary, and the calculated average salary.

Result:

Name	Salary	AverageSalary
Alice	50000	60000
Bob	60000	60000
Charlie	70000	60000

---

5. Inline Views (Subquery in the FROM Clause)

An inline view is a subquery that is used in the FROM clause. It acts as a virtual table, allowing you to query the result set as though it were a regular table.

Syntax:

SELECT column_name
FROM (SELECT column_name FROM table_name WHERE condition) AS alias_name;

Example: To find the average salary in each department, you could use an inline view to first calculate the average salary per department:

SELECT DepartmentID, AVG(Salary) AS AverageSalary
FROM (SELECT DepartmentID, Salary FROM Employees) AS Subquery
GROUP BY DepartmentID;

Explanation:

• The subquery (SELECT DepartmentID, Salary FROM Employees) calculates the department and salary for each employee.
• The outer query then groups the results by DepartmentID and calculates the average salary for each department.

Result:

DepartmentID	AverageSalary
1	55000
2	65000

---

Subquery in DELETE and UPDATE Statements

Subqueries can also be used in DELETE and UPDATE statements to remove or modify records based on conditions derived from other tables.

Example of DELETE with Subquery:

To delete employees who belong to departments that have no employees:

DELETE FROM Employees
WHERE DepartmentID NOT IN (SELECT DepartmentID FROM Employees);

Example of UPDATE with Subquery:

To give all employees in the 'IT' department a 10% salary increase:

UPDATE Employees
SET Salary = Salary * 1.1
WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'IT');

---

Advantages of Subqueries

• Modularity: Subqueries break down complex tasks into smaller, manageable steps.
• Code readability: Using subqueries can make SQL queries easier to read and understand, as they allow you to structure queries logically.
• Flexibility: Subqueries can be used in multiple places (SELECT, WHERE, HAVING, etc.) and with different types of joins and operators.

---

Disadvantages of Subqueries

• Performance: Subqueries, especially correlated ones, can sometimes be less efficient compared to using joins or other techniques. They may result in slower execution times, particularly for large datasets.
• Complexity: While subqueries can make a query easier to read, they can also complicate things when nested too deeply.

---

Conclusion

Subqueries are a powerful feature of SQL that allow you to execute one query within another. Whether you're comparing values, calculating aggregates, or filtering data, subqueries are an essential tool in SQL. Understanding how to use different types of subqueries (single-row, multi-row, correlated, scalar, and inline views) will significantly enhance your ability to work with relational databases.