Trying to understand the difference between IN and EXISTS

Question

I'm currently taking a course and during one of the tests, I came across this question.

The math_students and english_students tables have the following columns:

student_id, grade, first_name, last_name

Using a subquery, find out what grade levels are represented in both the math and english classes.

The query I used was this.

select distinct grade 
from math_students 
where grade in (
select grade 
from english_students
);

However, it was graded as incorrect and the correct answer was given as

SELECT grade
FROM math_students
WHERE EXISTS (
  SELECT grade
  FROM english_students
);

I would really appreciate it if someone could help me understand the difference in the two queries because the output was the same in both cases. Also, why doesn't the query contain distinct?

Answer 1

The version with EXISTS is incorrect. Period. It is answering the "question":

Return all grades for math students if there is at least one English student.

Not very useful. The correct EXISTS would be:

SELECT DISTINCT grade
FROM math_students ms
WHERE EXISTS (
  SELECT 1
  FROM english_students es
  WHERE ms.grade = es.grade
);

If your database supports it, I would expect you to also be learning:

select grade
from math_students
intersect
select grade
from english_students;

Answer 2

The "In" clause checks if the value is a member of a provided list, this can either be a hard coded list or a query result.

In your example if the table english_students was defined as

id name grade
-- ---- -----
1  Joe  6
2  Bill 8
3  Sue  7

The query:

select distinct grade 
from math_students 
where grade in (
select grade 
from english_students
);

Will evaluate to:

select distinct grade 
from math_students 
where grade in (
"6","8","7"
);

Order of operations executes the query in parens first, then executes the outer query so since you are saying

select grade 
from english_students

It would return "6","8","7" then execute the outer query.

Your second query:

SELECT grade
FROM math_students
WHERE EXISTS (
  SELECT grade
  FROM english_students
);

"Exists" checks for just that Existence of data (or objects), so you are saying select all data in math_students if data exists in english_students as Gordon pointed out.

To answer the question in the title. The big distinction between "In" and "Exists" is: "In" is evaluates to "Give me anything that matches one of these values" where "Exists" evaluates to "Give me anything that exists"

The reason the second does not require a distinct is probably data related.

As Gordon pointed out "intersect" will append multiple results to the same result set and return the distinct records.