| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re[2]: {9i New Features: Joins}
OOPS, looks like a fat finger!!
____________________Reply Separator____________________ Author: "James Howerton" <jhowerton_at_uabmc.edu> Date: 7/15/2002 9:24 AM
All,
Here is a sample of 9i new features. You all should have a user on DBA9 (moray.hs.uab.edu) to try this out, if not let me know.
...JIM...
>>> JTESTA_at_longaberger.com 7/15/02 10:48:28 AM >>>
Welcome to the latest installment of 9i new features. This will not be
all exhaustive but just a sample.
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 ('DBAS','Database Admins');
insert into dept values ('SLIME','Slimy Induhviduals');
insert into dept values ('NWORK','Always Blame On');
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,'Junior','NWORK');
insert into emp values(13,'Help Desk','NWORK');
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,'Keep em Running','DBAS');
insert into emp values(19,'Look at me','SLIME');
insert into emp values(20,'HR Troop','HR');
insert into emp values(21,'Big Pain','USERS');
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: James Howerton INET: jhowerton_at_uabmc.edu 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: dgoulet_at_vicr.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 - 12:53:22 CDT
 |
 |