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.