Stuck in a sql statement.

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?

 

Select * from table1
where customer_name not in
(select customer_name
from table2)

 

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

 

Thanks. I was looking for a similar thing 2.

 

i dont think join will be faster then the first query wriiten above in this case..

 

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):