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:

SELECT Title
FROM Titles
WHERE Pub_ID
IN (SELECT Pub_ID
FROM Publishers
WHERE State= 'CA')    

The result is a list of titles, but only those published by California publishers.

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

SELECT columnNames
  FROM tableName1
 WHERE value IN 
(SELECT columnName
   FROM tableName2 
   WHERE condition)

SELECT column1 = (SELECT columnName 
FROM tableName WHERE condition),
 columnNames
 FROM tableName
 WEHRE condition