Stuck in a sql statement.

Discussion in 'Databases' started by mnaghdi, Nov 27, 2009.

  1. #1
    Hi

    Lets say I have 2 tables each has only 1 field - for simplisity -

    Table1:
    Customer_Name

    Table2:
    Customer_Name

    How do I get all the rows from Table1 WHERE there is no similar value in Table2?

    another word: SELECT * THE Customer_Names in Table1 WHERE There is no row in Table2 with the same Customer_Name

    Any idea?
     
    mnaghdi, Nov 27, 2009 IP
  2. rayqsl

    rayqsl Active Member

    Messages:
    91
    Likes Received:
    0
    Best Answers:
    1
    Trophy Points:
    53
    #2
    Select * from table1
    where customer_name not in
    (select customer_name
    from table2)
     
    rayqsl, Nov 27, 2009 IP
  3. mastermunj

    mastermunj Well-Known Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    110
    #3
    Following query will also do the trick and will be faster..

    Select A.* from Table1 A left join Table2 B on A.Customer_Name = B.Customer_Name where B.Customer_name is null
     
    mastermunj, Nov 27, 2009 IP
  4. vlad11

    vlad11 Peon

    Messages:
    40
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Thanks. I was looking for a similar thing 2.
     
    vlad11, Nov 30, 2009 IP
  5. tukyunaaya

    tukyunaaya Peon

    Messages:
    14
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    i dont think join will be faster then the first query wriiten above in this case..
     
    tukyunaaya, Dec 7, 2009 IP
  6. OracleLover

    OracleLover Member

    Messages:
    51
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #6
    Simple one as mentioned by raysql

    SQL> select * from table1;
    
    CUSTOMER_NAME
    -------------
    Scripter
    Oracle
    Unix
    
    SQL> select * from table2;
    
    CUSTOMER_NAME
    -------------
    Oracle
    Scripter
    
    SQL> Select * from table1 where customer_name not in (select customer_name from table2);
    
    CUSTOMER_NAME
    -------------
    Unix
    
    SQL>
    
    Code (markup):
     
    OracleLover, Dec 8, 2009 IP