SQL Subquery

In SQL, it's possible to place a SQL query inside another query known as subquery. For example,

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

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

Here, the SQL command

1. executes the subquery first; selects minimum age from the Customers table.
2. executes the outer query; selects rows where age is equal to the result of subquery.

Example 2: SQL Subquery

Suppose we want details of customers who have placed an order. Here's how we can do that using a subquery:

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

Here, the SQL command

1. selects customer_id from Orders table
2. select rows from Customers table where customer_id is in the result set of subquery

SQL Subquery and JOIN

In some scenarios, we can get the same result set using a subquery and the JOIN clause. For example,

The result set of this query

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;

will be the same as

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

Note: It's preferred to use the JOIN clause instead of a subquery whenever possible. It's because the execution speed of JOIN is faster and more optimized than a subquery.