title: How to return null on a left join field if value not exists
body: <p>I have five(5) tables: students, subjects, period_one, period_two and period_three. I'm performing a left join to select values from these five tables:</p>
  
  <p><strong>Students Table (students)</strong></p>
  
  <pre><code>  student_id    | Name
    --------------|-------
    1             |John
    2             |Peter
    3             |Flomo 
  </code></pre>
  
  <p><strong>Subjects Table (subjects)</strong></p>
  
  <pre><code>  subject_id       |SubjectName
    -----------------|-------
    math101          |Mathematics
    eng201           |English
    lang303          |Language Arts
  </code></pre>
  
  <p><strong>Period One Table (period_one)</strong></p>
  
  <pre><code>  id|student_id |subject_id| score
    --------------|----------|-----
    1 |1          | math101  |99
    2 |2          | eng201   |88
    3 |3          | lang303  |77 
  </code></pre>
  
  <p><strong>Period Two Table (period_two)</strong></p>
  
  <pre><code>  id|student_id |subject_id| score
    --------------|----------|-----
    1 |1          | math101  |100
    2 |2          | eng201   |60
    3 |3          | lang303  |65 
  </code></pre>
  
  <p><strong>Period Three Table (period_three)</strong></p>
  
  <pre><code>  id|student_id |subject_id| score
    --------------|----------|-----
    1 |1          | math101  |71
    2 |2          | eng201   |51
    3 |3          | lang303  |71
  </code></pre>
  
  <p>Here is the query I'm using to retrieve records <strong>Query1</strong>:</p>
  
  <pre><code>SELECT period_one.student_id, period_one.subject_id, period_one.score, period_two.score,period_three.score
  
  from period_one
  
  LEFT JOIN period_two
  ON period_one.subject_id = period_two.subject_id
  AND period_one.student_id = period_two.student_id
  
  LEFT JOIN period_three 
  on period_one.subject_id = period_three.subject_id
  AND period_one.student_id = period_three.student_id
  
  WHERE period_one.student_id = 10      
  </code></pre>
  
  <p>The problem with the code above is if the student id I'm looking for is not in the very first table(periodOne) the left join is being apply to the query returns null even if records of that student are in the others tables(periodTwo and periodThree). I look the issue up(<a href="https://www.w3schools.com/sql/sql_join_left.asp" rel="nofollow noreferrer">https://www.w3schools.com/sql/sql_join_left.asp</a>) and verified that is was not the best way to do things.</p>
  
  <p><strong>I THEN MADE SOME CHANGES</strong></p>
  
  <p>So, I updated my query to look like this (<strong>Query2</strong>):</p>
  
  <pre><code>SELECT students.student_id, period_one.score, period_one.subject_id, 
  period_two.score, period_two.subject_id, period_three.score, 
  period_three.subject_id
  from students
  
  LEFT JOIN period_one
  ON students.student_id = period_one.student_id
  
  LEFT JOIN period_two
  ON students.student_id = period_two.student_id
  
  LEFT JOIN period_three 
  ON students.student_id = period_three.student_id
  </code></pre>
  
  <p>WHERE students.student_id = 3 OR period_one.student_id = 3 OR period_two.student_id = 3 OR period_three.student_id = 3</p>
  
  <p>This works perfectly and since the students table is the main table which all the period tables <strong>are referencing</strong>. With this if a student id is not within the period_one and period_two table but period_there, the studentId, subjectId, and score is return for that table.</p>
  
  <p><strong>THEN ANOTHER PROBLEM POPS OUT</strong></p>
  
  <p>Before I updated my code I was displaying records a bit the way I wanted it, but wasn't getting/retrieving records the desire way. That was what prompted me to change my query, because I noticed it was the issue.</p>
  
  <p>Now, based on my first query(<strong>Query1</strong>) I was selecting the subject_id from the various tables in the select statement. When I'm displaying records I passed that subject_id return from the query into a function that gets the subject name for that id. This is how I was displaying my results:</p>
  
  <p><a href="https://i.stack.imgur.com/taHaU.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/taHaU.png" alt="enter image description here"></a></p>
  
  <p>This works if the student id is within the table assign to the from clause, otherwise it returns nothing. That was why I change my code.</p>
  
  <p>But now that I have change my code to (<strong>Query2</strong>) I'm unable to display the subject id and its name because it is not within the students table. Here is a gist on how I'm displaying my records: <a href="https://gist.github.com/nathansiafa/e9d22791800d4ba3a00e2b98de52baec" rel="nofollow noreferrer">https://gist.github.com/nathansiafa/e9d22791800d4ba3a00e2b98de52baec</a></p>
  
  <p>Is there a way which I can make it work better? Will appreciate suggestions as well as feed backs. Thanks!</p>