>>

>>select * from mytable1 LEFT join mytable2 on mytable1.fld1 = mytable2.fld1 where myTable2.fld1 IS NULL

>
>But the value in MyTable2.fld1 is not NULL. It has some entry but the entry has not corresponding value in MyTable1.
>
>Thank you.

The select I posted will return all records in table1 that do not have child records in table2. Try it.

select my.* from mytable1 my LEFT join mytable2 my2 on my.fld1 = my2.fld1 where my2.ID IS NULL

Also please notice that I use LEFT JOIN, not INNER JOIN. Left JOIN returns all records from the left table even if we don't have matching records in the right table (table2). By using IS NULL condition on the second table we retrive only non-matching records.

This performs better than NOT IN query.