Left outer joins

SQL Development

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

Left outer joins

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

The following example depicts a join statement using the outer join operator in the WHERE clause:

SELECT Customer.Custnum, Customer.Name, Order.Ordernum, Order.Orderdate
FROM Customer, Order
WHERE Customer.CustNum = Order.CustNum (+) ;

The query requests information about all the customers and their orders. Even if there is not a corresponding row in the Order table for each row in the Customer table, NULL values are displayed for the Order.Ordernum and Order.Orderdate columns. This query produces the following results:

CustNum    Name         Ordernum   OrderDate1          Lift Tours   6          2006-02-11
1          Lift Tours   1          2006-03-17
1          Lift Tours   36         2006-05-01
1          Lift Tours   79         2006-06-22
. . .
5          Ace Tennis   NULL       NULL
7          Xtreme Surf  NULL       NULL

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

SELECT e.firstname, e.lastname, e.deptcode, d.deptname
FROM employee e LEFT OUTER JOIN department d
ON e.deptcode = d.deptcode
WHERE SUBSTR(e.firstname, 1, 1) = 'J'
ORDER BY d.deptcode, e.lastname;

This query produces the following results:

FirstName LastName DeptCode DeptNameJohn      Burton   200      Administration
Jenny     Morris   200      Administration
Jay       Ahern    300      Marketing
Justine   Smith    400      Sales
Jean      Brady    600      Development
. . .