Hi,

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?

Thanks

Recommended Answers

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:[CODE]select table1.customerNumber from table1
where …

Jump to Post

All 2 Replies

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!

commented: Totally agree +9
commented: Thank you for your detailed post. +2

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

commented: Thanks for your help +2
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.