Get Ids from constant list for which there are no rows in corresponding table-CodePudding

Let say I have a table Vehicles(Id, Name) with below values:

1 Car
2 Bike
3 Bus

and a constant list of Ids:

1, 2, 3, 4, 5

I want to write a query returning Ids from above list for which there are no rows in Vehicles table. In the above example it should return:

4, 5

But when I add new row to Vehicles table:

4 Plane

It should return only:

And similarly, when from the first version of Vehicle table I remove the third row (3, Bus) my query should return:

3, 4, 5

I tried with exist operator but it doesn't provide me correct results:

select top v.Id from Vehicle v where Not Exists ( select v2.Id from Vehicle v2 where v.id = v2.id and v2.id in ( 1, 2, 3, 4, 5 ))

CodePudding user response：

You need to treat your "list" as a dataset, and then use the EXISTS:

SELECT V.I
FROM (VALUES(1),(2),(3),(4),(5))V(I) --Presumably this would be a table (type parameter),
                                     --or a delimited string split into rows
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.YourTable YT
                  WHERE YT.YourColumn = V.I);

CodePudding user response：

Please try the following solution.

It is using EXCEPT set operator.

Set Operators - EXCEPT and INTERSECT (Transact-SQL)

SQL

-- DDL and sample data population, start
DECLARE @Vehicles TABLE (ID INT PRIMARY KEY, vehicleType VARCHAR(30));
INSERT INTO @Vehicles (ID, vehicleType) VALUES
(1, 'Car'),
(2, 'Bike'),
(3, 'Bus');
-- DDL and sample data population, end

DECLARE @vehicleList VARCHAR(20) = '1, 2, 3, 4, 5'
    , @separator CHAR(1) = ',';

SELECT TRIM(value) AS missingID
FROM STRING_SPLIT(@vehicleList, @separator)
EXCEPT
SELECT ID FROM @Vehicles;

Output

 ----------- 
| missingID |
 ----------- 
|         4 |
|         5 |
 -----------

CodePudding user response：

In SQL we store our values in tables. We therefore store your list in a table.
It is then simple to work with it and we can easily find the information wanted.
I fully agree that it is possible to use other functions to solve the problem. It is more intelligent to implement database design to use basic SQL. It will run faster, be easier to maintain and will scale for a table of a million rows without any problems. When we add the 4th mode of transport we don't have to modify anything else.

create table vehicules(
id int, name varchar(25));
insert into vehicules values
(1 ,'Car'),
(2 ,'Bike'),
(3 ,'Bus');

create table ids (iid int)
insert into ids values
(1),(2),(3),(4),(5);

create view unknownIds as
select iid unknown_id from ids 
left join vehicules
on iid = id
where id is null;

select * from unknownIds;

| unknown_id |
| ---------: |
|          4 |
|          5 |

insert into vehicules values (4,'Plane')

select * from unknownIds;

| unknown_id |
| ---------: |
|          5 |

db<>fiddle here