Full outer joining tables

The INNER join only displays matching records. The LEFT JOIN displays all the records from just the left table and the RIGHT JOIN displays all the records from just the right table. If you wish to display the all records from both tables regardless of a matching field we use a FULL OUTER JOIN.

So basically the FULL OUTER JOIN combines the results of a LEFT JOIN and RIGHT JOIN. Below is the syntax for a FULL OUTER JOIN.

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

Using the example from the other JOIN lessons, below is the code for FULL OUTER JOIN on the student and program tables. This will show all the records from both the student and program tables and will combine record information where there is a match.

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

FULL JOIN on MySQL

The MySQL database system does not recognize the FULL OUTER JOIN statement. As mentioned earlier a FULL OUTER JOIN is really just an LEFT JOIN and RIGHT JOIN combined. We can use the UNION command to manually perform this task.

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

Lesson Activites

  1. Perform a full join that combines the first_name and last_name fields from the student table and the first_name and last_name fields from the faculty table.
    Click to see code
    SELECT student.first_name, student.last_name, faculty.first_name, faculty.last_name
    FROM student
    LEFT JOIN faculty
    ON student.faculty_id = faculty.faculty_id
    UNION
    SELECT student.first_name, student.last_name, faculty.first_name, faculty.last_name
    FROM student
    RIGHT JOIN faculty
    ON student.faculty_id = faculty.faculty_id;
  2. Perform the same join in the previous lesson but show duplicate results by using UNION ALL.
    Click to see code
    SELECT student.first_name, student.last_name, faculty.first_name, faculty.last_name
    FROM student
    LEFT JOIN faculty
    ON student.faculty_id = faculty.faculty_id
    UNION ALL
    SELECT student.first_name, student.last_name, faculty.first_name, faculty.last_name
    FROM student
    RIGHT JOIN faculty
    ON student.faculty_id = faculty.faculty_id;
  3. Perform a full join that combines the first_name and last_name fields from the student table and the name from the program table.
    Click to see code
    SELECT student.first_name, student.last_name, program.program_name
    FROM student
    LEFT JOIN program
    ON student.program_id = program.program_id
    UNION
    SELECT student.first_name, student.last_name, program.program_name
    FROM student
    RIGHT JOIN program
    ON student.program_id = program.program_id;

Questions