TSQL to get missing records inside IN operator

user_4055627 0
Viewed 63

I have a table like below in SQL Server:

create table address (id int, city varchar(10));

insert into address values (1, 'Rome');
insert into address values (2, 'Dallas');
insert into address values (3, 'Cracow');
insert into address values (4, 'Moscow');
insert into address values (5, 'Liverpool');
insert into address values (6, 'Cracow');
insert into address values (7, 'Seoul');

I'm writing a query with the IN operator as

SELECT City 
FROM address 
WHERE city IN ('Rome', 'Mumbai', 'Dallas', 'Delhi', 'Moscow')

I can get the result, but I want to get the list of missing or not available records in the table like

|  City  |  Status   |
+--------+-----------+
| Rome   | Available |
| Dallas | Available |
| Moscow | Available |
| Mumbai | Missing   |
| Delhi  | Missing   |
+--------+-----------+
sql-in select tsql sql-server sql
4 Answers

For this, I would recommend using row constructor values() rather than in:

select c.city, case when a.city is null then 'Missing' else 'Available' end status
from values(('Rome'), ('Mumbai'), ('Dallas'), ('Delhi'), ('Moscow')) c(city)
left join address a on a.city = c.city

If there are duplicates city in the address table, then exists is a better option:

select 
    c.city, 
    case when exists(select 1 from address a where a.city = c.city)
        then 'Available'
        else 'Missing' 
end status
from values(('Rome'), ('Mumbai'), ('Dallas'), ('Delhi'), ('Moscow')) c(city)
user_106767160

Use a derived table using VALUES for all the cities in question and a CASE expression with EXISTS that checks if an address with a city exists.

SELECT city.name city,
       CASE
         WHEN EXISTS (SELECT *
                             FROM address
                             WHERE address.city = city.name) THEN
           'Available'
         ELSE
           'Missing'
       END status
       FROM (VALUES ('Rome'),
                    ('Mumbai'),
                    ('Dallas'),
                    ('Delhi'),
                    ('Moscow')) city (name);

db<>fiddle

user_96614240

You can use IIF

SELECT [City], 
   IIF([City] IN('Rome', 'Mumbai', 'Dallas', 'Delhi', 'Moscow'), 'Available', 'Missing') STATUS
FROM [Address];

Result

user_136219830

With CTE :

with CitySearch (city) as (
select 'Rome' union all
select 'Mumbai'  union all
select 'Dallas'  union all
select 'Delhi'  union all
select 'Moscow' 
)
select t1.city,
case when t2.city is null then 'Missing' else 'Available' end Status
from CitySearch t1
left outer join address t2 on t1.city=t2.city
order by 2
user_37356900
Related