outer join
a set of operations, called outer joins, were developed for the case where the user wants to keep all the tuples in 
, or all those in 
, or all those in both relations in the result of the JOIN, regardless of whether or not they have matching tuples in the other relation. this satisfies the need of queries in which tuples from two tables are to be combined by matching corresponding rows, but without losing any tuples for lack of matching values. for example, suppose that we want a list of all employee names as well as the name of the departments they manage if they happen to manage a department; if they do not manage one, we can indicate it with a NULL value. we can apply an operation LEFT OUTER JOIN, denoted by 
, to retrieve the result as follows:
the LEFT OUTER JOIN operation keeps every tuple in the first, or left, relation in 
; if no matching tuple is found in , then the attributes of in the join result are filled or padded with NULL values.
a similar operation, RIGHT OUTER JOIN, denoted by , keeps every tuple in the second, or right, relation in the result of 
. A third operation, FULL OUTER JOIN, denoted by 
, keeps all tuples in both the left and the right relations when no matching tuples are found, padding them with NULL values as needed. the three outer join operations are part of the SQL2 standard. these operations were provided later as an extension of relational algebra in response to the typical need in business applications to show related information from multiple tables exhaustively. sometimes a complete reporting of data from multiple tables is required whether or not there are matching values.
[cite:@elmasri_db_2015 chapter 8.4.4 outer join operations]
, or all those in , or all those in both relations in the result of the JOIN, regardless of whether or not they have matching tuples in the other relation. this satisfies the need of queries in which tuples from two tables are to be combined by matching corresponding rows, but without losing any tuples for lack of matching values. for example, suppose that we want a list of all employee names as well as the name of the departments they manage if they happen to manage a department; if they do not manage one, we can indicate it with a NULL value. we can apply an operation LEFT OUTER JOIN, denoted by , to retrieve the result as follows:
the LEFT OUTER JOIN operation keeps every tuple in the first, or left, relation in ; if no matching tuple is found in , then the attributes of in the join result are filled or padded with NULL values.
a similar operation, RIGHT OUTER JOIN, denoted by , keeps every tuple in the second, or right, relation
in the result of . A third operation, FULL OUTER JOIN, denoted by , keeps all tuples in both the left and the right relations when no matching tuples are found, padding them with NULL values as needed. the three outer join operations are part of the SQL2 standard. these operations were provided later as an extension of relational algebra in response to the typical need in business applications to show related information from multiple tables exhaustively. sometimes a complete reporting of data from multiple tables is required whether or not there are matching values.
[cite:@elmasri_db_2015 chapter 8.4.4 outer join operations]