finding difference between to selects

[room24]

select supplier_id
from suppliers
MINUS
select supplier_id
from orders;

whats the equivalent of this query using sql server??

 

[jby1]

Give this a try ...

select
(select supplier_id 
from suppliers)
-
(select supplier_id 
from orders)

 

[SQLBill]

jby1,

That won't work. MINUS in ORACLE isn't equivlent to subtraction. This is similar to a UNION in reverse. It returns the Supplier_IDs from Suppliers table that are NOT IN the Orders table.

So, this might work....

SELECT Supplier_ID
FROM Supplier
WHERE Supplier_ID NOT IN (SELECT Supplier_ID
                          FROM Orders)

-SQLBill

 

[SQLSister]

select supplier_id 
from suppliers
Left join
from orders
on suppliers.supplier_id = orders.Supplier_id
where Orders.supplier_id is null

This version should be more efficient than using not in.

Questions about posting. See faq183-874

 

[room24]

select supplier_id
from suppliers
Left join
from orders
on suppliers.supplier_id = orders.Supplier_id
where Orders.supplier_id is null

there is something wrong with the syntax in the code above

 

[SQLSister]

Oops, I was modifying your code and forgot to take out the from in front of orders.

Questions about posting. See faq183-874