What is the EXISTS operator in SQL Server ? OR What is the EXISTS keyword in SQL Server ?

When you use EXISTS, you do not really returned data - instead you return a simple TRUE/FALSE regarding the existence of data that meets the criteria established in the query that the EXISTS statement is operated against.
EXISTS simply test whether the inner query returns any row. If it does, then the outer query proceeds, if not, the outer query does not executes, and the entire SQL statement returns nothing.

The EXISTS condition is considered "to be met " if the subquery returns at least one row.

The syntax for EXISTS condition is -
SELECT columns FROM sometable WHERE EXISTS (subquery)