Mega Code Archive
Cartesian Product
<![CDATA[
1>
2> CREATE TABLE employee (emp_no INTEGER NOT NULL,
3> emp_fname CHAR(20) NOT NULL,
4> emp_lname CHAR(20) NOT NULL,
5> dept_no CHAR(4) NULL)
6>
7> insert into employee values(1, 'Matthew', 'Smith', 'd3')
8> insert into employee values(2, 'Ann', 'Jones', 'd3')
9> insert into employee values(3, 'John', 'Barrimore','d1')
10> insert into employee values(4, 'James', 'James', 'd2')
11> insert into employee values(5, 'Elsa', 'Bertoni', 'd2')
12> insert into employee values(6, 'Elke', 'Hansel', 'd2')
13> insert into employee values(7, 'Sybill', 'Moser', 'd1')
14>
15> select * from employee
16> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
emp_no emp_fname emp_lname dept_no
----------- -------------------- -------------------- -------
1 Matthew Smith d3
2 Ann Jones d3
3 John Barrimore d1
4 James James d2
5 Elsa Bertoni d2
6 Elke Hansel d2
7 Sybill Moser d1
(7 rows affected)
1>
2>
3> CREATE TABLE department(dept_no CHAR(4) NOT NULL,
4> dept_name CHAR(25) NOT NULL,
5> location CHAR(30) NULL)
6>
7> insert into department values ('d1', 'developer', 'Dallas')
8> insert into department values ('d2', 'tester', 'Seattle')
9> insert into department values ('d3', 'marketing', 'Dallas')
10>
11> select * from department
12> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
dept_no dept_name location
------- ------------------------- ------------------------------
d1 developer Dallas
d2 tester Seattle
d3 marketing Dallas
(3 rows affected)
1>
2>
3> -- Cartesian Product
4>
5> -- ANSI join syntax:
6>
7> SELECT * FROM employee CROSS JOIN department
8> GO
emp_no emp_fname emp_lname dept_no dept_no dept_name location
----------- -------------------- -------------------- ------- ------- ------------------------- ------------------
1 Matthew Smith d3 d1 developer Dallas
2 Ann Jones d3 d1 developer Dallas
3 John Barrimore d1 d1 developer Dallas
4 James James d2 d1 developer Dallas
5 Elsa Bertoni d2 d1 developer Dallas
6 Elke Hansel d2 d1 developer Dallas
7 Sybill Moser d1 d1 developer Dallas
1 Matthew Smith d3 d2 tester Seattle
2 Ann Jones d3 d2 tester Seattle
3 John Barrimore d1 d2 tester Seattle
4 James James d2 d2 tester Seattle
5 Elsa Bertoni d2 d2 tester Seattle
6 Elke Hansel d2 d2 tester Seattle
7 Sybill Moser d1 d2 tester Seattle
1 Matthew Smith d3 d3 marketing Dallas
2 Ann Jones d3 d3 marketing Dallas
3 John Barrimore d1 d3 marketing Dallas
4 James James d2 d3 marketing Dallas
5 Elsa Bertoni d2 d3 marketing Dallas
6 Elke Hansel d2 d3 marketing Dallas
7 Sybill Moser d1 d3 marketing Dallas
(21 rows affected)
1>
2> drop table employee
3> drop table department
4> GO
1>
]]>