find the missing values from a set of values, using SQL

Question

How can I find a missing values from a set of values, using SQL (Oracle DB) e.g.

SELECT NAME
FROM ACCOUNT
WHERE ACCOUNT.NAME IN ('FORD','HYUNDAI','TOYOTA','BMW'...)

(The "IN" clause may contain hundreds of values) If 'HYUNDAI' is missing in the ACCOUNT table, I need to get the result as "HYUNDAI".

Currently I use the result of the above query to do a Vlookup against the original set of values to find the missing values, I want to directly get the missing values without doing the Vlookup.

Thanks Kiran,

Answer 1

You got it reversed. Do this: http://www.sqlfiddle.com/#!2/09239/3

SELECT Brand
FROM 
(
    -- Oracle can't make a row without a table, need to use DUAL dummy table
    select 'FORD' as Brand from dual union
    select 'HYUNDAI' from dual union
    select 'TOYOTA' fom dual union
    select 'BMW' from dual      
) x
where Brand not in (select BrandName from account)

Sample Account data:

create table account(AccountId int, BrandName varchar(10));

insert into account(AccountId, BrandName) values
(1,'FORD'),
(2,'TOYOTA'),
(3,'BMW');

Output:

|   BRAND |
-----------
| HYUNDAI |

---

Better yet, materialized the brands to a table:

select *
from Brand
where BrandName not in (select BrandName from account)

Output:

| BRANDNAME |
-------------
|   HYUNDAI |

Sample data and live test: http://www.sqlfiddle.com/#!2/09239/1

CREATE TABLE Brand
    (`BrandName` varchar(7));

INSERT INTO Brand
    (`BrandName`)
VALUES
    ('FORD'),
    ('HYUNDAI'),
    ('TOYOTA'),
    ('BMW');


create table account(AccountId int, BrandName varchar(10));

insert into account(AccountId, BrandName) values
(1,'FORD'),
(2,'TOYOTA'),
(3,'BMW');