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,

What DBMS are you using?

Zane Bien
– Zane Bien

2012-07-13 05:49:47 +00:00
Commented Jul 13, 2012 at 5:49 — Zane Bien
– Zane Bien, Commented Jul 13, 2012 at 5:49

Michael Buen · Accepted Answer · 2012-07-13 15:53:01Z

6

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

edited Jul 13, 2012 at 15:53

answered Jul 13, 2012 at 5:26

Michael Buen

39.6k10 gold badges99 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Kiranshell Over a year ago

I think, this will work but, will be tedious to type the query if there are hundreds of values.(but can Use Excel). Any other options?

Kiranshell Over a year ago

I dont have access to create table, that is why this is tricky

Michael Buen Over a year ago

If you can't create table, just use UNION on subquery(see the answer above), or if you are using MySQL,Postgresql,Sql Server you can use VALUES shortcut

Luxspes Over a year ago

if you use CTE as in my answer you have no need to use create table

Kiranshell Over a year ago

I did not know about sqlfiddle.com, this will be very useful. Thanks Michael.

|

Zane Bien · Accepted Answer · 2012-07-13 05:47:59Z

0

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

answered Jul 13, 2012 at 5:47

Zane Bien

23.2k6 gold badges47 silver badges58 bronze badges

Comments

highlycaffeinated · Accepted Answer · 2012-07-14 01:53:21Z

0

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

edited Jul 14, 2012 at 1:53

highlycaffeinated

19.9k9 gold badges62 silver badges92 bronze badges

answered Jul 13, 2012 at 22:16

Kiranshell

2674 gold badges6 silver badges12 bronze badges

Comments

Luxspes · Accepted Answer · 2012-07-13 05:45:57Z

-1

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

edited Jul 13, 2012 at 5:45

answered Jul 13, 2012 at 5:20

Luxspes

6,8003 gold badges30 silver badges31 bronze badges

7 Comments

Kiranshell Over a year ago

"Not In" will give me values other than the once I have queried for but which are in the table.

Luxspes Over a year ago

Yes, "not in" was not the answer, I have changed my answer, please use EXCEPT.

Kiranshell Over a year ago

I will not know which is the missing element, HYUNDAI was just a example.

Luxspes Over a year ago

I know, I just only used HYUNDAI because you used that in your example, my code will find everthing that is in AllRows table but it is not in SomeRows table

Luxspes Over a year ago

Removed the "HYUNDAI" stuff from the Source table to avoid confusion

|

John Dvorak · Accepted Answer · 2013-08-09 14:12:01Z

-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.

edited Aug 9, 2013 at 14:12

John Dvorak

27.4k13 gold badges73 silver badges86 bronze badges

answered Aug 9, 2013 at 13:51

user2121350

11 bronze badge

Collectives™ on Stack Overflow

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

5 Answers 5

7 Comments

Comments

Comments

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

Comments

Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related