Re: {9i New Features: Joins}

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...
>
>>>>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:
>
>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 9i_at_oracle-dba.com
>
>Joe
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Joe Testa
  INET: jtesta_at_dmc-it.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).