SQL - Select In from Array, and NOT in in same query

Question

I have a userform built in VBA where my coworkers can enter multiple values that builds an array and places it into an IN statement, which works great. Problem is I need to also be able to display what values do not exist within the tables.

Example table

id | value
1  | value1
2  | value2
4  | value4

Then a query that could be generated would be

SELECT [id],[value] FROM [tablea] WHERE [id] IN (1,2,3,4)

Expected or desirable outcome would be as follows

id | value
1  | value1
2  | value2
3  | null
4  | value4

I've tried doing it like so;

SELECT [id],[value] FROM [tablea] WHERE [id] IN (1,2,3,4) AND [id] NOT IN (1,2,3,4)

since both arrays will be the same, this returns 0 of course.

I know I can do this with a union, and define the not in statement within the second union, but I'd like to do this without a union.. Any other thoughts?

This is on Microsoft SQL 2005

I unfortunately only have access to SELECT, since I'm performing queries either via VBA or Tableau. So I cannot create a derived table or have anything to reference other than the select statement.

If you are building an array and doing this I have a very strong suspicion your code is vulnerable to sql injection. Are you building up a string which includes this array and then executing that against your database? That is the textbook definition of sql injection. — Sean Lange
– Sean Lange, Commented Nov 16, 2015 at 15:43
@SeanLange I agree. However,the scripts are only ran behind Active Directory with only select rights, and select users. — Jeff Beagley
– Jeff Beagley, Commented Nov 16, 2015 at 15:46
@SeanLange while I am here to learn though, what would you suggest? — Jeff Beagley
– Jeff Beagley, Commented Nov 16, 2015 at 15:47
I would suggest one of two options. Both of them would be using parameters instead of pass through sql. You could either use a table valued parameter (that would be my first choice) or you could pass the delimited list as a parameter and split that. — Sean Lange
– Sean Lange, Commented Nov 16, 2015 at 15:49

Gordon Linoff · Accepted Answer · 2015-11-16 15:43:39Z

4

You need a left join of some sort. One way would be to construct your query as:

select v.id, t.value
from (values (1), (2), (3), (4)
     ) v(id) left join
     table t
     on v.id = t.id;

answered Nov 16, 2015 at 15:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Joel Coehoorn Over a year ago

Huh. I didn't know you could use the values clause like that. Learn something new every day.

Sean Lange Over a year ago

@JoelCoehoorn this is a Table Value Constructor. Very handy for this type of thing. :) msdn.microsoft.com/en-us/library/dd776382.aspx

Joel Coehoorn Over a year ago

Indeed. I would have had to recommend generating a "numbers" table to filter on his input set before the outer join. It works here, but imagine if those were string values; I'd be really in trouble.

Jeff Beagley Over a year ago

Afraid I'm using Server 2005, after trying this for a few minutes lol. Updated OP with that detail.

Joel Coehoorn Over a year ago

@JeffBeagley You know that Sql Server 2005 is less than 5 months from End of Life, right? This could be a good excuse to upgrade.

|

Jeff Beagley · Accepted Answer · 2015-11-30 22:25:31Z

0

Thanks to Joel Coehoorn for the tip towards using a CTE

I was able to accomplish this like so;

WITH numbers AS (
    SELECT 1 AS num UNION ALL
    SELECT 2 AS num UNION ALL
    SELECT 3 AS num UNION ALL
    SELECT 4 as num UNION ALL )

SELECT
    COALESCE(id,num) as col1,
    id as col2
FROM tablea

RIGHT JOIN numbers ON tablea.id = numbers.num

This would return

col1  |  col2
1     |    1
2     |    2
3     |    NULL
4     |    4

answered Nov 30, 2015 at 22:25

Jeff Beagley

1,02511 silver badges22 bronze badges

Collectives™ on Stack Overflow

SQL - Select In from Array, and NOT in in same query

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related