Right outer joins

SQL Development

OpenEdge SQL Data Manipulation Language : Working with join operations : Using outer joins : Right outer joins

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.

A right outer join retrieves all the rows from the right table even if there are not matches with the left table.

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 DeptNameChristine   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:

OpenEdge SQL has not yet implemented the RIGHT OUTER JOIN expression. Instead, it allows you to achieve the same effect of a right outer join by using the outer join operator (+).

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.