What is Multi-valued subqueries

A multi-valued subquery is well suited to return results using the IN operator. The following hypothetical example returns the CustomerID, SalesOrderID values for all orders placed by customers in Canada.

SQLCopy

SELECT CustomerID, SalesOrderID
FROM Sales.SalesOrderHeader
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Sales.Customer
    WHERE CountryRegion = 'Canada');

In this example, if you were to execute only the inner query, a column of CustomerID values would be returned, with a row for each customer in Canada.

In many cases, multi-valued subqueries can easily be written using joins. For example, here’s a query that uses a join to return the same results as the previous example:

SQLCopy

SELECT c.CustomerID, o.SalesOrderID
FROM Sales.Customer AS c
JOIN Sales.SalesOrderHeader AS o
    ON c.CustomerID = o.CustomerID
WHERE c.CountryRegion = 'Canada';

So how do you decide whether to write a query involving multiple tables as a JOIN or with a subquery? Sometimes, it just depends on what you’re more comfortable with. Most nested queries that are easily converted to JOINs will actually BE converted to a JOIN internally. For such queries, there is then no real difference in writing the query one way vs another.

One restriction you should keep in mind is that when using a nested query, the results returned to the client can only include columns from the outer query. So if you need to return columns from both tables, you should write the query using a JOIN.

Finally, there are situations where the inner query needs to perform much more complicated operations than the simple retrievals in our examples. Rewriting complex subqueries using a JOIN can be difficult. Many SQL developers find subqueries work best for complicated processing because it allows you to break down the processing into smaller steps.