SELECT w/ INNER JOIN Query on Optional Columns I have five tables: File2, Branches_of_Study, Schools, Education_Establishments, and Certificates. File2 is the transaction table with all the other tables as reference tables.

File2 is all about the educational background of an employee. The employee has to select from a dropdown list in the site the education establishment first, e.g. Highschool, College, etc. Branch of Study refers to the course the employee has taken, e.g. IT, HRM, Photography, etc.

Upon selecting College, the employee must select a Branch of Study. But when selecting Highschool, selecting a branch of study is not allowed.

I have a gridview in the site that shows all of the employee's educational attainment. Here is my query:

SELECT File2.file2_id,
    File2.candidate_id,
    ee.education_establishment,
    s.school,
    File2.school_others,
    File2.start_date,
    File2.end_date,
    bs1.branch_of_study AS branch_of_study_1,
    c.certificate,
    File2.course_appraisal,
    bs2.branch_of_study AS branch_of_study_2
FROM File2
    INNER JOIN Education_Establishments ee
            ON File2.education_establishment_code = ee.education_establishment_code
    INNER JOIN Schools s
            ON File2.school_code = s.school_code
    INNER JOIN Branches_of_Study bs1
            ON File2.branch_of_study_1_code = bs1.branch_of_study_code
    INNER JOIN Branches_of_Study bs2
            ON File2.branch_of_study_2_code = bs2.branch_of_study_code
    INNER JOIN Certificates c
            ON File2.certificate_code = c.certificate_code
 WHERE File2.candidate_id = 100003  


The query returns only results for the College entries for employee number 100003. I assume this is caused by having null values in the Branch of Study fields for Highschool entries.

I want a query that will show all entries (College and Highschool) and at the same time showing the text value (not the code) from the reference tables.
If joining is "optional", then it is not an inner join, by definition.
Consider investigating left joins.