I have the following two tables:

ITEM (Item#, Item_Name, Unit_Price)

ORDER_ITEM (Order#, Item#, Qty)

Now, how do I list the items that have never been ordered by using the MINUS operator?


How about:

select item# from item
select item# from ORDER_ITEM
|improve this answer|||||
  • Yeah that can work ..... However, I was interested to display Item_Name instead of Item# by using MINUS operator. – stranger Aug 10 '17 at 11:38
  • select * from item where item# in (select item# from item minus select item# from ORDER_ITEM) – CalZ Aug 10 '17 at 12:00

This is a bit long to add as a comment to @CalZ answer so I create another answer:

select item#, Item_Name from item
select i.item#, i.Item_Name 
from item i
join order_item oi
    on i.item# = oi.item#

in the second leg of the minus, we construct a new relation using a join between item and order_item that we can subtract from item.

|improve this answer|||||
  • That seems like an unnecessary join and it would be better to use the list of items to select from the item table. – Joe W Aug 10 '17 at 12:53
  • 1
    @JoeW why is it unnecessary? How could we get Item_Name without a join to item? – ypercubeᵀᴹ Aug 10 '17 at 13:47
  • By using the return from the selects with the minus as input into another select. I might be wrong but it seems like it would be faster to just do a direct select then to do the join since you wont be selecting a lot of duplicate data from the item table.. Using the comment from CalZ on the other answer seems faster. select * from item where item# in (select item# from item minus select item# from ORDER_ITEM) – Joe W Aug 10 '17 at 13:54
  • @JoeW so it would still be one join. But different order of logical execution (minus vs join). I don'tknow enough about Oracle and whether one or the other would be more efficient. or whether Oracle optimizer knows to rewrite the 2 versions/ways. – ypercubeᵀᴹ Aug 10 '17 at 14:03
  • If the order table is 100+ times as large as the item table do you think the work to join the item table in during the initial select is going to be the same as using the results to select from the item table? – Joe W Aug 10 '17 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.