Conditional NOT IN clause in SELECT WHERE

Question

Is it possible in a stored procedure for SQL Server 2008 R2 to have a conditional NOT IN clause for a SELECT statement's WHERE? I have three tables, Employee, TestingA, and TestingB, where employeeID is a foreign key in TestingA and TestingB:

CREATE TABLE [dbo].[Employee](
    [employeeID] [int] IDENTITY(9500,1) NOT NULL,
    [firstName] [nvarchar](50) NOT NULL,
    [middleName] [nvarchar](50) NULL,
    [lastName] [nvarchar](50) NOT NULL,
    [suffix] [nvarchar](50) NULL,
    [createdDate] [datetime] NOT NULL CONSTRAINT [DF_Employee_createdDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
    [employeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[TestingA](
    [testingA_ID] [int] IDENTITY(1,1) NOT NULL,
    [employeeID] [int] NOT NULL,
    [isComplete] [bit] NULL CONSTRAINT [DF_TestingA_isComplete]  DEFAULT ((0)),
    [createdDate] [datetime] NOT NULL CONSTRAINT [DF_TestingA_createdDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_TestingA] PRIMARY KEY CLUSTERED 
(
    [testingA_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[TestingB](
    [testingB_ID] [int] IDENTITY(1,1) NOT NULL,
    [employeeID] [int] NOT NULL,
    [isComplete] [bit] NULL CONSTRAINT [DF_TestingB_isComplete]  DEFAULT ((0)),
    [createdDate] [datetime] NOT NULL CONSTRAINT [DF_TestingB_createdDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_TestingB] PRIMARY KEY CLUSTERED 
(
    [testingB_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I need to do SELECT on the Employee table with a conditional NOT IN for its WHERE clause based on an input parameter for the stored procedure:

@Type nvarchar(50)

Based on the value of @Type, I want to do a NOT IN on TestingA or TestingB. So it's something like this:

select e.employeeId from Employee e
    where 
        -- If @Type = 'TestingA' Then 
        -- e.employeeID NOT IN (select ta.employeeID from TestingA ta)
        -- End
        -- Else             
        -- e.employeeID NOT IN (select tb.employeeID from TestingB ta)
        -- End

How would I do this in Transact-SQL? Thanks.

http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause — Ako, Jul 05 '16 at 14:31

score 3 · Accepted Answer · answered Jul 05 '16 at 14:31

3

select e.employeeId
from Employee e
where @Type = 'TestingA'
  AND not exists(select 1 from TestingA ta where ta.employeeID=e.employeeID)
  OR @Type != 'TestingA' 
  AND not exists(select 1 from TestingB tb where tb.employeeID=e.employeeID)

answered Jul 05 '16 at 14:31

Ivan Starostin

8,798
5
21
39

Thanks, @IvanStarostin. What does the `select 1` do? – Alex Jul 05 '16 at 14:32
1

Selected column list is ignored in `exists`. Some like to put there even `1/0`. – Ivan Starostin Jul 05 '16 at 14:34
This works, @IvanStarostin. Thanks! A follow-up: What's the difference between `NOT IN` and `NOT EXISTS`? – Alex Jul 05 '16 at 15:03
1

Found the answer to diff b/w `NOT IN` and `NOT EXISTS`: http://stackoverflow.com/questions/173041/not-in-vs-not-exists – Alex Jul 05 '16 at 15:07
OK. There is some performance and logical difference _possible_. – Ivan Starostin Jul 05 '16 at 15:09
In most cases, I think it's safe to go with `NOT EXISTS`. – Alex Jul 05 '16 at 15:10

vercelli · Answer 2 · 2016-07-05T14:39:00.720

1

Since it's a stored procedure I'd do two statements (It'll probably improve performance)

IF @Type = 'TestingA' Then 
  select e.employeeId from Employee e
      where e.employeeID NOT IN (select ta.employeeID from TestingA ta)
else
  select e.employeeId from Employee e
      where e.employeeID NOT IN (select ta.employeeID from TestingB ta)

edited Jul 05 '16 at 14:39

answered Jul 05 '16 at 14:30

vercelli

4,717
2
13
15

score 1 · Answer 3 · answered Jul 05 '16 at 14:48

use This

select e.employeeId from Employee e
where 
     e.employeeID NOT IN (case when @Type = 'TestingA' Then 
      (select ta.employeeID from TestingA ta)
     Else             
     (select tb.employeeID from TestingB tb)
     End)

You can use join also for the same query is given below

select e.employeeId from Employee e
left join TestingA e1 on @Type='TestingA' and e.employeeId != e1.employeeID 
left join TestingB e2 on @Type!='TestingA' and e.employeeId != e2.employeeID

Conditional NOT IN clause in SELECT WHERE

3 Answers3