Show values that are not in a table

CCMF · #1 (**permalink**) 03-16-10, 17:07

I have a lot of values in an Excel worksheet and want to check them against a table.

Example:
338306
338406
338506

From these 3 values I know that only 338306 exists in the table.

What is the SQL statement to show me that 338406 and 338506 don't exist in the table?

Thanks in advance.

n_i · #2 (**permalink**) 03-16-10, 17:16

Code:

select * from excel_worksheet eww where not exists (
  select 1 from database_table dt where dt.value = eww.value
)

Vaibhav Vyas · #3 (**permalink**) 03-18-10, 01:03

select 1 from excel_worksheet eww EXCEPT(
select 1 from database_table dt
) with ur

n_i · #4 (**permalink**) 03-18-10, 07:33

Quote:

Originally Posted by Vaibhav Vyas

select 1 from excel_worksheet eww EXCEPT(
select 1 from database_table dt
) with ur

I have a feeling that the query above will always return 0 rows.

Vaibhav Vyas · #5 (**permalink**) 03-18-10, 07:37

I think you get confused by 1...It should be just replaced by the column name @ both sides...:

Don't you think will that work???

CCMF · #6 (**permalink**) 03-19-10, 14:50

I will see if I can make it clearer what I mean:

First of all, please forget the Excel worksheet I mentioned in my first posting.
I have a lot of numbers like 338306,338406,338506. I want to know which of these values is not in a table (I know that 338306 is).

Maybe it could be something like:
select ... from .... where column_name ... in (338306,338406,338506)

Thank you.

n_i · #7 (**permalink**) 03-19-10, 15:29

Same principle:

Code:

with t(value) as (values 338306,338406,338506)
select value from t where not exists (
  select 1 from database_table dt where dt.value = t.value
)

CCMF · #8 (**permalink**) 03-19-10, 15:40

It works.

Thank you, Nick.

tonkuma · #9 (**permalink**) 03-19-10, 16:53

Quote:

What is the SQL statement to show me that 338406 and 338506 don't exist in the table?

It could be written by using NOT EXISTS(like Nick wrote), NOT IN, or EXCEPT(like Vyas wrote).

Code:

SELECT value
  FROM (VALUES 338306 , 338406 , 338506) t(value)
 WHERE NOT EXISTS
       (SELECT *
          FROM database_table dt
         WHERE dt.column_name = t.value)
;

Code:

SELECT value
  FROM (VALUES 338306 , 338406 , 338506) t(value)
 WHERE value NOT IN
       (SELECT column_name
          FROM database_table)
;

Code:

VALUES 338306 , 338406 , 338506
EXCEPT
SELECT column_name
  FROM database_table
;

CCMF · #10 (**permalink**) 03-22-10, 13:14

After some tests, I decided to use the first code that tonkuma suggested:

Quote:

SELECT value
FROM (VALUES 338306 , 338406 , 338506) t(value)
WHERE NOT EXISTS
(SELECT *
FROM database_table dt
WHERE dt.column_name = t.value)
;

It seems to have the lowest cost.

Thanks to all of you.

Internet Services

Web Development

Digital Marketing

Consumer Tech