数据库表:
demo01:id,name,age,occupation
demo02:id,name,age
第一种:自连接查询,eg:
语法:select 表1.字段名,表2.字段名,... from 表1,表2 where 连接条件
select * from demo01,demo02 where demo01.name=demo02.name
id name age occupation id name age
第二种:内连接查询,eg:
语法:select 表1.字段名1,表2.字段名2,... FROM 表1 join_type 表2 ON 连接条件
inner join
等值连接:select * from demo01 inner join demo02 on demo01.name=demo02.name
id name age occupation id name age
非等值连接:select * from demo01 inner join demo02 on demo01.name!=demo02.name
id name age occupation id name age
自然连接:select demo01.name,demo01.age from demo01 inner join demo02 on demo01.name=demo02.name
name age
左连接left join:select * from demo01 left join demo02 on demo01.name=demo02.name
id name age occupation id name age
右连接left join:select * from demo01 right join demo02 on demo01.name=demo02.name
id name age occupation id name age
全连接full out join:select * from demo01 full outer join demo02 on demo01.name=demo02.name
id name age occupation id name age
cross join:select * from demo01 cross join demo02
id name age occupation id name age
第三种:union、union all查询
union :将多个表合并显示,并不是连接显示。结果集并集,不是交集。多个表的列必须是相同的种类,结果集自动执行distincd,去除重复的列。
union all: 保留重复列
执行:select name from demo01 union all select name from demo02
执行:select name from demo01 union select name from demo02
执行:select name,age from demo01 union select name,age from demo02
如果执行:select name,age from demo01 union all select name,age from demo02