A cláusula EXIST, segundo o site MSDN da microsoft, especifica uma subconsulta a ser testada quanto à existência de linhas. IN segundo o site MSDN da Microsoft determina se um valor especificado corresponde a qualquer valor em uma subconsulta ou uma lista.

Por exemplo: Imagine em um sistema previdenciário, um relatório que deseja trazer todos os credenciados que tem dependentes.

SELECT nome, matricula FROM credenciado WHERE EXIST (
    SELECT ID FROM dependente WHERE dependente.responsavelID = credenciado.ID
)

A consulta retornará apenas credenciados que tenham pelo menos um dependente. Como a claúsula EXIST deseja saber se existe pelo menos um, ele não realizará uma busca em toda a base de dados (Full Scan). No momento em que a subconsulta encontrar o primeiro resultado será finalizada, poupando recursos do servidor.

A cláusula IN tem um funcionamento diferente, utiliza-se quando o objetivo é verificar se a consulta retorna algum linha que esteja compatível com valores especificados.

Por exemplo: No mesmo sistema previdenciario, um relatório que se deseja trazer todos os credenciados que já morreram ou são inválidos.

SELECT nome, matricula FROM credenciado WHERE status IN (FALECIDO, INVALIDOS)

Os valores especificados podem ser através de uma subconsulta.