oracle内连接和等值连接,内连接和等值连接区别与联系

一般情况下，等值连接是作为内连结进行处理的。

但内连接并非等同于等值连接,因为还有非等值连接的情况 ^_^

那么正确的结论应该是：内连接 =(等值连接+非等值连接)：

CREATE TABLE PERSONS(

ID NUMBER(10) PRIMARY KEY ,

LASTNAME VARCHAR2(20),

FIRSTNAME VARCHAR2(20),

ADDRESS VARCHAR2(20),

CITY VARCHAR2(20)

);

CREATE TABLE ORDERS(

ID NUMBER(10) PRIMARY KEY,

ORDERNO VARCHAR2(20),

ID_P NUMBER(10)

);

INSERT INTO PERSONS (ID,LASTNAME,FIRSTNAME,ADDRESS,CITY) VALUES(1,'Adams','John','Oxford Street','London');

INSERT INTO PERSONS (ID,LASTNAME,FIRSTNAME,ADDRESS,CITY) VALUES(2,'Bush','George','Fifth Avenue','New York');

INSERT INTO PERSONS (ID,LASTNAME,FIRSTNAME,ADDRESS,CITY) VALUES(3,'Carter','Thomas','Changan Street','Beijing');

INSERT INTO ORDERS (ID,ORDERNO,ID_P) VALUES (1,'77895',3);

INSERT INTO ORDERS (ID,ORDERNO,ID_P) VALUES (2,'44678',3);

INSERT INTO ORDERS (ID,ORDERNO,ID_P) VALUES (3,'22456',1);

INSERT INTO ORDERS (ID,ORDERNO,ID_P) VALUES (4,'24562',1);

INSERT INTO ORDERS (ID,ORDERNO,ID_P) VALUES (5,'34764',65);

COMMIT;

内连接和等值连接比较

1. SELECT P.LASTNAME,P.FIRSTNAME,O.ORDERNO FROM PERSONS P INNER JOIN ORDERS O ON P.ID=O.ID_P;

2. SELECT P.LASTNAME,P.FIRSTNAME,O.ORDERNO FROM PERSONS P , ORDERS O WHERE P.ID=O.ID_P;

数据库： ORACLE

COST 1: 10

COST 2: 10

结论：两者相同

内连接和非等值连接比较

1. SELECT P.LASTNAME,P.FIRSTNAME,O.ORDERNO FROM PERSONS P INNER JOIN ORDERS O ON P.ID！=O.ID_P;

2. SELECT P.LASTNAME,P.FIRSTNAME,O.ORDERNO FROM PERSONS P , ORDERS O WHERE P.ID！=O.ID_P;

数据库： ORACLE

COST 1: 26

COST 2: 26

结论：两者相同