CODEWITHSUNDEEP
sqloracle-databasein-clause
Kiranshell
Kiranshell

Reputation: 267

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

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,

Upvotes: 2

Views: 10568

Answers (5)

user2121350
user2121350

Reputation: 1

Contributing Excel code to make the typing of the answer easier:

Say column A has the values (Ford, Hyundai,...).

In column B, put this in every cell:

select 'x' as brand from dual union

In column C, write this formula, and copy it down.

=REPLACE(A2,9,1,A1)

All of the select/union statements should appear in column C.

Upvotes: -1

Kiranshell
Kiranshell

Reputation: 267

This worked perfectly, thanks Michael.

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      
)
where Brand not in (select BrandName from account)

Luxspes and Zane thank you for your inputs

Upvotes: 0

Michael Buen
Michael Buen

Reputation: 39393

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');

Upvotes: 6

Zane Bien
Zane Bien

Reputation: 23125

You can do:

SELECT a.val
FROM
(
    SELECT 'FORD' val    UNION ALL
    SELECT 'HYUNDAI'     UNION ALL
    SELECT 'TOYOTA'      UNION ALL
    SELECT 'BMW'         UNION ALL
    etc...
    etc...
) a
LEFT JOIN account b ON a.val = b.name
WHERE b.name IS NULL

Upvotes: 0

Luxspes
Luxspes

Reputation: 6750

You should use Except: EXCEPT returns any distinct values from the left query that are not also found on the right query.

WITH SomeRows(datacol) --It will look for missing stuff here
AS( SELECT *
    FROM (  VALUES  ('FORD'),
                    ('TOYOTA'), 
                    ('BMW') 
                    ) AS F (datacol)),
AllRows (datacol) --This has everthing
AS( SELECT *
    FROM (  VALUES  ('FORD'),
                    ('HYUNDAI'),
                    ('TOYOTA'), 
                    ('BMW') 
                    ) AS F (datacol))
SELECT datacol
FROM AllRows 
EXCEPT 
SELECT datacol 
FROM SomeRows

Upvotes: -1

Related Questions