Retrieval of Unmatched rows

Question

I have 2 tables Order and OrderItem. Order table has the column ID_Order and OrderItem has the foreign key FK_Order which is the value from ID_order of order table.

Now I have to create a query showing all entries from table order where no reference is in orderItem (id_order is not used as fk_order in the table orderitem).

Please let me know. I used outer joins..but they are not displaying proper results.

Thanks

score -1 · Accepted Answer · edited May 23 '17 at 10:33

-1

I would recommend using Exist rather than IN clause. Because it will be faster

SELECT *
FROM   Order o
WHERE NOT EXIST (SELECT *
                    FROM   OrderItem oi
                    WHERE o.ID_order = oi.ID_order)

Another way would be LEFT JOIN

SELECT DISTINCT o.*
FROM   Order o LEFT JOIN OrderItem oi 
       ON o.ID_order = oi.ID_order
WHERE oi.ID_order IS NULL

edited May 23 '17 at 10:33

Community

1
1

answered Nov 30 '14 at 14:04

Mostafa Armandi

879
1
11
24

Out of above queries which one is faster..please let me know..Thanks for the help.. – user2028871 Dec 01 '14 at 04:52
1

This is not true - in all versions since 2008 they produce the same plan and execute identically. – JNK Dec 24 '14 at 13:53

Retrieval of Unmatched rows

1 Answers1