Skip to main content

Using Subqueries

Introduction​

In SQL, a subquery is a query that is embedded within another SQL query. This is often done to perform complex queries and calculations that cannot be achieved with a single query. The result of a subquery can be used in the WHERE or HAVING clause of an outer SQL SELECT statement.

In this article, we will delve into the understanding and usage of subqueries in SQL.

What is a Subquery?​

A Subquery or Inner query or Nested query is a query within another SQL query and embedded within the WHERE clause. A Subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.

Here is a basic example of a subquery:

SELECT column_name(s)
FROM table_name
WHERE column_name OPERATOR 
    (SELECT column_name FROM table_name WHERE condition);

Types of Subqueries​

There are two types of Subqueries in SQL:

  1. Single Row Subquery: These types of subqueries return only one row to the main query. They use single row comparison operators like =, <, >, >=, <=.

  2. Multiple Row Subquery: These types of subqueries return more than one row to the main query. They use multiple row comparison operators like IN, ANY and ALL.

Using Subqueries in SQL Statements​

1. Subqueries with the SELECT Statement​

Subqueries can be used in the SELECT statement to find precise data. Here is an example:

SELECT EmployeeName, Salary 
FROM Employee
WHERE Salary > 
    (SELECT AVG(Salary) FROM Employee);

The subquery (SELECT AVG(Salary) FROM Employee) returns the average salary of all employees. The outer query then returns the EmployeeName and Salary of employees who earn more than the average salary.

2. Subqueries with the INSERT Statement​

Subqueries can also be used with INSERT statements. Here is an example:

INSERT INTO table2 
SELECT * FROM table1 
WHERE condition;

This query will insert into table2 all rows from table1 that meet the specified condition.

3. Subqueries with the UPDATE Statement​

Subqueries can be used with UPDATE statements. Here is an example:

UPDATE table 
SET column = 
    (SELECT column FROM table WHERE condition)
WHERE condition;

This query will update the value in a specified column based on a value in a column in another table.

4. Subqueries with the DELETE Statement​

Subqueries can be used with DELETE statements. Here is an example:

DELETE FROM table 
WHERE column IN 
    (SELECT column FROM table WHERE condition);

This query will delete rows where a column's value matches a value in a column in another table.

Conclusion​

Subqueries in SQL provide a powerful way to perform complex calculations and data manipulations. Remember that subqueries are executed first, and their result is used to complete the query condition for the main or outer query. Subqueries can be used in various ways with SELECT, INSERT, UPDATE, and DELETE statements, making them a crucial tool in any SQL user's toolbox.