You are here

Add new comment

FULL JOIN - two sided outer join

Syntax

    FROM Table1 [Alias1] FULL JOIN Table2 [Alias2] ON {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2
    SQL92 (explicit declaration of a full or union join)

    SELECT Fieldlist FROM Table1 [Alias1] LEFT JOIN Table2 [Alias2] ON {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2 UNION
    SELECT Fieldlist FROM Table1 [Alias1] RIGHT JOIN Table2 [Alias2] ON {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2

    SQL92 (if a direct declaration of a full or union join is not supported by the database system)

    SELECT Fieldlist FROM Table1 [Alias1], OUTER Table2 [Alias2]
    WHERE {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2 UNION
    SELECT Fieldlist FROM OUTER Table1 [Alias1], Table2 [Alias2]
    WHERE {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2

    Informix before SQL92 (implicit join declaration)

    SELECT Fieldlist FROM Table1 [Alias1], Table2 [Alias2]
    WHERE ({Table1|Alias1}.Field1 = {Table2|Alias2}.Field2(+)) UNION
    SELECT Fieldlist FROM Table1 [Alias1], Table2 [Alias2]
    WHERE ({Table1|Alias1}.Field1(+) = {Table2|Alias2}.Field2)

    Oracle before SQL92 (implicit join declaration)

Description

    The full or the full outer join includes all records from both tables in one resulting row, where the specifyed join expression evaluates as true. Additionally all records from the left table, where is no match in the right table, and all records from the right table, where is no match in the left table, are included in the query results.

    Attention, if the database system does not directly support a full join (Syntax 1), all matching records in both tables are selected in the first select statement with the left join and then are selected a second time in the second select statement with the right join. At the and duplicate rows are eliminated with UNION (without ALL, this is importand).

    Example:

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

Ergebnis:

vnum vname       knum kname
                 10   Testcustomer
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 		

List of all salesman (tverkauf) and customers (tkunde), matching salesman and customers are in the same result record, salesman without customers and customers wothout salesman are uncluded without their counterparts.

Categories: