Find student id with highest marks using INNER JOIN and EXCEPT?

Given a table, find the highest marks using INNER JOIN and EXCEPT. It straight forward find marks. Select max(marks) from Students. But how to find highest using INNER JOIN and EXCEPT?

Students Table

sno	name	marks
1	A	90
2	B	95
3	C	96
4	D	82
5	E	87

Answer

You don’t need to write an INNER JOIN or EXCEPT (your question is tagged mysql which doesn’t support the EXCEPT statement) to get the student id with the highest mark. It’s much simpler to write:

select sno from students
where marks = (select max(marks) from students)

Result:

3

If you use an INNER JOIN you just complicate things and essentially do the same. Example:

select s1.sno, s1.marks from students s1
inner join (select max(marks) as max_mark from students) s2
on s1.marks = s2.max_mark