`
myharmony
  • 浏览: 108138 次
  • 性别: Icon_minigender_1
  • 来自: 中山市
社区版块
存档分类
最新评论

Orace Join Demo

阅读更多
http://blog.chinaunix.net/u/25552/showart_196102.html

Join Demo Tables
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);
Demo Table Data Load
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;

Two Table Inner 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; 

Three Table Inner Join
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; 

Left Outer Join
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; 

Self Join
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; 

ANSI Joins
Inner Join
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; 
Left Outer Join
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; 
Right Outer Join
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; 
Full Outer Join 
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; 
Natural Join
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; 
Self Join 
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; 
 
Cartesian Join
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;

Inner Join 
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; 
 
Join Related Queries
Column Join Usage
SET LINESIZE 131

desc col_usage$

SELECT *
FROM col_usage$
WHERE obj# IN (
  SELECT object_id
  FROM dba_objects
  WHERE owner = 'UWCLASS');
分享到:
评论

相关推荐

    orace性能优化高级培训

    orace性能优化

    stampgis连接orace数据库监听配置

    stampgis连接orace数据库监听配置

    Orace Essbase数据仓库函数大全

    Orace Essbase数据仓库函数大全,具有详细的函数说明和使用方法

    ORACE的学习笔记

    - 使用`JOIN`语句连接两个或多个表,如:`SELECT a1.ename, a1.sal, a2.dname FROM emp a1 JOIN dept a2 ON a1.deptno = a2.deptno;` 通过以上解析,我们可以看到这份Oracle学习笔记涵盖了从表的创建、管理到复杂...

    Orace与SQL常用函数对照文档

    ### Orace与SQL常用函数对照知识点详解 #### 一、ASCII 函数 - **功能**:返回与指定的字符相对应的十进制数值。 - **语法**:`ASCII(字符)` - **示例**: - `SELECT ASCII('A') A, ASCII('a') a, ASCII('0') zero...

    orace rac 功能测试

    Oracle RAC,全称为Real Application Clusters,是Oracle数据库的一项高级特性,用于构建高可用性和高性能的数据库集群。Oracle RAC允许多个服务器节点共享同一份物理数据库,从而提供连续的服务和负载均衡。...

    orace 导入导出数据库

    ### Oracle 数据库导入导出详解 #### 一、Oracle 导出(Export)操作 **1.... 在Oracle中,我们可以通过`exp`命令来进行数据库的导出工作。若要将一个名为`TEST`的数据库进行完全导出,可以使用以下命令: ...

    orace 财务操作手册

    ### Oracle EBS 财务模块操作手册关键知识点解析 #### 一、系统应用介绍 **1.1 系统配置和安装** - **配置 Hosts 文件:** - 在 Windows 目录下查找 `Hosts` 文件,通常位于 `C:\WINNT\system32\drivers\etc\...

    Orace数据导出文件版本兼容修改工具

    "Orace数据导出文件版本兼容修改工具"就是为了解决这一特定问题而设计的。 该工具的主要功能是调整DMP文件的内部结构,使其符合目标数据库版本的要求。在Oracle数据库系统中,DMP文件是由`expdp`(数据泵导出)命令...

    orace导入导出的操作

    在Oracle数据库管理中,数据导入导出是至关重要的操作,特别是在数据迁移、备份与恢复、系统复制等场景中。Oracle提供了多种工具来实现这一目的,其中最常用的是`expdp`(Export Data Pump)和`impdp`(Import Data ...

    orace性能优化高级培训.rar

    Oracle性能优化是数据库管理员和开发人员的关键技能,尤其是在大型企业级应用中。Oracle数据库系统以其高效、稳定和可扩展性而著称,但随着数据量的增长和复杂查询的增多,性能问题时常出现。本高级培训将深入探讨...

    ubuntu+python3+orace需要的包

    首先,我们需要理解标题和描述中的“ubuntu+python3+orace需要的包”指的是在Ubuntu操作系统中,使用Python3语言与Oracle数据库进行连接和数据操作所需的基础组件。以下三个文件是关键: 1. **instantclient-...

    ORACE数据库

    ### Oracle数据库核心知识点详解 #### 一、查询与管理数据库对象 **1.... 使用`desctable_name`命令可以快速查看Oracle数据库中任何表的结构,包括字段名称、数据类型以及是否允许为空等信息。 ...

    linuxas4安装orace9i

    ### Linux AS4上安装Oracle 9i的详细步骤与注意事项 #### 一、概述 本文档旨在详述在Red Hat Enterprise Linux AS4操作系统上安装Oracle 9i数据库的过程,包括必要的准备工作、补丁安装以及Oracle软件包的部署。...

    orace 学习 永久有效.txt

    根据提供的文件信息,我们可以推断出这份文档主要涉及Oracle数据库的学习资料。虽然具体的文档内容没有给出,但从标题、描述及部分展示的内容来看,这是一份关于Oracle基础知识的学习指南。接下来,我们将围绕Oracle...

    orace数据仓库

    Oracle数据仓库是一种高效的数据存储和分析系统,专为大规模数据管理和业务智能应用设计。它集成了数据集成、数据清洗、数据存储、数据管理和数据分析等功能,为企业提供了一个统一的平台来处理大量的历史数据,用于...

    Orace PPT 02

    Oracle是全球知名的数据库管理系统提供商,其产品广泛应用于各种规模的企业和组织中。PPT,即PowerPoint,通常用于创建和展示具有丰富图形、文本和多媒体元素的演示文稿。"Oracle PPT 02"可能是指一系列关于Oracle...

Global site tag (gtag.js) - Google Analytics