Create Table Persons(
Id_P Number,
LastName Varchar2(20),
FirstName Varchar2(20),
Address Varchar2(20),
City Varchar2(20)
)
Create Table Orders(
Id_O Number,OrderNo Varchar2(20),Id_P Number
)
Insert Into Persons Values (1,'Adams','John','Oxford Street','London' );
Insert Into Persons Values (2,'Bush','George','Fifth Avenue','New York' );
Insert Into Persons Values (3,'Carter','Thomas','Changan Street','Beijing' );
Insert Into Orders Values (1,'77895',3 );
Insert Into Orders Values (2,'44678',3 );
Insert Into Orders Values (3,'22456',1 );
Insert Into Orders Values (4,'24562',1 );
Insert Into Orders Values (5,'34764',65 );
Select * From persons;
Select * From orders;
Select * From persons;
Select * From orders;
Select * From persons,orders Where persons.id_p=orders.id_p;--理解相等的原理
Select * From persons Inner Join orders On persons.id_p=orders.id_p;--内连接
Select * From persons Left Outer Join orders On persons.id_p=orders.id_p;--左连接
Select * From persons right Join orders On persons.id_p=orders.id_p;--右连接
Select * From persons Full Join orders On persons.id_p=orders.id_p;--完全连接
•JOIN: 如果表中有至少一个匹配,则返回行 注释:INNER JOIN 与 JOIN 是相同的。
•LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行 注释:在某些数据库中, LEFT JOIN 称为 LEFT OUTER JOIN。
•RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行 注释:在某些数据库中, RIGHT JOIN 称为 RIGHT OUTER JOIN。
•FULL JOIN: 只要其中一个表中存在匹配,就返回行 注释:在某些数据库中, FULL JOIN 称为 FULL OUTER JOIN。