Lesson 16. Subqueries

You can use the output of a query to power another query. This is called a subquery.

The only rule here is that your subquery has to have one, and only one, output column.*

You use subqueries in WHERE clauses in conjunction with the IN operator.

The use case for this is when you are looking for data that is not necessarily directly related, so there is no join to be performed. This also comes in handy when your filter needs a series of values and not just one value on the right side of the equality.

*Technically, you can have more than one column by concatenating the columns together.

Examples

A Simplistic Subquery Example

The below example is simplistic. We will do a more complex example in putting it all together.

USE AdventureWorks2016

SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID IN (SELECT ProductID FROM Production.Product WHERE MakeFlag = 1)