SQL Subquery: Syntax, Types & Examples

SQL, or Structured Query Language, is a powerful programming language that stores and manages data in relational databases. An important element of this language that enables the retrieval of data from multiple tables in one query is SQL subquery. It is a group of clauses that can be nested within other queries and can perform quite complex operations on the underlying database. 

Subqueries can also be used to join multiple tables together into a single result set using joins, such as inner joins or outer joins. When used correctly, they can provide significant performance improvements over traditional queries due to their ability to filter out unnecessary rows from the result.

What is a SQL Subquery?

A subquery is a query within a query. It can be used to return data from multiple tables or databases in one single statement. Subqueries are usually enclosed in parentheses and nested inside other queries, such as SELECT, FROM, WHERE, HAVING, and ORDER BY clauses. They enable users to perform complex operations on the underlying database by providing greater flexibility when querying for specific information. 

Subqueries can be added after certain keywords, such as WHERE or ON with extra conditions specified for them using comparison operators (>, <, =). 

To learn more about SQL subquery, you can pursue a professional SQL course.

Let’s look at an example.

— use a subquery to select the first name of the customer

— with the maximum value of customer id

SELECT first_name

FROM Customers

WHERE customer_id= (

SELECT MAX(customer_id)

FROM CUSTOMERS

);

The above example is divided into the following two parts:

• The subquery selects the maximum id from the Customers table.
• The outer query selects the first_name of the customer with the maximum id (returned by the subquery).

SQL Subquery Syntax

The syntax of a SQL subquery is:

SELECT column

FROM table

WHERE column OPERATOR (

SELECT column

FROM table

);

Let’s break down this syntax.

• column is the name of the column(s) to filter.
• OPERATOR is any SQL operator to connect the two queries.
• table is the name of the table to fetch the column.

SQL Subquery Example

Let’s have a look at an example of a subquery.

— select all the rows from the Customers table with the minimum age

SELECT *
FROM Customers
WHERE age = (
  SELECT MIN(age)
  FROM Customers
);

When this is applied to a table, the following would be the result.

customer_id	first_name	last_name	Age	country
1	Ram	Lakhan	31	India
2	Shyam	Rajput	22	USA
3	Laxman	Kapoor	22	UK
4	Arjun	Singh	27	Australia

SELECT *
FROM Customers
WHERE age = (
  SELECT MIN(age)
  FROM Customers
);

customer_id	first_name	last_name	Age	country
2	Shyam	Rajput	22	USA
3	Laxman	Kapoor	22	UK

Types of SQL Subquery

There are several types of subqueries in SQL. These are:

Single-Row Subquery

 Single-row subqueries produce results in a single row. They are usually used in the SELECT statement’s WHERE clause to filter the outcomes. For instance, the following search makes a list of every employee whose pay exceeds the average standard.

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Multiple-Row Subqueries

 Multiple-row subqueries return a number of rows of information. They are frequently used in the FROM clause of a SELECT statement to combine the outcomes of the outer query with those of the subquery. For example, the following query retrieves all personnel who have a manager whose annual income exceeds $100,000:

SELECT *
FROM employees
WHERE manager_id IN (
  SELECT id
  FROM employees
  WHERE salary > 100000
);

Correlated Subqueries

Correlated subqueries are a sort of multiple-row subqueries that make reference to the values in the outer query. As a result, the subquery is run once for each entry in the outer query. For example, the following query returns all employees who have a salary greater than the salary of their manager:

SELECT *
FROM employees e1
WHERE salary > (
SELECT salary
FROM employees e2
WHERE e1.manager_id = e2.id
);

Nested Subquery

Subqueries that are nested inside of other subqueries are called nested subqueries. They are frequently used to carry out complex queries that would be challenging or impossible with only one subquery. As an example, the following query returns all workers whose salaries exceed the average salaries of all workers in their department:

SELECT *
FROM employees e1
WHERE salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = (
    SELECT e3.department_id
    FROM employees e3
    WHERE e3.id = e1.employee_id
  )
);

SQL Subquery With IN Operator

The SQL IN operator makes it possible to specify multiple values in the WHERE clause, making it simple to determine whether an expression matches any of the values in the list of values. Let’s look at an example.

Suppose we want the details of customers who have placed an order. The subquery will be:

— select the customers who have made orders

SELECT customer_id, first_name
FROM Customers 
WHERE customer_id IN (
  SELECT customer_id
  FROM Orders
);

Let’s apply this query to a table.

Table: Customers                                                            

customer_id	first_name
1	Ram
2	Shyam
3	Laxman
4	Arjun

Table: Orders

order_id	amount	customer_id
1	200	4
2	500	10
3	300	3
4	800	1

Applying the subquery to both tables above, we will get the following result.

customer_id	first_name
1	Ram
2	Shyam
4	Arjun
3	Laxman

SQL Subquery along with JOIN

In order to merge information or rows from two or more tables based on a shared field, a SQL JOIN statement is used. Whenever possible, we ought to use a JOIN clause rather than a subquery. The reason for this is that JOIN’s execution speed is faster than a subquery’s. 

Let’s look at an example.

— SELECT DISTINCT only selects the unique combination of customer_id and first_name

— join the Customers and Orders tables and select the rows where their customer_id values match

— result set contains customer_id and first_name of customers who made an order

SELECT DISTINCT Customers.customer_id, Customers.first_name

FROM Customers

INNER JOIN Orders

ON Customers.customer_id = Orders.customer_id

ORDER BY Customers.customer_id;

The result of the above query will be the same as the below query.

— display the distinct customer ids and first names

— of customers who made an order using a subquery

SELECT customer_id, first_name

FROM Customers 

WHERE customer_id IN (

  SELECT customer_id

  FROM Orders

);

Conclusion

SQL subquery is a powerful way to optimize the performance of your database queries and make them more efficient. With careful consideration and practice, developers can use SQL subqueries to their advantage by crafting concise statements that yield precisely what they need with minimal effort.