Getting an error using EXISTS operator but correct result with IN operator in

I am getting an error An expression of non-Boolean type specified in a context where a condition is expected while using EXISTS But when I am using IN operator it is giving the correct result.

Below is enter image description hereboth query

SELECT * FROM emp
WHERE Emp_id IN (SELECT Emp_id FROM Department WHERE Department.Emp_id=emp.Emp_id)

SELECT * FROM emp Department
WHERE emp.Emp_id EXISTS (SELECT Emp_id FROM Department WHERE emp.Emp_id=Department.Emp_id)

Answer

EXISTS is a unary operator, not a binary operator, and takes only a subquery as its argument. You say:

WHERE EXISTS (subquery...)

not

WHERE somefield EXISTS (subquery...)

Since EXISTS merely tests whether the subquery would return at least one row, the fields selected are not relevant and people commonly do:

EXISTS (SELECT 1 FROM ...)

though selecting null or any other value or multiple values works just the same.