SQL LEFT JOIN with WHERE statement after is not displaying unconnected rows

Question

I’m trying to return all of the course_codes from my curriculum table while also displaying a specific student’s progress in each course. The transcript table holds all the courses the student has taken or is taking and has their grade is those courses. When I use the below code, I almost get the output I’m looking for (All courses codes in the curriculum and the student’s grade from the transcript table if they have taken a course that is in the curriculum. The courses the student hasn’t taken yet should display an output of null in any field from the transcript table). But I need to return the grades for a specific student and not all of the students. The query below does not take that into account.

SELECT DISTINCT curriculum.Course_Code, transcript.Course_Grade, transcript.Course_Comp, transcript.CWID 
FROM curriculum 
LEFT JOIN transcript 
ON curriculum.Course_Code = transcript.Course_Code ;

Result: Query with no WHERE

The next query is the same as the one above but tries to return the grades for a specific student by adding WHERE transcript.CWID = “C38475920”. The output of the query is only the class that are in both the student’s transcript and curriculum.

SELECT DISTINCT curriculum.Course_Code, transcript.Course_Grade, transcript.Course_Comp, transcript.CWID 
FROM curriculum 
LEFT JOIN transcript 
ON curriculum.Course_Code = transcript.Course_Code 
WHERE transcript.CWID = "C38475920";

Result: Query with WHERE

How can I get a result of all the course_codes from the curriculum and a student’s grades in those courses with a CWID of "C38475920"?

Example: If a student with a transcript.CWID of “C38475920" has completed 5 courses in the curriculum and there are a total of 10 courses in the curriculum. All 10 course_codes in the curriculum will be returned with 5 courses that the student has taken showing grade values and 5 courses the student hasn’t taken yet showing NULL values in transcript.COURSE_GRADE, transcript.COURSE_COMP, etc…

To get true LEFT JOIN result, move the transcript.CWID from WHERE to ON. As it is now you get regular INNER JOIN result. — jarlh, Apr 01 '20 at 21:00
Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN ON you want as part of an OUTER JOIN ON. After a LEFT JOIN ON, a WHERE or INNER JOIN that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Apr 01 '20 at 21:19
This is a faq. Before considering posting please read the manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. — philipxy, Apr 01 '20 at 21:19

score 1 · Accepted Answer · answered Apr 01 '20 at 21:00

Just move the condition on the left joined table from the where clause to the on part of the join:

SELECT ...
FROM curriculum c
LEFT JOIN transcript t 
    ON c.Course_Code = t.Course_Code and t.CWID = "C38475920";

Conditions in the where clause are mandatory, so the way you phrased the query actually turned the left join to an inner join: if a curriculum had no transcript, the where clause evicts the corresponding row.

score 1 · Answer 2 · answered Apr 01 '20 at 21:00

The where condition is applied after the tables are joined and the rows returned. If you want to join on a subset of the rows, you could have the condition in the on clause:

SELECT DISTINCT curriculum.Course_Code, transcript.Course_Grade, transcript.Course_Comp, transcript.CWID 
FROM curriculum 
LEFT JOIN transcript 
ON curriculum.Course_Code = transcript.Course_Code AND transcript.CWID = "C38475920";

SQL LEFT JOIN with WHERE statement after is not displaying unconnected rows

2 Answers2