You are here

LEFT JOIN - left outer join

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.

Categories: