Orace Join Demo

[url]http://blog.chinaunix.net/u/25552/showart_196102.html[/url]

[b]Join Demo Tables [/b]
CREATE TABLE person (
person_id NUMBER(10),
first_name VARCHAR2(25) NOT NULL,
middle_inits VARCHAR2(4),
last_name VARCHAR2(25) NOT NULL,
name_suffix VARCHAR2(5),
title_1 VARCHAR2(5),
title_2 VARCHAR2(5),
dob DATE NOT NULL,
ssn VARCHAR2(11) NOT NULL,
home_phone VARCHAR2(12),
work_phone VARCHAR2(12),
cell_pager VARCHAR2(12),
email_address VARCHAR2(30))
PCTFREE 20;

ALTER TABLE person
ADD CONSTRAINT pk_person
PRIMARY KEY (person_id)
USING INDEX
PCTFREE 5;

CREATE TABLE person_role (
role_id VARCHAR2(1),
role_name VARCHAR2(20) NOT NULL);

ALTER TABLE person_role
ADD CONSTRAINT pk_role
PRIMARY KEY (role_id)
USING INDEX
PCTFREE 5;

CREATE TABLE person_role_ie (
person_role_id NUMBER(10),
person_id NUMBER(10) NOT NULL,
role_id VARCHAR2(1) NOT NULL);

ALTER TABLE person_role_ie
ADD CONSTRAINT pk_person_role_ie
PRIMARY KEY (person_role_id)
USING INDEX
PCTFREE 5;

CREATE TABLE title (
title_abbrev VARCHAR2(5),
title_name VARCHAR2(20))
PCTFREE 0;

ALTER TABLE title
ADD CONSTRAINT pk_title
PRIMARY KEY (title_abbrev)
USING INDEX
PCTFREE 0;

ALTER TABLE person_role_ie
ADD CONSTRAINT fk_person_role_ie_person
FOREIGN KEY (person_id)
REFERENCES person(person_id);

ALTER TABLE person_role_ie
ADD CONSTRAINT fk_person_role_ie_role
FOREIGN KEY (role_id)
REFERENCES person_role(role_id);

ALTER TABLE person
ADD CONSTRAINT fk_person_title1
FOREIGN KEY (title_1)
REFERENCES title(title_abbrev);

ALTER TABLE person
ADD CONSTRAINT fk_person_title2
FOREIGN KEY (title_2)
REFERENCES title(title_abbrev);
[b]Demo Table Data Load [/b]
INSERT INTO title VALUES
('BA', 'Bachelor of Arts');

INSERT INTO title VALUES
('BS', 'Bachelor of Science');

INSERT INTO title VALUES
('MS', 'Master of Arts');

INSERT INTO title VALUES
('PhD', 'Doctor of Philosophy');

INSERT INTO title VALUES
('MD', 'Doctor of Medicine');

INSERT INTO person
(person_id, first_name, last_name, title_1, dob, ssn)
VALUES
(1, 'Daniel', 'Morgan', 'BS', TO_DATE('12-JAN-1950'), '111-22-3456');

INSERT INTO person
(person_id, first_name, last_name, title_1, dob, ssn)
VALUES
(2, 'Jack', 'Cline', 'BA', TO_DATE('12-JAN-1950'), '111-22-3456');

INSERT INTO person
(person_id, first_name, last_name, title_1, dob, ssn)
VALUES
(3, 'Muriel', 'Dance', 'PhD', TO_DATE('12-JAN-1950'), '111-22-3456');

INSERT INTO person
(person_id, first_name, last_name, title_1, dob, ssn)
VALUES
(4, 'Elizabeth', 'Scott', 'MS', TO_DATE('12-JAN-1950'), '111-22-3456');

INSERT INTO person
(person_id, first_name, last_name, title_1, dob, ssn)
VALUES
(5, 'Jacqueline', 'Stough', NULL, TO_DATE('12-JAN-1950'), '111-22-3456');

INSERT INTO person_role VALUES (1, 'Administrator');
INSERT INTO person_role VALUES (2, 'Professor');
INSERT INTO person_role VALUES (3, 'Instructor');
INSERT INTO person_role VALUES (4, 'Employee');
INSERT INTO person_role VALUES (5, 'Student');
INSERT INTO person_role VALUES (9, 'Alumni');

CREATE SEQUENCE seq_pr_id START WITH 1;

INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 1, 2);

INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 1, 9);

INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 2, 3);

INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 1, 5);

INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 3, 1);

INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 3, 9);

INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 4, 4);

INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 5, 5);

INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 5, 9);

COMMIT;

[b]Two Table Inner Join [/b]

SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>
WHERE <alias.column_name> = <alias.column_name>
SELECT p.last_name, t.title_name
FROM person p, title t
WHERE p.title_1 = t.title_abbrev;

[b]Three Table Inner Join [/b]
SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>
WHERE <alias.column_name> = <alias.column_name>
AND <alias.column_name> = <alias.column_name>;
SELECT p.last_name, r.role_name
FROM person p, person_role_ie i, person_role r
WHERE p.person_id = i.person_id
AND i.role_id = r.role_id
ORDER BY p.person_id;

