Creating Subquery in SQL Server

Subquery

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery is also called an inner query. The statement that contains the subquery is called the outer query. The Transact-SQL language offers the ability to compare a column value to the result of another select statement. Such an inner select statement nested in the where clause of an outer SELECT Statement is also called a subquery. 

Properties of a Subquery

The following are the important properties of a subquery:

1. A sub-query must be enclosed in parenthesis.
2. A sub-query must be put in the right hand of the comparison operator.
3. A sub-query cannot contain an ORDER-BY clause.
4. A query can contain more than one sub-query.

Creating Subquery

First create two tables named MajorCategory and minorCategory. The following images display both tables.

MajorCategory

MinorCategory

Now create a query showing all SubCategories for the Java Category.

Create the first query on the MajorCategory table to find the Categoryid of Java, as in the following:

SELECT Categoryid

FROM [MajorCategory]

WHERE (CategoryName = 'Java')

 

Output

Now create the second query using the SubCategories table, listing the information you need about Category:

SELECT [MinorCategoryId], Categoryid, [SubCategoryName]

FROM [MinorCategory]

Output

You can construct a WHERE clause with a subquery. We can use the following operators with subquery:

• comparison operator
• IN operator
• ANY or All operator
• EXISTS function

Now add a WHERE clause to the second query, Place parentheses around the first query. This example shows the simple subquery that is used with the IN operator. So the result looks like this:

SELECT [MinorCategoryId], Categoryid, [SubCategoryName]

FROM [MinorCategory] where Categoryid in (SELECT Categoryid

FROM [MajorCategory]

WHERE (CategoryName = 'Java'))

Output

A subquery is a query that is nested inside a select, insert, update, or delete statement, or inside another subquery.