where in (sub query) vs join I just want to ask about how to optimize my query. I am curious about the efficiency of using join instead of where in sub query. Can someone explain the difference?

SELECT  C.Customer_id,
        C.tran_id,
        t.date
    FROM CUSTOMERS C
    INNER JOIN TRANSACTIONS T ON c.Customer_id = t.Customer_id
    WHERE C.Customer_id IN (
        SELECT Customer_id FROM TRANSACTIONS_DATE WHERE tran_Date &lt;= '2013-01-01'
    )
    AND C.Customer_id NOT IN(1,2,3)
I think you can move your subquery onto the join condition, as you're just looking for customers' transactions before or on 1st Jan 2013. 

SELECT  C.Customer_id,
        C.tran_id,
        t.date
    FROM CUSTOMERs C
    INNER JOIN TRANSACTIONS T ON c.Customer_id= t.Customer_id
    AND t.tran_Date &lt;= '2013-01-01'
  WHERE  C.Customer_id NOT IN(1,2,3)