Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2004
    Posts
    67

    Unanswered: Show values that are not in a table

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    select * from excel_worksheet eww where not exists (
      select 1 from database_table dt where dt.value = eww.value
    )
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2009
    Posts
    17
    select 1 from excel_worksheet eww EXCEPT(
    select 1 from database_table dt
    ) with ur

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Vaibhav Vyas View Post
    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.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jul 2009
    Posts
    17
    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???
    Last edited by Vaibhav Vyas; 03-18-10 at 08:42.
    Vyas| Miracle Happens

  6. #6
    Join Date
    Nov 2004
    Posts
    67
    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.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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
    )
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Nov 2004
    Posts
    67
    It works.

    Thank you, Nick.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ;
    Last edited by tonkuma; 03-19-10 at 17:58. Reason: Removed quotation marks from VALUES in the examples.

  10. #10
    Join Date
    Nov 2004
    Posts
    67
    After some tests, I decided to use the first code that tonkuma suggested:
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •