Left and right joining tables

In the previous lesson we looked at the INNER JOIN and records only are displayed when there is a matching field between the two tables. If you need to display all the records from either table and then show the matches where they exist we can use the LEFT or RIGHT JOINS.

LEFT JOIN

Consider the syntax when using a LEFT JOIN.

SELECT field_name(s)
FROM table1
LEFT JOIN table2
ON table1.field_name=table2.field_name;

The first table listed, table1, is the left table. Using a LEFT JOIN will cause all the records from table1 to be displayed. The records from table2 will be filled in where the ON fields match.

In the example below, all of the records from the student table will be displayed. The field columns from both tables will be listed along the top heading. The student records that do not have a program_id listed will just display no value for those fields from the program table.

SELECT student.last_name, student.first_name, program.name, program.year
FROM student
LEFT JOIN program
ON student.program_id = program.program_id;

RIGHT JOIN

Consider the syntax when using a RIGHT JOIN.

SELECT field_name(s)
FROM table1
RIGHT JOIN table2
ON table1.field_name=table2.field_name;

The second table listed, table2, is the right table. Using a RIGHT JOIN will cause all the records from table2 to be displayed regardless of a matching field between the tables.

In the example below, the matching records from the student table will be displayed. The field columns from both tables will be listed along the top heading. The program records that do not have a matching  program_id will also be listed.

SELECT student.last_name, student.first_name, program.name, program.year
FROM student
RIGHT JOIN program
ON student.program_id = program.program_id;

Lesson Activities

  1. Perform a left join using last_name and first_name from the student table and the the first_name and last_name fields from the faculty table.
    Click to see code
    SELECT student.last_name, student.first_name, faculty.last_name AS "Adviser Last", faculty.first_name AS "Adviser First"
    FROM student
    LEFT JOIN faculty
    ON student.faculty_id = faculty.faculty_id;
  2. Perform a right join using last_name and first_name from the student table and the first_name and last_name fields from the faculty table.
    Click to see code
    SELECT student.last_name, student.first_name, faculty.last_name AS "Adviser Last", faculty.first_name AS "Adviser First"
    FROM student
    RIGHT JOIN faculty
    ON student.faculty_id = faculty.faculty_id;

Questions