A subquery is a SELECT statement that is nested inside another SELECT, INSERT, UPDATE or DELETE statement.

Subqueries are used to retrieve data that will be used as a condition in the main query or to perform additional calculations on the result set. In this article, we will discuss the basics of SQL subqueries and provide examples of how they can be used in a database.

Syntax of SQL Subqueries

The syntax of a subquery is as follows:

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

In this syntax, the subquery is enclosed in parentheses and is placed in the WHERE clause of the main query.

The result set of the subquery is used as a condition for the main query to filter the data.

Examples of SQL Subqueries

1. Using a Subquery as a Condition

Let’s say we have two tables: Customers and Orders.

We want to retrieve the name of customers who have placed an order.

We can use a subquery to achieve this:

SELECT customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id 
                      FROM orders);

This SQL statement selects the customer_name column from the customers table, and uses a subquery to retrieve the customer_id column from the orders table.

The result set of the subquery is used as a condition in the WHERE clause to filter the customer_name values.

Check out our post on SQL Commands and Functions.

2. Using a Subquery to Perform Calculations

Let’s say we have a table called Sales with columns for sales_id, product_name, and sales_amount.

We want to retrieve the product_name and the average sales_amount for each product.

We can use a subquery to perform the calculation:

SELECT product_name, (SELECT AVG(sales_amount) 
                      FROM sales 
                      WHERE product_name = p.product_name) 
                      AS avg_sales
FROM sales p
GROUP BY product_name;

This SQL statement selects the product_name column from the sales table, and uses a subquery to calculate the average sales_amount for each product_name value.

The AVG() function is applied to the sales_amount column in the subquery. The result set of the subquery is used as a value for the avg_sales column in the main query.

product_name	avg_sales
Phone	350
Tablet	450
Laptop	800

3. Using a Subquery in an UPDATE Statement

Let’s say we have a table called Employees with columns for employee_id, first_name, last_name, and salary.

We want to give a 10% raise to employees who earn less than the average salary.

We can use a subquery in an UPDATE statement to achieve this:

UPDATE employees
SET salary = salary * 1.1
WHERE salary < (SELECT AVG(salary) 
                FROM employees);

This SQL statement updates the salary column in the employees table, and uses a subquery to calculate the average salary value.

The result set of the subquery is used as a condition in the WHERE clause to filter the rows that meet the condition.

The SET clause then applies a 10% raise to the salary column for those rows.

Conclusion

Subqueries are a powerful tool in SQL that allow you to retrieve data that will be used as a condition in the main query or to perform additional calculations on the result set.

By using subqueries, you can write more complex and efficient queries that can help you retrieve the exact data you need from a database.