XOR Select

Posted on 2005-04-12
Last Modified: 2006-11-18
Hi everyone,

need help.

I have two table with same structure (T1 and T2).
T2 is a subset of T1.

How can I select from T1 such that I can exclude the rows found in T2.

e.g. T1 has 467 rows, T2 has 72 rows.  I want to get the 395 rows

is there something like a

Thanks in advance
Question by:3Mann
    LVL 17

    Accepted Solution

    there are various equivalent forms, try this one, it is normally the most efficient :

    SELECT T1.* FROM T1 left outer join T2 on T1.PrimaryKey = T2.PrimaryKey WHERE t2.PrimaryKey IS NULL
    LVL 1

    Author Comment

    there is no primary key for the table
    just a compund key
    table has 3 columns and the 3 columns are the members of the compund key

    is it still possible?
    LVL 14

    Expert Comment

    by:Renante Entera
    Hi 3Mann!

    Perhaps, this is what you are looking for :

    Select t1.* From t1
    Where Not Exists
      Select * From t2
      Where t2.field1 = t1.field1
      And t2.field2 = t1.field2
      And t2.field3 = t1.field3

    Hope this helps you.  Just try it.

    eNTRANCE2002 :-)
    LVL 17

    Expert Comment

    you can use compound key just the same :

    SELECT T1.* FROM T1 left outer join T2
    on T1.field1 = T2.field1
    and T1.field2 = T2.field2
    and T1.field3 = T2.field3
    WHERE t2.PrimaryKey IS NULL

    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.

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how the fundamental information of how to create a table.

    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

    7 Experts available now in Live!

    Get 1:1 Help Now