The natural join and cross join operators of SQL-99

Natural Join

A natural join is an equi-join on attributes with the same name
The common attrubute will appear ONCE in the result
(You can't have 2 attributes (columns) withe the same name !!!)

Example:

select * from works_on; ESSN PNO HOURS --------- ---------- ---------- 123456789 1 32.5 123456789 2 7.5 333445555 2 10 333445555 3 10 333445555 10 10 333445555 20 10 999887777 30 30 999887777 10 10 987654321 30 20 987654321 20 15 666884444 3 40 453453453 1 20 453453453 2 20 987987987 30 5 987987987 10 35 888665555 20 1
select * from dependent; ESSN NAME S BDATE RELATIONSH --------- ---------- - ---------- ---------- 333445555 Alice F 05-APR-76 DAUGHTER 333445555 Theodore M 25-OCT-73 SON 333445555 Joy F 03-MAY-48 SPOUSE 987654321 Abner M 29-FEB-32 SPOUSE 123456789 Micheal M 01-JAN-78 SON 123456789 Alice F 31-DEC-78 DAUGHTER 123456789 Elizabeth F 05-MAY-57 SPOUSE
select * from works_on natural join dependent ESSN PNO HOURS NAME S BDATE RELATIONSH --------- ---------- ---------- ---------- - ---------- ---------- 123456789 1 32.5 Elizabeth F 05-MAY-57 SPOUSE 123456789 1 32.5 Alice F 31-DEC-78 DAUGHTER 123456789 1 32.5 Micheal M 01-JAN-78 SON 123456789 2 7.5 Elizabeth F 05-MAY-57 SPOUSE 123456789 2 7.5 Alice F 31-DEC-78 DAUGHTER 123456789 2 7.5 Micheal M 01-JAN-78 SON 333445555 2 10 Joy F 03-MAY-48 SPOUSE 333445555 2 10 Theodore M 25-OCT-73 SON 333445555 2 10 Alice F 05-APR-76 DAUGHTER 333445555 3 10 Joy F 03-MAY-48 SPOUSE 333445555 3 10 Theodore M 25-OCT-73 SON 333445555 3 10 Alice F 05-APR-76 DAUGHTER 333445555 10 10 Joy F 03-MAY-48 SPOUSE 333445555 10 10 Theodore M 25-OCT-73 SON 333445555 10 10 Alice F 05-APR-76 DAUGHTER 333445555 20 10 Joy F 03-MAY-48 SPOUSE 333445555 20 10 Theodore M 25-OCT-73 SON 333445555 20 10 Alice F 05-APR-76 DAUGHTER 987654321 30 20 Abner M 29-FEB-32 SPOUSE 987654321 20 15 Abner M 29-FEB-32 SPOUSE

Notice that the common attribute name ESSN appears only once in the result.

Cross Join

A cross join is the same as a Cartesian Product

Example:

select ssn, fname, lname, dno, dnumber, dname from employee cross join department SSN FNAME LNAME DNO DNUMBER DNAME --------- ---------- ---------- ---------- ---------- --------------- 123456789 John Smith 5 5 Research 333445555 Frank Wong 5 5 Research 999887777 Alice Miller 4 5 Research 987654321 Jack Wallace 4 5 Research 666884444 John Doe 5 5 Research 453453453 Joyce English 5 5 Research 987987987 Jake Jones 4 5 Research 888665555 James Borg 1 5 Research 123456789 John Smith 5 4 Administration 333445555 Frank Wong 5 4 Administration 999887777 Alice Miller 4 4 Administration 987654321 Jack Wallace 4 4 Administration 666884444 John Doe 5 4 Administration 453453453 Joyce English 5 4 Administration 987987987 Jake Jones 4 4 Administration 888665555 James Borg 1 4 Administration 123456789 John Smith 5 1 Headquarters 333445555 Frank Wong 5 1 Headquarters 999887777 Alice Miller 4 1 Headquarters 987654321 Jack Wallace 4 1 Headquarters 666884444 John Doe 5 1 Headquarters 453453453 Joyce English 5 1 Headquarters 987987987 Jake Jones 4 1 Headquarters 888665555 James Borg 1 1 Headquarters