mysql&oracle中的各种join连接用法（整理）

1. 各种join用法
   例表：
   
   1.1 inner join（内连接）
   当用两张表进行查询时，只保留两张表中完全匹配的记录。
   例：

SELECT p.LastName, p.FirstName, o.OrderNoFROM Persons pINNER JOIN Orders oON p.Id_P=o.Id_PORDER BY p.LastName

结果：

笛卡尔乘积：select * from a,b 若a表有5条数据，b表有3条数据，则笛卡尔乘积有15条数据

但是如果对两个表进行关联:select * from a,b where a.id = b.id 意思就变了，此时就等价于：
select * from a inner join b on a.id = b.id。即就是内连接。
但是这种写法并不符合规范，可能只对某些数据库管用，如sqlserver。推荐最好不要这样写。最好写成inner join的写法。
1.2 left join（左连接）
用两张表查询时，会返回左表所有的行，即使在右表没有匹配的记录。
例：

SELECT p.LastName, p.FirstName, o.OrderNoFROM Persons pLEFT JOIN Orders oON p.Id_P=o.Id_PORDER BY p.LastName

结果：

左表(Person表)中Id_P为2的记录在右表中没有，但它还是会被查询出来。

1.3 right join（右连接）
两张表查询时，会返回右表所有的行，即使在左表中没有匹配的记录。
例：

SELECT p.LastName, p.FirstName, o.OrderNoFROM Persons pRIGHT JOIN Orders oON p.Id_P=o.Id_PORDER BY p.LastName

结果：

右表中Id_P为65的记录在左表中没有，但是它还是会被查询出来。
1.4 Full join（全连接）
Mysql不支持全连接。
查询两表时，会返回左表和右表中没有匹配的行（也就是所有记录都会返回）
例：

SELECT p.LastName, p.FirstName, o.OrderNoFROM Persons pFULL JOIN Orders oON p.Id_P=o.Id_PORDER BY p.LastName

结果：

结果也就是left join 和right join的并集

1. 使用连接时条件加在on和where后面的区别
   例表：
   address表：
   
   test_on_position表：
   
   2.1 Left join
   条件在where后面：

SELECT*FROMtest_on_positionLEFT JOIN address ON test_on_position.address = address.idWHEREtest_on_position.address IS NOT NULL

结果：

条件在on后面：；

SELECT*FROMtest_on_positionLEFT JOIN address ON test_on_position.address = address.id AND test_on_position.address IS NOT NULL

结果：

分析：条件在on后面使用left join时，即使不符合on后面加的条件也会返回左表中的记录；条件在where后面时，只有满足条件的记录才会被返回。右连接同理。
2.2 inner join
条件在on后面：

SELECT*FROMtest_on_positionINNER JOIN address ON test_on_position.address = address.id AND test_on_position.address IS NOT NULL

结果:

条件在where后面：

SELECT*FROMtest_on_positionINNER JOIN address ON test_on_position.address = address.idWHEREtest_on_position.address IS NOT NULL

结果：

分析：使用内连接时，不管条件加在on后面还是where后面都会返回满足条件的结果。
总结：不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。 而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。