What is Sub-Query in SQL Server?

A subquery or Inner query or Nested query is a query within another query and embedded within the where clause condition. Let take some example below and see how the Subquery will work.

Below are common Syntax for Sub-Query

SELECT * FROM <TableName>
WHERE <ColumnName> In 
(
	SELECT <ColumnName> FROM TableName WHERE <ColumnName> = <> != <Condition>
)

SubQuery will be used in IN / NOT IN / ANY / ALL / EXISTS / NOT EXISTS / FROM clause / UPDATE, DELETE, orINSERT statement

The below example shows the subQuery that will be used in the where condition. Use the below query to create an Employee table.

USE [StudentDb]
GO
/****** Object:  Table [dbo].[EmployeeDetails]    Script Date: 08/12/2021 21:40:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EmployeeDetails]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[EmployeeDetails](
	[EmpId] [bigint] IDENTITY(1,1000) NOT NULL,
	[EmpName] [varchar](50) NULL,
	[Age] [int] NULL,
	[MobileNumber] [bigint] NULL,
	[Department] [varchar](50) NULL,
	[Salary] [bigint] NULL,
 CONSTRAINT [PK_EmployeeDetails] PRIMARY KEY CLUSTERED 
(
	[EmpId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[EmployeeDetails] ON
INSERT [dbo].[EmployeeDetails] ([EmpId], [EmpName], [Age], [MobileNumber], [Department], [Salary]) VALUES (1, N'Prabhakaran', 35, 9898989898, N'IT', 20000)
INSERT [dbo].[EmployeeDetails] ([EmpId], [EmpName], [Age], [MobileNumber], [Department], [Salary]) VALUES (1001, N'Ram', 20, 3434343255, N'Sale', 30000)
INSERT [dbo].[EmployeeDetails] ([EmpId], [EmpName], [Age], [MobileNumber], [Department], [Salary]) VALUES (2001, N'Ramesh', 30, 4453224245, N'IT', 22000)
INSERT [dbo].[EmployeeDetails] ([EmpId], [EmpName], [Age], [MobileNumber], [Department], [Salary]) VALUES (3001, N'Prabhakaran', 35, 3434344534, N'IT', 20000)
INSERT [dbo].[EmployeeDetails] ([EmpId], [EmpName], [Age], [MobileNumber], [Department], [Salary]) VALUES (4001, N'Radha', 30, 9865685656, N'Sale', 25000)
INSERT [dbo].[EmployeeDetails] ([EmpId], [EmpName], [Age], [MobileNumber], [Department], [Salary]) VALUES (5001, N'Siva', 32, 7845215452, N'Marketing', 60000)
INSERT [dbo].[EmployeeDetails] ([EmpId], [EmpName], [Age], [MobileNumber], [Department], [Salary]) VALUES (6001, N'Vignesh', 25, 6323562232, N'Sale', 70000)
SET IDENTITY_INSERT [dbo].[EmployeeDetails] OFF

We will take an example, to pull the Max salaried person from the employee.

Note: It’s common for interview questions as well, and based on your experience how are you going to pull the data.

Have look at the below query

SELECT * FROM [EmployeeDetails]
WHERE Salary = (
	-- Sub Query
	SELECT MAX(Salary) FROM [EmployeeDetails]
)

Here the logic is first we are taking Max(Salary) from the EmployeeDetails, and base on Max salary we can pull the full EmployeeDetails.

The output will be like below

Thanks for your time to look at this topic here..!!