In SQL Server, you can write query in a query. You can write the subqueries in the SELECT section, FROM section, and the WHERE section of the query. The Main query is also known as OUTER QUERY or OUTER SELECT, and subquery is known as INNER QUERY or INNER SELECT
In the following example, you can find subquery uses.
Example:
First, we create two tables as below and we add a few records to these tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
USE [TestDB] GO CREATE TABLE [dbo].[Person]( [ID] [int] NOT NULL, [Name] [varchar](50) NOT NULL, [DepartmentID] [int] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Department]( [DepartmentID] [int] NOT NULL, [DepartmentName] [varchar](50) NOT NULL ) ON [PRIMARY] GO INSERT INTO [dbo].[Person] VALUES (1,'Nurullah',323),(2,'Faruk',356) INSERT INTO [dbo].[Department] VALUES (323,'IT Department' ),(356,'Sales Department') |
Using subquery in SELECT section:
With the following query, we read the Name and DepartmentID columns in the Person table and the DepartmentName from the Department table with Subquery. This query will work better with the INNER JOIN method. I just wanted to show you how to use subqueries in the Select section of the query.
|
1 2 3 |
SELECT mq.Name,mq.DepartmentID, (Select DepartmentName FROM [dbo].[Department] WHERE DepartmentID=mq.DepartmentID) subquery FROM [dbo].[Person] mq |
Using subquery in FROM section:
Let’s write the same query using the subquery in the FROM section as follows.
|
1 2 3 |
SELECT [Name],subquery.DepartmentID,subquery.DepartmentName FROM [dbo].[Person], (Select [DepartmentID],[DepartmentName] FROM [dbo].[Department]) subquery WHERE subquery.DepartmentID=[dbo].[Person].DepartmentID |
Using subquery in WHERE section:
Using a subquery in the WHERE section, let’s write a query as follows. In this query, we list the records that have the DepartmentName value ‘IT Department’.
|
1 2 |
SELECT [Name],[DepartmentID] FROM [dbo].[Person] WHERE [DepartmentID]=(Select [DepartmentID] FROM [dbo].[Department] WHERE [DepartmentName]='IT Department') |
