SQL Subqueries

SQL, or Structured Query Language, is a powerful tool used for interacting with databases. It provides a standardized method for retrieving, modifying, or deleting data, making it an essential skill for anyone working with databases. Among the numerous concepts in SQL, subqueries are one of the more complex, but also one of the most versatile and powerful tools you can learn. These are queries nested inside other queries, allowing for more complex operations and sophisticated data retrieval. In this blog, we will dive deep into the world of SQL subqueries, providing you with a solid understanding, as well as practical examples to help you grasp the concept better. Buckle up, and let’s get started!

What are SQL Subqueries?

Subqueries, also known as inner queries or nested queries, are SQL queries embedded within another SQL query. They can be used in various parts of a query, such as in the SELECT, WHERE, or FROM clauses. The outer query that wraps the subquery is often referred to as the main query or outer query.

A subquery executes once before its parent query runs. The result of the subquery is then used by the outer query for its operations.

Here is a simple example:

SELECT EmployeeName 
FROM Employees 
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

In the above SQL query, the subquery calculates the average salary of all employees. The main query then uses this average salary to select employees who earn more than the average.

Types of Subqueries

Subqueries can be classified into two main types based on their return values:

Scalar Subqueries

A scalar subquery is a subquery that returns exactly one column value from one row.

SELECT EmployeeName, Salary 
FROM Employees 
WHERE Salary > (SELECT Salary FROM Employees WHERE EmployeeName = 'John Doe');

Here, the subquery returns the salary of ‘John Doe’ and the main query selects the names and salaries of employees who earn more than ‘John Doe’.

Multi-row Subqueries

Multi-row subqueries are those that return multiple rows. They are used with operators like IN, ANY, or ALL.

SELECT EmployeeName 
FROM Employees 
WHERE EmployeeId IN (SELECT ManagerId FROM Employees);

In this example, the subquery returns a list of all manager IDs. The main query then selects the names of employees who are also managers.

SQL Subquery Operators

When dealing with subqueries that return multiple rows, you can use operators like IN, ANY, and ALL.

The IN Operator

The IN operator is used when you want to compare a value with a list of results from the subquery.

SELECT EmployeeName 
FROM Employees 
WHERE EmployeeId IN (SELECT ManagerId FROM Employees);

The ANY Operator

The ANY operator is used when you want to check if a value is equal to any value returned from the subquery.

SELECT EmployeeName 
FROM Employees 
WHERE Salary > ANY (SELECT Salary FROM Employees WHERE DepartmentId = 1);

This query will select any employee whose salary is greater than the salary of any employee in department 1.

The ALL Operator

The ALL operator is used when you want to compare a value with all values returned from the subquery.

SELECT EmployeeName 
FROM Employees 
WHERE Salary > ALL (SELECT Salary FROM Employees WHERE DepartmentId = 1);

This query will select any employee whose salary is greater than the salaries of all employees in department 1.

Using Subqueries in Different SQL Clauses

Subqueries can be used in various parts of an SQL statement.

SELECT Clause

A subquery can be used in the SELECT clause to provide a column of values. .

SELECT EmployeeName, (SELECT AVG(Salary) FROM Employees) AS AverageSalary
FROM Employees;Code language: SQL (Structured Query Language) (sql)

In this example, the subquery calculates the average salary for all employees, and this value is returned in every row of the result set.

FROM Clause

A subquery can be used in the FROM clause to act as a temporary table for the main query.

SELECT e.EmployeeName, e.Salary
FROM (SELECT EmployeeName, Salary FROM Employees WHERE DepartmentId = 1) AS e;
Code language: SQL (Structured Query Language) (sql)

Here, the subquery retrieves all employees in department 1 and their salaries. The outer query selects from this temporary table.

WHERE Clause

As we saw in the initial examples, a subquery can be used in the WHERE clause to filter rows for the main query.

SELECT EmployeeName 
FROM Employees 
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Correlated Subqueries

Correlated subqueries are a type of subquery where the inner query depends on the outer query for its values. This means the inner query runs once for every row processed by the outer query.

Here is an example:

SELECT e1.EmployeeName, e1.DepartmentId
FROM Employees e1
WHERE Salary > (SELECT AVG(Salary) FROM Employees e2 WHERE e1.DepartmentId = e2.DepartmentId);

In this example, for each employee in the outer query, the inner query calculates the average salary of employees in the same department and checks if the employee’s salary is greater than this average.

The EXISTS Operator with Subqueries

The EXISTS operator is used in combination with a subquery and is always followed by a subquery. It returns true if the subquery returns one or more records.

SELECT DepartmentName 
FROM Departments d
WHERE EXISTS (SELECT 1 FROM Employees e WHERE e.DepartmentId = d.DepartmentId);

This query selects all departments that have at least one employee.

Frequently Asked Questions (FAQs)

Q1: Can a subquery be used in the INSERT statement?

Yes, subqueries can be used in an INSERT statement. For instance, you could use a subquery to insert multiple rows into a table from the result of the subquery.

Q2: Can we use ORDER BY clause in a subquery?

Yes, but only in certain cases. For a subquery in the FROM clause, you can use an ORDER BY. But in a subquery in the WHERE, SELECT, or HAVING clauses, using ORDER BY wouldn’t make sense because the subquery is only used for comparison and doesn’t retrieve its results directly.

Q3: What is the difference between a correlated and non-correlated subquery?

A non-correlated subquery can be run independently of the outer query, while a correlated subquery depends on the outer query for its values.

Q4: Can a subquery be used in the UPDATE statement?

Yes, similar to the INSERT statement, subqueries can also be used in the UPDATE statement. This can be useful when the value you want to update is calculated from a subquery.

In conclusion, mastering SQL subqueries can be a great asset when working with databases. It allows you to construct more complex queries and to extract more meaningful information from your data. Keep practicing, and soon enough you’ll find yourself writing subqueries with ease.