Re: {9i New Features: Joins}

From: James Howerton <jhowerton_at_uabmc.edu>
Date: Mon, 15 Jul 2002 09:24:22 -0800
Message-ID: <F001.00498177.20020715092422@fatcity.com>

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: 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).

Received on Mon Jul 15 2002 - 12:24:22 CDT