SQL Subquery

In SQL, a SELECT statement may contain another SQL statement, known as a subquery or nested query.

Example

-- use a subquery to select the first name of customer
-- with the highest age

SELECT first_name
FROM Customers
WHERE age= (
    -- subquery
    SELECT MAX(age)
    FROM CUSTOMERS
);

Here, the query is divided into two parts:

  • the subquery selects the maximum age 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

SELECT column FROM  table
WHERE column OPERATOR (
  SELECT column FROM table
);

Here,

  • 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 from

Example: Select Customers with Minimum Age Using Subquery

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

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

Here is how the query filters the table.

How to use subquery in SQL
Example: SQL Subqueries

In a subquery, the outer query's result depends on the result set of the inner subquery. That's why subqueries are also called nested queries.

Here is how this code works:

  • executes the subquery first (inner query), and returns the minimum age 22
  • executes the outer query, and selects customers with age 22
Working of Nested Query
Working of Nested Query

More on SQL Subquery

SQL Subquery With IN Operator

Suppose we want the details of customers who have placed an order. We can achieve that by using a subquery.

-- select the customers who have made orders

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

Here, the SQL command

  • selects customer_id and first_name from the Orders table
  • select those rows from the Customers table where customer_id is in the result set of the subquery
How to use subquery in SQL
Example: SQL Subquery

To learn more, visit SQL IN.

SQL Subquery and JOIN

In some scenarios, we can get the same result set using a subquery and the JOIN clause. For 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 set of the above query will be the same as the one below:

-- 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
);

Note: We should use the JOIN clause instead of a subquery whenever possible. It's because the execution speed of JOIN is more optimized than that of a subquery.


Also Read:

Did you find this article helpful?

Our premium learning platform, created with over a decade of experience and thousands of feedbacks.

Learn and improve your coding skills like never before.

Try Programiz PRO
  • Interactive Courses
  • Certificates
  • AI Help
  • 2000+ Challenges