I'm wondering if anyone can help me develop the right sql query.

I'm trying to select a "customerNumber" in table 1, but only if it doesn't exist in table2.

I tried this

Select Table1.customerNumber From Table1, table2
Where table1.customerNumber NOT IN (select table2.customerNumber from table2)

It isn't right because it displays customerNumber that exist in table2.
Can someone please help me?


Edited by haranaboy: n/a

6 Years
Discussion Span
Last Post by simplypixie

I tested your code, and it didn't give me customer numbers that I wasn't expecting. However, in your FROM clause, there is nothing relating table1 to table2. This gives you a cross-join (a.k.a. Cartesian Product) when you execute it. A corrected version of this would be:

select table1.customerNumber from table1
where table1.customerNumber not in (select table2.customerNumber from table2)

This gives a correct result.

An alternative method for doing this type of result retrieval uses a LEFT JOIN like so:

select table1.customerNumber 
from table1 
left join table2
on table1.customerNumber = table2.customerNumber
where table2.customerNumber is null

Depending on the size of the tables and the indices on each table, it could give you superior performance.

Hope this helps! Good luck!

Edited by BitBlt: n/a

Votes + Comments
Thank you for your detailed post.
Totally agree

You simply need

Select customerNumber From Table1
Where customerNumber NOT IN (select customerNumber from table2)

You don't need to join or select from 2 tables in the main part of your query

Votes + Comments
Thanks for your help
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.