Q:

Using join in place of this sub-query in MySQL

I have a table by the following structure and records, All i want to do is to just extract orderid of those records whose status id is either 1 or 2 , i want to exclude all orderid with statusid=3 , , problem is that orderid 106 has both status 1 and 3 ...
I have written a sub-query which serves the purpose.. 
select * 
from orders_status_history 
where orders_id NOT IN 
   (select orders_id 
    from orders_status_history 
    where orders_status_id = 3)

is there any other way of doing this without using sub-queries as i have heard it hampers performance. As my query can return thousands of rows as well.
id    order-id    status-id
1     1            1
2     2            1
3     105          1
4     106          1
5     106          3
6     108          1
7     109          1
8     109          2

Any help or suggestion will be highly appreciated ..Thanks in advance..

A:

You may do that:
SELECT  osh.*
FROM    orders_status_history osh
LEFT JOIN
        orders_status_history oshd
ON      oshd.orders_id = osh_orders_id
        AND oshd.orders_status_id = 3
WHERE   oshd.orders_id IS NULL

However, if you have an index on order_status_history (orders_id, orders_status_id), then the NOT IN query is just as fine.
You may want to read this:

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL

