mysql连接1表的位置

Question

I have 2 tables, student and grades student table contains id, name and date_of_birth grades table contains id, student_id, grade and course Actual table contain more data.

I have a query like

SELECT s.*, AVG(g.grade) as average_grade 
FROM student s LEFT JOIN grade g ON s,id = g.student_id 
WHERE g.course = 'mathematics' and s.id = 1

With this I could get the data i needed which are student details and the average grade, then come the problem where when the course = "mathematics" is not found in the grades table, the query will return NULL. My question is, is there a way for me to get the s.id = 1 details together with NULL average instead of all NULL value?

I would prefer if it is able to do it with 1 query, as because in my current I am using subquery and it takes very long to get the data. My main objective is to get more faster speed if you have better way instead of using 1 query feel free to comment your idea. In addition I have tried multiple query and sub query to get all the data but it all take too long.

Answer 1

Move your filter criteria for g.course = 'mathematics' in joining part

SELECT s.*, AVG(g.grade) as average_grade 
FROM student s 
LEFT JOIN grade g ON s.id = g.student_id AND g.course = 'mathematics'
WHERE  s.id = 1

Your query produces result as inner join not left because putting g.course = 'mathematics in where clause turns your left join to inner join, Moving this part in on clause will still return data from student table if there were no rows found from grade table with course = 'mathematics'