Post
Ask Question

Creating Subquery in SQL Server

Introduction

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.

MajorCategory

Table1-in-SQL-Server.jpg

MinorCategory

Table2-in-SQL-Server.jpg

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

Subquery1-in-Sql-Server.jpg

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

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

Output

Subquery2-in-Sql-Server.jpg

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

Output

Subquery3-in-Sql-Server.jpg

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

Conclusion

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