An outer join combines and displays all rows that match across tables, based on the specified matching criteria (also known as join conditions), plus some or all of the rows that do not match. You can think of an outer join as an augmentation of an inner join: an outer join returns all rows generated by an inner join, plus additional (nonmatching) rows.

The syntax of an outer join is shown below.

Consider how each type of outer join works.

A left outer join retrieves all rows that match across tables, based on the specified matching criteria (join conditions), plus nonmatching rows from the left table (the first table specified in the FROM clause).

Suppose you are using the following PROC SQL left join to combine the two tables One and Two. The join condition is stated in the expression following the ON keyword. The two tables and the three rows of output are shown below:

proc sql;
    select *
       from one
        left join
        two
       on one.x=two.x;

In each row of output, the first two columns correspond to table One (the left table) and the last two columns correspond to table Two (the right table).

Because this is a left join, all rows (both matching and nonmatching) from table One (the left table) are included in the output (the first two columns). Rows from table Two (the right table) are displayed in the output (the last two columns) only if they match a row from table One.

In this example, the second row of output is the only row in which the row from table One matched a row from table Two, based on the matching criteria (join conditions) specified in the ON clause. In the first and third rows of output, the row from table One had no matching row in table Two.

To eliminate one of the duplicate columns (in this case, X) in any outer join, as shown earlier with an inner join, you can modify the SELECT clause to list the specific columns that is displayed. Here, the SELECT clause from the preceding query has been modified to remove the duplicate X column:

proc sql;
    select one.x, a, b
       from one
       left join
       two
       on one.x=two.x;

A right outer join retrieves all rows that match across tables, based on the specified matching criteria (join conditions), plus nonmatching rows from the right table (the second table specified in the FROM clause).

Consider what happens when you use a right join to combine the two tables used in the previous example. The following PROC SQL query uses a right join to combine rows from One and Two, based on the join conditions specified in the ON clause:

proc sql;
    select *
       from one
       right join
       two
       on one.x=two.x;

In each row of output, the first two columns correspond to table One (the left table) and the last two columns correspond to table Two (the right table).

Because this is a right join, all rows (both matching and nonmatching) from table Two (the right table) are included in the output (the last two columns). Rows from table One (the left table) are displayed in the output (the first two columns) only if they match a row from table Two.

In this example, there is only one row in table One that matches a value of X in table Two, and these two matching rows combine to form the first row of output. In the remaining rows of output, there is no match and the columns corresponding to table One are set to missing values.

A full outer join retrieves both matching rows and nonmatching rows from both tables.

Combine the same two tables again, this time using a full join. The PROC SQL query, the tables, and the output are shown below:

proc sql;
   select *
      from one 
      full join
      two
      on one.x=two.x;

Because this is a full join, all rows (both matching and nonmatching) from both tables are included in the output. There is only one match between table One and table Two, so only one row of output displays values in all columns. All remaining rows of output contain only values from table One or table Two. The remaining columns are set to missing values.

Now that you have seen how the three types of outer joins work, consider a realistic situation requiring the use of an outer join.

Suppose you want to list all of an airline's flights that were scheduled for March, along with corresponding delay information (if it exists). Each flight is identified by both a flight date and a flight number. Your output should display the following data: flight date, flight number, destination, and length of delay in minutes.

The data that you need is stored in the two tables shown below. The applicable columns from each table are identified.

Your output should include the columns that are listed above and all of the following rows:

To generate the output that you want, the following PROC SQL query uses a left outer join. Sasuser.Marchflights is specified as the left (first) table.

proc sql outobs=20;
title 'All March Flights';
   select m.date,
          m.flightnumber 
	             label='Flight Number',
          m.destination
          label='Left',
          f.destination
          label='Right',
          delay
          label='Delay in Minutes'
      from sasuser.marchflights as m 
           left join 
           sasuser.flightdelays as f
      on m.date=f.date
         and m.flightnumber=
             f.flightnumber
      order by delay;

Notice the following:

The query output is shown below.

The first 12 rows of output display rows from Sasuser.Marchflights (the left table) that have no matching rows in Sasuser.Flightdelays. Therefore, in these 12 rows, the last 2 columns are set to missing values.