Symmetric Pair in SQL with JOIN

I am trying to find the name of students where a symmetric pair exists. There are 3 tables:

**student**
student_id (Primary Key) | smallint
student_name             | varchar(30)

**mathematics_marks**
student_id (Primary Key) | smallint
score                    | float (5,2)

**science_marks**
student_id (Primary Key) | smallint
score                    | float (5,2)

with Functions as (
select s.student_name as name, mm.score as math_score, sm.score as science_score 
from student s 
join mathematics_marks mm 
on mm.student_id = s.student_id
join science_marks sm 
on sm.student_id = s.student_id)
select t1.name
from Functions t1
join Functions t2
    on  t1.math_score = t2.science_score
    and t1.science_score = t2.math_score
where t1.math_score < t1.science_score

Edit from your comment: A student is called as being a part of a symmetric pair if the marks obtained by that student in science is equal to the marks obtained by some other student in mathematics and the marks obtained in mathematics are the same as marks obtained by the other student in science.

Answer

Given the structure of the data, I would assume that students could have multiple marks in each subject. Otherwise, why store the values in separate tables?

To solve this problem, I would preaggregate the marks:

with mm as (
      select student_id, group_concat(score order by score desc) as math_scores
      from mathematics_marks
      group by student_id
     ),
     sm as (
      select student_id, group_concat(score order by score desc) as science_scores
      from science_marks
      group by student_id
     ),
     sms as (
      select *
      from mm join
           sm
           using (student_id)
     )
select sms.student_id, sms2.student_id
from sms join
     sms sms2
     on sms.math_scores = sms2.science_scores and
        sms.science_scores = sms2.math_scores and
        sms.student_id < sms2.student_id;

This returns the matching ids. You need an additional join if you want to bring in the names.

Note: You have stored the values as floats. This is quite dangerous. You should be storing the values as decimal/numeric. Two values that look the same might actually be different.