-2

I am studying SQL and I am not sure which is the way to filtering data.

For example, there I have two tables:

Reference_OrderTable:

  • OrderID
  • Item
  • Price

OrderTable:

  • OrderID
  • Item
  • Price
  • Reference_Ordertable: this table has all the type of orders.
  • OrderTable: this is the actual order table, we store by customer's orders.

I am looking for missing orderID in OrderTable.

For example:

Reference_Ordertable:

OrderID: 1, 2, 3, 4, 5, 6, 7, 8

OrderTable:

OrderID: 1, 3, 4, 5, 7

I would like to find the missing part such as OrderID: 2, 6, 8 because OrderTable is missing 2,6,8 if we compare with Reference_Ordertable.

I was thinking to use Right Join method. However, Right Join contains common data and it is not searching missing part. How can we filter missing data from another table?

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
NBB
  • 137
  • 2
  • 6
  • 14
  • As an aside, while you could use an outer join here, I'd recommend a LEFT JOIN (not RIGHT). Technically either works, but LEFT JOINs are more common. https://stackoverflow.com/questions/436345/when-or-why-would-you-use-a-right-outer-join-instead-of-left – SOS Mar 22 '22 at 05:14

1 Answers1

4

You can try below.

Using EXCEPT

select OrderID from reference_OrderTable
EXCEPT
select OrderID from OrderTable

using join

select r.OrderID from reference_OrderTable r 
LEFT JOIN OrderTable o ON o.OrderID = r.OrderID
WHERE o.OrderID IS NULL

using sub queries

select OrderID from reference_OrderTable
where OrderID NOT IN (select OrderID from OrderTable)
Gaurang Prajapati
  • 128
  • 5
  • Good list. There's also `WHERE NOT EXISTS (....)`. BTW, I think the they're looking for orders missing from `OrdersTable`, rather than the other way around. – SOS Mar 22 '22 at 05:02
  • Thanks!! I kept searching Join method. I think, "Except" and "NOT IN" can be simpler. Good to learn EXCEPT and NOT IN. Thanks! – NBB Mar 22 '22 at 05:07
  • @NBB - They each have slight differences in behavior and pros/cons, so read up on them. Also, compare query execution plans to select the best option for your specific query. – SOS Mar 22 '22 at 05:30