mysql表链接

 

1、内链接(取的公共部分)

select * from user1 inner join user2 on user1.id = user2.id

select * from user1 join user2 on user1.id = user2.id

 

2、左外连接(以左表为主)

select * from user1 left join user2 on user1.id = user2.id

select * from user1 left join user2 on user1.id = user2.id where user2.name is null

 

3、右外连接（以右表为主）

select * from user1 right join user2 on user1.id = user2.id

select * from user1 right join user2 on user1.id = user2.id where user1.name is null

4、全连接（full join）

因mysql不支持full join 会报错 所以用到了 union all

例1:

select * from user1 left join user2 on user1.id = user2.id

union all

select * from user1 right join user2 on user1.id = user2.id

例2:

select * from user1 left join user2 on user1.id = user2.id

union all

select * from user1 right join user2 on user1.id = user2.id

where user1.user_name is null or user2.user_name is null

 

5、交叉连接（cross join）笛卡尔积连接 无需on条件 。 表1条数 乘于 表2 条数

select * from user1 cross join user2