When to use Subquery
Often, you need to limit a SELECT statement to values that relate to another table, but a join is not necessarily the answer. In these cases, the subquery comes into play. You can use a SELECT statement as the filtering mechanism to limit the overall results from a query.
For example, from the PUBS database, you can say, "Give me all the titles for publishers who are located in CA."
The subquery in the query below limits the publisher IDs to only those who live in the state of California:
IN (SELECT Pub_ID
WHERE State= 'CA')
The result is a list of titles, but only those published by California publishers.
What are Subqueries?
Subqueries are regular queries placed inside parenthesis. Subqueries can be used in different ways and at different locations inside a query: Here is an subquery with the IN operator
WHERE value IN
Subqueries can also assign column values for each record:
SELECT column1 = (SELECT columnName
FROM tableName WHERE condition),