What is the left join that equals this righ join?

Question

Thanks first.

I have this right join worked fine, but when I try to change it into a left join I got an error.

The question is to get all information about the students from table Student who has higher score in class1 than class2 from a score-student table called SC.

Student(SId,Sname,Sage,Ssex)

SC(SId,CId,Score)

Here is my RIGHT JOIN:

SELECT * FROM Student RIGHT JOIN (
    SELECT t1.SId, class1, class2 FROM
      (SELECT SId, score as class1 FROM sc WHERE sc.CId = '01')AS t1,
      (SELECT SId, score AS class2 FROM sc WHERE sc.CId = '02')AS t2
    WHERE t1.SId = t2.SId
    AND t1.class1 > t2.class2
    )r 
ON Student.SId = r.SId;

And then I tried something like:

SELECT t1.SId, class1, class2, Student.* FROM
    (SELECT SId, score as class1 FROM sc WHERE sc.CId = '01')AS t1,
    (SELECT SId, score AS class2 FROM sc WHERE sc.CId = '02')AS t2
    WHERE t1.SId = t2.SId
    AND t1.class1 > t2.class2
)r
LEFT JOIN Student
ON Student.SId = r.SId;

but it didn`t work.

I am pretty new with SQL, wish you could explain a little bit.

I am using MySQL5.7

Answer 1

The first query that works is here:

SELECT * FROM Student RIGHT JOIN (
    SELECT t1.SId, class1, class2 FROM
      (SELECT SId, score as class1 FROM sc WHERE sc.CId = '01')AS t1, 
      (SELECT SId, score AS class2 FROM sc WHERE sc.CId = '02')AS t2
      WHERE t1.SId = t2.SId
      AND t1.class1 > t2.class2
    )r 
ON Student.SId = r.SId;

The table r is being created via this sub-query:

SELECT t1.SId, class1, class2 FROM
  (SELECT SId, score as class1 FROM sc WHERE sc.CId = '01')AS t1, 
  (SELECT SId, score AS class2 FROM sc WHERE sc.CId = '02')AS t2
WHERE t1.SId = t2.SId
AND t1.class1 > t2.class2

t1.Sid, class1 and class2 are resolved without ambiguity. t1.Sid becomes available as r.Sid in the join Student.SId = r.Sid. Hence, the query works.

The second query that causes problem is here:

SELECT t1.SId, class1, class2, Student.* FROM
   (SELECT SId, score as class1 FROM sc WHERE sc.CId = '01')AS t1, 
   (SELECT SId, score AS class2 FROM sc WHERE sc.CId = '02')AS t2
  WHERE t1.SId = t2.SId
  AND t1.class1 > t2.class2
)r
LEFT JOIN Student
ON Student.SId = r.SId;

There are two issues to be addressed:

• Issue 1: Student.* is not resolved since the FROM section does not have any reference to it.
• Issue 2: Table r is not getting constructed correctly.

The two issues when addressed result in this modified second query, which works:

SELECT * from 
  (SELECT t1.SId, class1, class2 FROM
     (SELECT SId, score as class1 FROM sc WHERE sc.CId = '01')AS t1,
     (SELECT SId, score AS class2 FROM sc WHERE sc.CId = '02')AS t2
    WHERE t1.SId = t2.SId
    AND t1.class1 > t2.class2
  )r
LEFT JOIN Student
ON Student.SId = r.SId;