About the usage note Rules for SQL statement in (GO)

SELECT * from Tuser where Userno not in (select Userno from Filter_barcode)

In the above sentence query,Userno does not exist in the Filter_barcode table, but the entire statement can be executed normally (the query that executes the subquery will report the absence of the field), and returns all the result sets in the Tuser table. If you are not aware of this situation, once it is not used to query, is used to delete, the entire table data is unknowingly deleted.

Reason: The original is not to use the table alias if the in sub-query field in the inner table can not find the reference to the appearance.

The following four articles I have seen from other places, posted for your reference

1. The child table refers to the parent table column, but not itself, in the case that the child table has data, return all non-null key parent table records, the child table is empty, the result is no
2. The child table references the parent table property, and only the outermost subquery can reference
3. There is a prefix identifier, by prefix, if the child table parent table prefix, by the 4 rule
4. If the prefix identifier is unique, the subquery table also has this field, then the local subquery will prevail
If the prefix is the same, this field is present in the subquery, whichever is the subquery table, or the parent table

To avoid this problem there are several ways for you to refer

1, when the need to use in sub-query, the first to execute the sub-query in the inside of the error, if the wrong to modify the corresponding

2, using the table prefix (alias) is the hard truth, for example

3. Use the EXISTS statement instead of the in statement

About the difference between exists and in usage here is not the story, you can query the relevant information

For in and exists differences: if the subquery results in fewer result set records, the table in the main query should use in if it is large and indexed, whereas if the outer main query records are small, the tables in the subquery are large and the indexes are indexed with exists. In fact, we distinguish in and exists is mainly caused by the change of the driving sequence (this is the key to performance change), if it is exists, then the other layer table is the driver table, first accessed, if it is in, then execute the subquery first, so we will be the driver table of the fast return as the target, Then the relationship between the index and the result set is considered, and in is not null processing.
Http://www.cnblogs.com/xiaoqiangzhou/p/3919270.html