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;
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);
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;
Ah, I have never used a derived table such as this one, but only dynamically generate the
INlist. Potentially going to be big and ugly (could have hundreds of thousands of rows). Concerns? Maybe best to leave it to the application?@user1032531 better to leave it to the application
@M0rtiis Will do. Thanks