sql中exists替換in的區別

在sql中使用exists替換in查詢時要注意使用exists時一定要關聯主查詢和子查詢的關聯不然查詢會得不到相應的結果如下語句：
 語句一使用in查詢：
 select realname from Users where Users.UserId  in
 (select Gallery.Galleries.CreatorId from  Gallery.Galleries  group by Gallery.Galleries.CreatorId
 having COUNT(Gallery.Galleries.CreatorId)>1 ) Order by UserId 
 語句二使用exists查詢：
 select realname from Users where exists
 (select Gallery.Galleries.CreatorId from  Gallery.Galleries  group by Gallery.Galleries.CreatorId
 having COUNT(Gallery.Galleries.CreatorId)>1 ) Order by UserId
 乍一看沒有錯誤，但是語句二忘記了主查詢和子查詢的主鍵的關聯，導致把主查詢的所有內容查出來。
 語句二的正確寫法應該是：
  select * from Gallery.Galleries as A where exists (select A .CreatorId from  Gallery.Galleries as B
  Where A.CreatorId = B.CreatorId  group by B.CreatorId having COUNT(B.CreatorId)>1)Order by CreatorId
註：exists是boolean運算的只要字查詢的結果集有一條資料結果就為真where後的條件就為真所以第二條查詢語句等價於
 select realname from Users where 1=1 Order by UserId（如果子查詢有一條資料被查出）