Jim, since you sent that to the list, do i have a userid also? :) joe
James Howerton wrote: >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... > >>>>[EMAIL PROTECTED] 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: > >1. Natural join: This is a join between 2 or more tables where the >columns names match between the tables, like in our table, the dept_id >column is the same name between the dept_name AND the same datatype. > >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 [EMAIL PROTECTED] > >Joe > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).