CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Crezzer7
Crezzer7

Reputation: 2335

SQL Server WHERE NOT EXISTS not working

I have the 3 below statements,

  1. Selects the Order Numbers that dont exist
select Orders.OrderNumber 
FROM Orders 
inner join InvoiceControl on Orders.OrderNumber = InvoiceControl.OrderNumber 
where not exists (select OrderNumber from Orders where InvoiceControl.OrderNumber = Orders.OrderNumber)
  1. Selects a specific Order number that does not exist
select OrderNumber from Orders where OrderNumber = 987654
  1. Selects the specific Order Number in the corresponding table that does not exist
select OrderNumber from InvoiceControl where OrderNumber = 987654

these 3 queries work in other scenarios regarding other tables but not this one, have I made an obvious mistake anywhere? below is the query ran and the outputs: enter image description here

the idea behind this is to locate the OrderNumbers that do not exist in the InvoiceControl, based on the OrderNumbers in the Orders Tabl, so the top query would also return the value 987654 as this value has not yet been included in the InvoiceControl Table as this could be a new Order without an Invoice

Upvotes: 1

Views: 3209

Answers (3)

MADnoobie
MADnoobie

Reputation: 74

If what you're looking for is to find all the ordernumbers in tbl Orders and not in tbl InvoiceControl. Then I would try this instead.

Select O.Ordernumbers from Orders O
Left Join Invoicecontrol I
On O.Ordernumbers = I.Ordernumbers
Where I.Ordernumbers is null

Upvotes: 0

siride
siride

Reputation: 210025

In the first query, you first asked for rows in both Orders and InvoiceControl (by way of the FROM and JOIN tables), and then you added in your WHERE clause a request to exclude all rows that exist in Orders. Since your starting set only includes rows that are in Orders, if you ask for all of those rows to be excluded, you will get no results back.

Upvotes: 0

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

Because your INNER JOIN will already create all correspondents between Orders.OrderNumber = InvoiceControl.OrderNumber.

After this result set is built, you actually filter out everything based on the condition in your WHERE.

where not exists (select OrderNumber from Orders where InvoiceControl.OrderNumber = Orders.OrderNumber)

Hypothetically, if you'd have just 987654 in your Orders table and you'd have a Correspondent in your InvoiceControl table, then the following query, without your WHERE clause

select Orders.OrderNumber 
FROM Orders 
inner join InvoiceControl on Orders.OrderNumber = InvoiceControl.OrderNumber

would return:

OrderNumber
987654

Then, by applying your where not exists (select OrderNumber from Orders where InvoiceControl.OrderNumber = Orders.OrderNumber) condition, you'd be looking for all records that do not have a correspondent (but you already have all possible correspondents between your two tables, based on your INNER JOIN).

Thus, your result will be:

OrderNumber

Upvotes: 3

Related Questions