dcsimg
Results 1 to 6 of 6

Thread: Compare/filter 2 tables...help!!!

  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Unhappy Compare/filter 2 tables...help!!!

    Would appreciate if someone could help!

    I have 2 tables , table 1 and table 2. Exact copies of some records from table 1 also exist in table 2. What i need to do is display records that exist in table 1 but do not exist in table 2.

    Thanks for your help!

  2. #2
    Join Date
    May 2004
    Location
    Germany
    Posts
    9
    Hi

    SELECT * FROM table1
    MINUS
    SELECT * FROM table2

    This query will return all the rows which are in table1 but not in table2.

    The two tables need to have the same structure (columns, data types and so on), if not, than you may replace the "*" with the list of columns common to both tables.

    Hope this helps.

    Regards,
    Georg

  3. #3
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    create table x1 (id int, name varchar(100))
    create table x2 (id int, name varchar(100))

    insert into x1 select 1,'a'
    insert into x1 select 2,'b'
    insert into x1 select 3,'c'
    insert into x1 select 4,'c'
    insert into x1 select 5,'c'

    insert into x2 select 1,'a'
    insert into x2 select 2,'b'
    insert into x2 select 3,'c'

    select min(TableName), id,name from
    (
    select 'Table1' as TableName, id,name
    from x1
    union all
    select 'Table2' as TableName, id,name
    from x2
    ) as mytable
    group by id,name
    having count(*) = 1

  4. #4
    Join Date
    May 2004
    Location
    Germany
    Posts
    9
    Hi MAK,

    your code has a bug. It will return the correct rows ONLY if the second table X2 doesn't contain rows which aren't in X1 (so only if X2 is a subset of X1). As soon as X2 contains rows which are not in X1 it will also return this rows which is not wat olu wanted.

    Imho it's also "too complicated" for the task at hand and probably wouldn't perform very well on big tables (first a UNION, like in my example, and then a GROUP BY on it, which is very sort intensive, the dabase will do it on the hard disks if there is not enough memory for the sort).

    Regards,
    Georg

  5. #5
    Join Date
    May 2004
    Location
    South Africa
    Posts
    1
    a cheaper solution to the request and the one which usually performs the best is:
    create table x1 (id int, name varchar(100))
    create table x2 (id int, name varchar(100))

    insert into x1 select 1,'a'
    insert into x1 select 2,'b'
    insert into x1 select 3,'c'


    insert into x2 select 1,'a'
    insert into x2 select 2,'b'
    insert into x2 select 3,'c'
    insert into x2 select 4,'d'
    insert into x2 select 5,'e'



    -- the same result acheived in a cheaper way
    select x2.*
    from x1
    right join x2 on x1.id = x2.id -- join on whatever the PK is
    where x1.id is null

    just compare the execution plan with the proposal above ... then use whatever performs better in your case

  6. #6
    Join Date
    May 2004
    Location
    Germany
    Posts
    9
    Hi TSideov,

    you are right it may perform better. I work mostly on datawarehouses and made the experience that select minus select for really big tables faster than an outer join is.

    The other problem with data warehouses is that you have to load, cleanse and process external data and don't always have a primary key at the beginning of the process, but still have to get the delta to some of the datawarehouse tables.

    Regards,
    Georg

Posting Permissions

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