SQL Statement

Posted on 2008-11-19
Last Modified: 2012-05-05

I need a SQL statement that selects records where a column value in one table does not exist in another table

For example
TableA has a column called Part. There could be 1 million plus records in this table

TableB has a column called NonPart. This table could have several thousand records.

What I need is a list of all items in TableA where the PART is NOT IN TableB NonPart column.

I've tried several things, nothing seems to work. I end up getting some records that are correct, and some that are not (meaning it does exist in TableB).

Any help is greatly appreciated.
Question by:WaldaInc
    LVL 15

    Accepted Solution

    Did you try this style?

    SELECT TableA.*
    FROM TableA
    WHERE TableA.Part NOT IN
         (SELECT NonPart FROM TableB)

    Expert Comment

    SELECT * FROM myTable WHERE part NOT IN (SELECT otherpart FROM myOtherTable)

    Author Closing Comment

    No. I didn't try NOT IN.

    Man I was trying <> and stuff like that.  I had the inner select correct just didn't use the NOT IN

    Thank you so much. I think you save me pulling a few hairs out today.
    LVL 3

    Expert Comment

    as an alternative - OUTER JOIN may be used.

    SELECT TableA.* FROM TableA LEFT OUTER JOIN TableB ON Part=NonPart WHERE NonPart is NULL

    In some engines (not sure about MS SQL 2005) it works faster.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    760 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

    8 Experts available now in Live!

    Get 1:1 Help Now