Differerence in Exists and In operator in sql query

You can get Difference in Exists and In operator in sql query by below example.

Using SQL server database 2008 R2 in query editor.

SELECT * FROM Dept where DeptID IN (select Deptid from subDept )
SELECT * FROM Dept where exists (select Deptid from subDept )

This a simple example where I know the second query will execute faster than the first.

The qualifier "if both Deptid columns are unique" is a critical requirement in order for the JOIN to produce the same results as the IN.
So:

•JOIN will produce one row for each matching row in subDepts.
•IN will produce only one row if there is one or more matching rows in subDepts
•EXISTS will produce one row if there are any rows in subDepts regardless of whether they match or not