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,
columnis the name of the column(s) to filterOPERATORis any SQL operator to connect the two queriestableis 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.
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
More on SQL Subquery
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
To learn more, visit SQL IN.
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: