Return WHERE IN clause rows even if table doesn't contain the given row

Question

mytable contains records for id 1, 2, 3, 10, 12 and 20, and specifically does not include records for id 5 and 15.

How can I perform some query such as:

SELECT id, id IN (1,5,10,15,20) AS status
FROM mytable
WHERE id IN (1,5,10,15,20);

and return records:

id  status
1   true
5   false
10  true
15  false
20  true

If there was some other table, I could just outer join mytable, but there is none. The following will certainly not work, but is there some sort of fictional table I can join against?

SELECT id, isnull(mytable.id) AS status
FROM *
LEFT OUTER JOIN mytable
WHERE id IN (1,5,10,15,20);

Gordon Linoff · Accepted Answer · 2019-07-03 14:37:42Z

1

You can create a derived table in the FROM clause:

SELECT i.id, (t.id is not null) AS status
FROM (SELECT 1 as id UNION ALL SELECT 5 UNION ALL SELECT 10 UNION ALL SELECT 15 UNION ALL SELECT 20
     ) i LEFT JOIN
     mytable t
     ON i.id = t.id;

answered Jul 3, 2019 at 14:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

user1032531 Over a year ago

Ah, I have never used a derived table such as this one, but only dynamically generate the IN list. Potentially going to be big and ugly (could have hundreds of thousands of rows). Concerns? Maybe best to leave it to the application?

M0rtiis Over a year ago

@user1032531 better to leave it to the application

user1032531 Over a year ago

@M0rtiis Will do. Thanks

Collectives™ on Stack Overflow

Return WHERE IN clause rows even if table doesn't contain the given row

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related