You are here

Добавить комментарий

CROSS JOIN - kartesian product

Syntax

    SELECT {Table1|Alias1}.Field1 [,...] , {Table2|Alias2}.Field2 [,...]
    FROM Table1 [Alias1], Table2 [Alias2]

    before SQL92 (no join declaration at all)

    FROM Table1 [Alias1] CROSS JOIN Table2 [Alias2]
    SQL92 (explicit declaration of a cross join)

Desciption

    The cross join or the so called kartesian product involves no record match at all, all posible combinations of all records from both tables are included in the query results.

    Example:

-- before SQL 92
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a, tkunden b
ORDER BY vnum;

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

Result:

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