RIGHT JOIN - right outer join

Syntax

    FROM OUTER Table1 [Alias1], Table2 [Alias2]
    WHERE a.Field1 = b.Field2

    Informix, before SQL92 (implicit join declaration)

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

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

Description

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

    Beispiel:

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

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

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

Result:

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

List of all customers with corresponding salespeople, but only when they live in the same city.

Categories: