AAM Building the Bridge to Real Content

Syntax

FROM Table1 [Alias1], OUTER Table2 [Alias2]
WHERE a.Field1 = b.Field2
Informix, before SQL92 (implicit declaration of table join)

WHERE (a.Field1 = b.Field2(+))
Oracle, before SQL92 (implicit declaration of table join)

FROM Table1 [Alias1] LEFT JOIN Tabelle2 [Alias2] ON {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2
SQL92 (explicit join declaration)

Description

The result of a left join (or left outer join) includes all data from the left/normal table, data from the right /outer table is included, when the specifyed join expression evaluates as true.

Example:

-- Informix
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a, OUTER tkunden b
WHERE a.vnum = b.vnum
ORDER BY vnum;

-- Oracle
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a, tkunden b
WHERE (a.vnum = b.vnum(+))
ORDER BY vnum;

-- SQL 92
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
ORDER BY vnum;

Result:

vnum vname       knum kname
1    Mueller     1    Lehmann
1    Mueller     2    Schmidt
1    Mueller     3    Schumacher
2    Meyer       4    Schroeder
2    Meyer       5    Stoiber
3    Schulz      6    Lohmann
3    Schulz      7    Krupp
3    Schulz      8    Stradivari
3    Schulz      9    Schumacher
4    Burgmueller 

In addition to a normal or equal or inner join, there are the salesman (values from table tverkauf) with vnum=4 in the result, which has no matching records in the table tkunden.