Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

When I run the following script... The error occurred

/*Creating Tables*/

USE [Database1]

GO

CREATE TABLE Tb_Sample1(

[Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,

[Col1] [varchar](10) NULL,

[Col2] [datetime] NULL

) ON [PRIMARY]

GO

CREATE TABLE Tb_Sample2(

[Id] [int] FOREIGN KEY REFERENCES Tb_Sample1(Id) NULL,

[Col1] [varchar](50) NULL

) ON [PRIMARY]

GO

/*Inserting Records*/

Insert Tb_Sample1(Col1,Col2) Values('SQL Server',CURRENT_TIMESTAMP)

Go

Insert Tb_Sample2(Id,Col1) Values(1,'Administration')

Go

/*Using sub-query*/

SELECT a.* FROM Tb_Sample1 a with (Nolock)

WHERE a.Id =

(SELECT b.Id,b.Col1 FROM Tb_Sample2 b with (Nolock))

Msg 116, Level 16, State 1, Line 3

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Reason:

Here, Sub query returns Two columns, These value being compared with one column of the mail query...

Alternate ways:

The query can be changed to any one of the following ways..

SELECT a.* FROM Tb_Sample1 a with (Nolock)

WHERE a.Id in

(SELECT b.Id FROM Tb_Sample2 b with (Nolock))

(or)

Select a.* from Tb_Sample1 a with (Nolock)
where exists
(Select 1 from Tb_Sample2 b with (Nolock) where a.Id = b.Id)

(or)

Select a.* from Tb_Sample1 a with (Nolock) join Tb_Sample2 b with (Nolock) on (a.Id = b.Id)

 

(or)

Select a.* from Tb_Sample1 a with (Nolock), Tb_Sample2 b with (Nolock) where a.Id = b.Id

Recommended way:

Select a.Id, a.Col1, a.Col2 from Tb_Sample1 a with (Nolock) join Tb_Sample2 b with (Nolock) on (a.Id = b.Id)

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Subquery should have only one expression when comparing with main query using normal operators(=,>=,>,<=.<,<>, In, Not In).

/*Creating a table*/
If OBJECT_ID('Tb_Sample1') Is Null
Create Table Tb_Sample1
(
Id Int Identity(1,1),
Col1 Varchar(10),
Col2 DateTime
)
Go

/*Creating a table*/
If OBJECT_ID('Tb_Sample2') Is Null
Create Table Tb_Sample2
(
Id Int ,
Col1 Varchar(10),
)
Go

/*Inserting record*/
Insert Tb_Sample1(Col1, Col2) Values('SQL Server',CURRENT_TIMESTAMP)
Go
Insert Tb_Sample2(Id,Col1) Values(1, 'DBA')
Go

/*Using Sub-query with multiple expressions*/
Select * from Tb_Sample1 where Id in (Select Id, Col1 from Tb_Sample2)
Go

It'll throug an error..
Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Sub-query has two expressions Id, Col1. It should be one when using Sub-query and normal operators

The query can be changed to..
Select a.* from Tb_Sample1 a Join Tb_Sample2 b On (a.Id = b.Id)
Go
or
Select a.* from Tb_Sample1 a where exists (Select 1 from Tb_Sample2 b where a.Id = b.Id)
Go
or
Select a.* from Tb_Sample1 a where exists (Select 1 from Tb_Sample2 b where a.Id = b.Id)
Go