Right outer joins

In a right outer join, the information from the table on the right is preserved. The result table contains all rows from the right table even if some rows do not have matching rows in the left table. Where there are no matching rows in the left table, SQL generates NULL values.

The following example offers an example of a right outer join.

SELECT e.firstname, e.lastname, d.deptcode, d.deptname
FROM employee e, department d
WHERE e.deptcode(+) = d.deptcode AND d.deptcode >= 500
ORDER BY d.deptcode, e.lastname;

This query produces the following results:

FirstName   LastName      DeptCode  DeptName
Christine   Brown         500       Training
Brittney    Burton        500       Training
Larry       Dawsen        500       Training
Sabrina     Raymond       500       Training
Luke        Sanders       500       Training
Harold      Tedford       500       Training
Neil        Watson        500       Training

When working with right outer joins, remember the following:

  • The outer join operator (+) appears on the left side of a right outer join.
  • As you become fluent in SQL, you will notice that you can achieve the result of a right outer join by writing a left outer join and reversing the tables in the FROM and ON clauses. So, why do we have right outer joins? The reason is that right outer joins are a SQL standard with which OpenEdge SQL simply complies.

The following example uses the RIGHT OUTER JOIN phrase in the FROM clause.

SELECT e.firstname,e.EmpNum, e.WorkPhone , d.deptname
FROM employee e RIGHT OUTER JOIN department d
ON e.deptcode = d.deptcode
WHERE e.State = 'MA'
ORDER BY d.deptcode;

This query produces the following results:

FirstName         EmpNum      WorkPhone                  DeptName

-------------- ----------- --------------------- ------------------------------
Marcy                14    617 989-9890                  Administration
Andy                 10    617 288-3333                  Marketing
Mark                 23    617 222-3222                  Marketing
Justine               3    800 787-8484                  Sales
Scott                46    617 298-8298                  Sales
Larry                19    617 892-2822                  Training
Stacey               36    617 309-0903                  Finance