What to know about SQL Full Outer Join

Stephanie Warner headshot
Stephanie Warner|May 5, 2020
image (58)
image (58)
    No Data

    FULL OUTER JOIN (or FULL JOIN) renders ALL records when a match exists in either table’s (table 1 or table 2) records. It will also render all rows whether there is matching data from the other table or not. In cases where a table does not have a match from the join, the result set will render a “null” value in the field.

    image5 (3)
    FULL OUTER JOIN Syntax: SELECT table_1.field1, table_1.field2, table_2.field1, table_2.fieldn.... FROM Table_1 FULL OUTER JOIN table_2 ON table_1.matching_field = table2.matching_field;
    FULL OUTER JOIN Example:
    Table 1
    unnamed (25)
    Table 2
    unnamed (26)
    image3 (2)
    Result Set:
    unnamed (27)

    Notice how the result set outputs 'null' results for Kelly Radio's record. If there are rows in the "employees" table that do not have matches in the "employee_address" table or vice versa, the records will still be listed regardless. An example of this may be that Kelly Radio was just hired as a new employee and has a row in the employee's table but has not yet submitted his address information. Therefore, Kelly's address id does not yet have information stored in the employee_address table to populate with a query. However, the record will not be left out regardless of lacking a total match.

    Here are some additional resources to get you going:

    Stephanie Warner headshot
    Stephanie Warner

    Stephanie is a big time math nerd, avid snowboarder, and overall winter lover. She enjoys the torture of running and traveling to work Ragnar Relay races. From shredding the gnar to getting far too invested in murder/crime documentaries, you'll also find her petting all the doggos. She was an employee of PDQ.

    Related articles