{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:
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
conditionin 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
queriesand 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
Re: {9i New Features: Joins}
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: James Howerton
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To
Re[2]: {9i New Features: Joins}
OOPS, looks like a fat finger!!
Reply Separator
Author: James Howerton [EMAIL PROTECTED]
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...
[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: James Howerton
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
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...
[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
