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)