Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> {9i New Features: Joins}
Welcome to the latest installment of 9i new features. The reason for this one is its the latest thing that I've been studying for the OCP 9i Upgrade exam on this friday :), this will not be all exhaustive but just a sample to make your brain think, RTFM the docs :)
Here is the table scripts to build the data for testing purposes.
drop table dept;
drop table emp;
create table dept
( dept_id varchar2(5) not null,
dept_name varchar2(50) not null);
alter table dept add constraint dept_pk
primary key(dept_id);
create table emp
( emp_id number(5) not null,
emp_name varchar2(50) not null,
dept_id varchar2(5) null);
alter table emp add constraint emp_pk
primary key(emp_id);
insert into dept values ('HR','Catbert'); insert into dept values ('PAY','Payroll'); insert into dept values ('IT','Computer Geeks'); insert into dept values ('MANAG','PHB'); insert into dept values ('EXECU','Big Cheeses'); insert into dept values ('SECRE','Secretary Pool'); insert into dept values ('SEXHR','Known Sexual Harassers'); insert into dept values ('SLIME','Slimy Induhviduals'); insert into dept values ('STRIP','Company Paid Strippers'); insert into dept values ('DUH','No Clue People'); insert into emp values(10,'Bubba Jones','EXECU'); insert into emp values(11,'Honcho Man','EXECU'); insert into emp values(12,'Madam Big','STRIP'); insert into emp values(13,'Hubba Gal','STRIP'); insert into emp values(14,'Ima Dumb','DUH'); insert into emp values(15,'Dont Be','DUH'); insert into emp values(16,'Bosses Aid','SECRE'); insert into emp values(17,'Doy Doofus','MANAG'); insert into emp values(18,'Dom Phuck','SEXHR'); insert into emp values(19,'Look at me','SLIME');insert into emp values(20,'HR Babe','HR'); insert into emp values(21,'Big Mama 36DDD','HUGEB');
Ok now we have some test data, lets look at the various joins.
In the old days(and we're NOT going to talk about sub queries), we really only had equi-joins and a single outer join.
Now we have:
OLD: select emp_id, emp_name, dept_name
from dept, emp where dept.dept_id = emp.dept_id; NEW: select emp_id, emp_name, dept_name from emp natural join dept;
Notice the results we get 11 rows but we have 12 rows in emp. A natural join is an equi-join where you DON'T have to put the join condition in the where clause.
There is a bit more to this one, check the "using" clause also, hint its used if the column names match but maybe the data types don't, etc.
2. Cross join: Your and my favorite, also known as a cartesian join.
OLD: select emp_id, emp_name, dept_name
from dept, emp;
NEW: select emp_id, emp_name, dept_name
from dept cross join emp;
Useful?, I think thats up for debate :)
3. Outer join: This is where you join two tables and want to see all of the rows even if there is NO match. You could outer join to the left or right but not both at the same time. Now you can do left or right outer and even full outer, examples follow:
Left: We want to see all employees even if they dont belong to a dept.
OLD: select dept.dept_id, dept.dept_name, emp.emp_id
from emp, dept where dept.dept_id(+) = emp.dept_id order by emp_id; NEW: select dept.dept_id, dept.dept_name, emp.emp_id from emp left outer join dept on (emp.dept_id = dept.dept_id) order by emp.emp_id;
Right: We want to see all depts even if they dont have employees.
OLD: select dept.dept_id, dept.dept_name, emp.emp_id
from emp, dept where dept.dept_id = emp.dept_id(+) order by emp_id; NEW: select dept.dept_id, dept.dept_name, emp.emp_id from emp right outer join dept on (dept.dept_id = emp.dept_id) order by emp.emp_id;
Full: We want to see all emps with or without being assigned to a dept and all depts with or without employees.
OLD: No such single statement quewry exists, you had to do it via 2 queries and a union statement like this:
select dept.dept_id, dept.dept_name, emp.emp_id from emp, dept where dept.dept_id = emp.dept_id(+) union select dept.dept_id, dept.dept_name, emp.emp_id from emp, dept where dept.dept_id(+) = emp.dept_id; NEW: select dept.dept_id, dept.dept_name, emp.emp_id from emp full outer join dept on (emp.dept_id = dept.dept_id) order by emp.emp_id;
Thats about it for today, all hate email to /dev/null, all good stuff to 9i_at_oracle-dba.com
Joe
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: JOE TESTA INET: JTESTA_at_longaberger.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Jul 15 2002 - 10:48:29 CDT
![]() |
![]() |