Simple Oracle SQL query help needed

Posted on 2007-08-07
Last Modified: 2010-03-20
I need all id's in table1 that are not in table2 after a specific date.



I need to know all of the id's in table 1 that do not appear in table2 with a date greater than 1/1/07 for example.

This is an Oracle db.
Question by:jasonsbytes
    LVL 61

    Expert Comment

    SELECT Table1.ID
    FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID
    WHERE Table2.ID IS NULL AND Table1.Date > [SomeDate]
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    Another way ( not sure which is faster ):
    select id from table 2 where date > to_date('1/1/07','MM/DD/YY')
    select id from table1
    LVL 14

    Accepted Solution

    Another way
    select id from table1 where not exists (select 1 from table2 where = and >  to_date('1/1/07','MM/DD/YY'))
    LVL 17

    Author Comment

    Here were my results:
    mbizup: no results returned
    slightwv:  seems to return opposite of what I want.  I probably could just reverse it and get what I want.
    sathyagiri:  this seems to work.

    Thank you...
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    >>seems to return opposite of what I want.

    oops.  Yes, just reverse the minus and it should work.

    You should run an explainn plan on both to see which works best in your database.   My money is on the 'minus' query.
    LVL 17

    Author Comment

    Thanks I will...  This is just a one time (manual) data extract though, so not a big deal really.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now