Comparing the contents of 2 identical tables in mysql

Discussion in 'MySQL' started by jewellery, May 22, 2007.

  1. #1
    Hi all, I was wondering if someone could shed some light on a little query I have.

    I have 2 quite large tables (~100,000 entries each, ~90meg each) with exactly the same table structures, but some differing entries. Lets call them table1 and table2, with a structure of [id, name, description]. What I want to be able to do is find what entries are present in table2 that aren't in table1. I'm sure this should be a simple query but it escapes me at the moment.

    I would be very grateful if anyone could help me out and maybe shed some light on this :)
     
    jewellery, May 22, 2007 IP
  2. turiel

    turiel Peon

    Messages:
    148
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Right, I think this is what you're looking for:

    SELECT * FROM table2 LEFT JOIN table1 ON table1.name = table2.name WHERE table1.name IS NULL;

    Or something along those lines, you might have to swap the parameters around a litte. Trying to visualise these things without actually performing the query to test is a little hit and miss.
     
    turiel, May 22, 2007 IP
    jewellery likes this.
  3. jewellery

    jewellery Guest

    Messages:
    180
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Excellent! Works wonderfully! Many thanks turiel :)
     
    jewellery, May 22, 2007 IP
  4. Mr D

    Mr D Peon

    Messages:
    35
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #4
    why not everything in one table if the structure is the same?
     
    Mr D, May 22, 2007 IP
  5. turiel

    turiel Peon

    Messages:
    148
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Guessing one is a backup or test table and he wants to combine them or just see the difference.
     
    turiel, May 22, 2007 IP
  6. jewellery

    jewellery Guest

    Messages:
    180
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Its an affiliate datafeed and they don't supply a 'changes only' feed :(
     
    jewellery, May 22, 2007 IP