Using subqueries in SQL for simpler queries

When writing SQL queries, JOINs are often the first choice. But in some cases, a subquery can be simpler and easier to read.

A subquery in SQL is a query inside another query. It can be used to filter results or calculate values before the main query runs. Many databases support multiple levels of subqueries, but often, you just need one.

A common example is filtering with IN:

SELECT Name
FROM dbo.Customers
WHERE CustomerId IN
(
    SELECT CustomerId
    FROM dbo.Orders
);

This returns customers who have at least one order.

The same logic can be written with a join:

SELECT DISTINCT c.Name
FROM dbo.Customers c
JOIN dbo.Orders o ON c.CustomerId = o.CustomerId;

Both queries work, but the subquery version can be easier to understand at a glance: "give me customers whose ID exists in the orders table."

Subqueries are also useful with EXISTS:

SELECT Name
FROM dbo.Customers c
WHERE EXISTS
(
    SELECT 1
    FROM dbo.Orders o
    WHERE o.CustomerId = c.CustomerId
);

This is another way to express the same condition, and is often used for performance and clarity.

So, subqueries in SQL can make intent clearer, especially for filtering conditions. But, regular joins are still useful when you need data from multiple tables in the result.

You can follow a simple rule to select between a JOIN and a subquery: if a join feels complicated, try a subquery. It can sometimes make the query easier to read and reason about. Performance depends on the data, structure and indexes, for example.