sql中 in_notin_exists_not exists的用法和差别.docxVIP

sql中 in 、not in 、exists、not exists 用法和差别exists （sql返回结果集为真）not exists (sql不返回结果集为真）如下：表A ID NAME 1? ? A1 2? ? A2 3? ? A3 表B ID AID NAME 1? ? 1 B1 2? ? 2 B2 3? ? 2 B3 表A和表B是１对多的关系 A.ID = B.AID SELECT ID,NAME FROM A WHERE EXIST (SELECT * FROM B WHERE A.ID=B.AID) 执行结果为1 A1 2 A2 原因可以按照如下分析SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=１) ---SELECT * FROM B WHERE B.AID=１有值返回真所以有数据SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2) ---SELECT * FROM B WHERE B.AID=２有值返回真所以有数据SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3) ---SELECT * FROM B WHERE B.AI