Creating Subquery in SQL Server


A subquery in SQL is a SELECT statement that is nested within another SQL query. This article shows how to create and use a subquery to a select SQL server statement. 

What is Subquery in SQL Server?

The Transact-SQL language allows comparing a column value to the result of another select statement. A subquery is also called an inner query. The statement that contains the subquery is called the outer query—losing such an internal 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 essential properties of a subquery:

  1. A sub-query must be enclosed in parentheses.
  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 have more than one sub-query.

How do we create a Subquery in SQL Server?

First, create two tables named MajorCategory and minor category. The following images display both tables.





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')



Now create the second query using the SubCategories table, listing the information you need about the category.

SELECT [MinorCategoryId], Categoryid, [SubCategoryName]
FROM [MinorCategory]



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

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

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

SELECT [MinorCategoryId], Categoryid, [SubCategoryName]
FROM [MinorCategory] where Categoryid in (SELECT Categoryid
FROM [MajorCategory]
WHERE (CategoryName = 'Java'))



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


This article shows how to create a subquery to a select SQL server statement. Find an article about Queries in SQL Server article here:  Queries in SQL Server article here.u

Similar Articles