**有两个表 employees \departments
15:51:11 SQL> desc departments
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
DEPARTMENT_ID                             NOT NULL NUMBER(4)
DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
MANAGER_ID                                         NUMBER(6)
LOCATION_ID                                        NUMBER(4)

15:51:22 SQL> desc employees
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID                               NOT NULL NUMBER(6)
FIRST_NAME                                         VARCHAR2(20)
LAST_NAME                                 NOT NULL VARCHAR2(25)
EMAIL                                     NOT NULL VARCHAR2(25)
PHONE_NUMBER                                       VARCHAR2(20)
HIRE_DATE                                 NOT NULL DATE
JOB_ID                                    NOT NULL VARCHAR2(10)
SALARY                                             NUMBER(8,2)
COMMISSION_PCT                                     NUMBER(2,2)
MANAGER_ID                                         NUMBER(6)
DEPARTMENT_ID                                      NUMBER(4)

15:51:29 SQL>
**进行自然连接
16:03:24 SQL> select d.department_name, t.FIRST_NAME
16:03:24   2 from departments d natural join employees t;

DEPARTMENT_NAME                FIRST_NAME
------------------------------ --------------------
Marketing                      Pat
Accounting                     William
Executive                      Neena
Executive                      Lex
IT                             Bruce
IT                             David
IT                             Valli
IT                             Diana
Finance                        Daniel
Finance                        John
Finance                        Ismael

DEPARTMENT_NAME                FIRST_NAME
------------------------------ --------------------
Finance                        Jose Manuel
Finance                        Luis
Purchasing                     Alexander
Purchasing                     Shelli
Purchasing                     Sigal
Purchasing                     Guy
Purchasing                     Karen
Shipping                       Laura
Shipping                       Mozhe
Shipping                       James
Shipping                       TJ

DEPARTMENT_NAME                FIRST_NAME
------------------------------ --------------------
Sales                          Peter
Sales                          David
Sales                          Peter
Sales                          Christopher
Sales                          Nanette
Sales                          Oliver
Shipping                       Nandita
Shipping                       Alexis
Shipping                       Julia
Shipping                       Anthony

32 rows selected.

16:03:26 SQL>
***进行等值连接
SQL> select d.department_name, t.FIRST_NAME
2 from departments d join employees t
3 on d.manager_id=t.manager_id;

DEPARTMENT_NAME                FIRST_NAME
------------------------------ --------------------
Shipping                       Mozhe
IT                             David
Purchasing                     Shelli
Sales                          David
Shipping                       Laura
Shipping                       Alexis
Shipping                       Anthony
Executive                      Gerald
Sales                          Nanette
Finance                        John
Purchasing                     Karen

DEPARTMENT_NAME                FIRST_NAME
------------------------------ --------------------
Executive                      Lex
Shipping                       Julia
IT                             Bruce
Executive                      Alberto
Finance                        Daniel
Marketing                      Pat
Executive                      Adam
Accounting                     William
Sales                          Peter
Executive                      Michael
Purchasing                     Guy

DEPARTMENT_NAME                FIRST_NAME
------------------------------ --------------------
Executive                      Payam
Purchasing                     Alexander
Executive                      Neena
IT                             Diana
Shipping                       James
Executive                      Kevin
Sales                          Christopher
Shipping                       TJ
Executive                      Karen
IT                             Valli
Finance                        Luis

DEPARTMENT_NAME                FIRST_NAME
------------------------------ --------------------
Executive                      Den
Executive                      John
Shipping                       Nandita
Finance                        Ismael
Purchasing                     Sigal
Sales                          Peter
Sales                          Oliver
Finance                        Jose Manuel
Executive                      Shanta
Executive                      Matthew
Executive                      Eleni

44 rows selected.

SQL>
//**进行对比从而总结
**表1和表2只有一个列是完全一样的//** 字段名和数值类型都是完全一样的
1、自然连接一定是等值连接，但等值连接不一定是自然连接。
2、等值连接要求相等的分量，不一定是公共属性；而自然连接要求相等的分量必须是公共属性。
3、等值连接不把重复的属性除去；而自然连接要把重复的属性除去。

ITPub博客

自然连接

最新文章