Find ID which doesn't exist from another table-CodePudding

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!

CodePudding user response：

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)