[b]Left Outer Join [/b]
SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>
WHERE <alias.column_name = <alias.column_name>
AND <alias.column_name> = <alias.column_name> (+);
SELECT p.last_name, t.title_name
FROM person p, title t
WHERE p.title_1 = t.title_abbrev(+);

Right Outer Join
SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>
WHERE <alias.column_name> (+) = <alias.column_name>;
SELECT p.last_name, t.title_name
FROM person p, title t
WHERE p.title_1(+) = t.title_abbrev;

[b]Self Join [/b]
SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>, <table_name alias>
WHERE <alias .column_name> = < alias.column_name>
AND <alias .column_name> = <alias.column_name>;
UPDATE person
SET title_2 = 'PhD'
WHERE person_id = 1;
COMMIT;

SELECT p.last_name, t1.title_name, t2.title_name
FROM person p, title t1, title t2
WHERE p.title_1 = t1.title_abbrev
AND p.title_2 = t2.title_abbrev;

[b]ANSI Joins [/b]
[b]Inner Join [/b]
SELECT <column_name>, <column_name>
FROM <table_name alias> INNER JOIN <table_name alias>
ON <alias.column_name> = <alias.column_name>
SELECT p.last_name, t.title_name
FROM person p INNER JOIN title t
ON p.title_1 = t.title_abbrev;
[b]Left Outer Join [/b]
SELECT <column_name>, <column_name>
FROM <table_name alias> LEFT OUTER JOIN <table_name alias>
ON <alias.column_name> = <alias.column_name>
SELECT p.last_name, t.title_name
FROM person p LEFT OUTER JOIN title t
ON p.title_1 = t.title_abbrev;
[b]Right Outer Join [/b]
SELECT <column_name>, <column_name>
FROM <table_name alias> RIGHT OUTER JOIN <table_name alias>
ON <alias.column_name> = <alias.column_name>
SELECT p.last_name, t.title_name
FROM person p RIGHT OUTER JOIN title t
ON p.title_1 = t.title_abbrev;
[b]Full Outer Join[/b]
SELECT <column_name>, <column_name>
FROM <table_name alias> FULL OUTER JOIN <table_name alias>
ON <alias.column_name> = <alias.column_name>
SELECT p.last_name, t.title_name
FROM person p FULL OUTER JOIN title t
ON p.title_1 = t.title_abbrev;
[b]Natural Join[/b]
SELECT <column_name>, <column_name>
FROM <table_name alias> NATURAL JOIN <table_name alias>
CREATE TABLE parents (
person_id NUMBER(5),
adult_name VARCHAR2(20),
comments VARCHAR2(40))
PCTFREE 0;

CREATE TABLE child (
parent_id NUMBER(5),
person_id NUMBER(5),
child_name VARCHAR2(20),
comments VARCHAR2(40))
PCTFREE 0;

INSERT INTO parents VALUES (1, 'Dan', 'So What');
INSERT INTO parents VALUES (2, 'Jack', 'Who Cares');
INSERT INTO child VALUES (1, 2, 'Anne', 'Who Cares');
INSERT INTO child VALUES (1, 1, 'Julia', 'Yeah Right');
INSERT INTO child VALUES (2, 1, 'Marcella', 'So What');
COMMIT;

SELECT adult_name, child_name
FROM parents NATURAL JOIN child;
[b]Self Join [/b]
SELECT <column_name>, <column_name>
FROM <table_name alias> INNER JOIN <table_name alias>
ON <alias.column_name> = < alias.column_name>,
<table_name alias> INNER JOIN <table_name alias>
ON <alias .column_name> = <alias.column_name>;
SELECT p1.last_name, t1.title_name, t2.title_name
FROM person p1 INNER JOIN title t1
ON p1.title_1 = t1.title_abbrev,
person p2 INNER JOIN title t2
ON p2.title_2 = t2.title_abbrev;

[b]Cartesian Join [/b]
Table And Data For Cartesian
Product (Cross-Join) Demo CREATE TABLE cartesian (
join_column NUMBER(10))
TABLESPACE <tablespace_name>;

CREATE TABLE product (
join_column NUMBER(10))
TABLESPACE <tablespace_name>;
Load Demo Tables BEGIN
FOR i in 1..1000
LOOP
INSERT INTO cartesian VALUES (i);
INSERT INTO product VALUES (i);
END LOOP;
COMMIT;
END;
/
[b]Inner Join [/b]
SELECT COUNT(*)
FROM cartesian c, product p
WHERE c.join_column = p.join_column;
Not Inner Join SELECT COUNT(*)
FROM cartesian c, product p
WHERE c.join_column != p.join_column;
Cartesian (Cross-Join) Product SELECT COUNT(*)
FROM cartesian, product;

[b]Join Related Queries [/b]
[b]Column Join Usage[/b]
SET LINESIZE 131

desc col_usage$

SELECT *
FROM col_usage$
WHERE obj# IN (
SELECT object_id
FROM dba_objects
WHERE owner = 'UWCLASS');