mercredi 27 mai 2015

Sql query: How change of table will leads to different result?

The database is:

Highschooler ( ID, name, grade ) English: There is a high school student with unique ID and a given first name in a certain grade.

Friend ( ID1, ID2 ) English: The student with ID1 is friends with the student with ID2. Friendship is mutual, so if (123, 456) is in the Friend table, so is (456, 123).

Likes ( ID1, ID2 ) English: The student with ID1 likes the student with ID2. Liking someone is not necessarily mutual, so if (123, 456) is in the Likes table, there is no guarantee that (456, 123) is also present.

The task is to: Find names and grades of students who only have friends in the same grade. Return the result sorted by grade, then by name within each grade.

These two commands seem should generate same result, but the fact is not.

The correct command is:

select distinct name, grade
from Highschooler s1
where not exists (select * from Highschooler s2, Friend
                  where Friend.ID2 = s2.ID
                  and s1.ID = Friend.ID1 
                  and s2.grade <> s1.grade)
 order by grade, name 

But I also think the following command which only change the position of Friend also make sense:

select distinct name, grade
from Highschooler s1, Friend
where not exists (select * from Highschooler s2
                   where Friend.ID2 = s2.ID
                  and s1.ID = Friend.ID1 
                  and s2.grade <> s1.grade)
 order by grade, name 

But the latter one only return all the value in table Highschooler.

I really thinks that change the position of Friend won't make a difference in the result but it seems that the result is different.

This phenomenon is confusing and I really appreciate it if some experts can provide some help.

Aucun commentaire:

Enregistrer un commentaire