内连接时,返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件或 HAVING 条件)和连接条件的行。而采用外连接时,它返回到查询结果集合中的不仅包含符合连接条件的行,而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。
//表联合,意义不大,会得出庞大的数据集合(6行*9行=54行)
select u.user_id,u.user_name,a.user_auth_id,a.auth_type,a.passwd
from table1 u,table2 a
where a.user_id in (select user_id from table1);
select * from table1,table2;
//等值联合,查询出两表中id为2的用户名和性别
select a.use_name,b.user_sex
from table1 a,table2 b
where a.user_id=b. user_id and user_id=2;
//不等值联合
select a.use_name,b.user_sex
from table1 a,table2 b
where a.user_age>b. user_age;
//外联合,table2中id为1的static_passwd字段和table1联合
select u.user_name,a.static_passwd
from table1 u join table2 a on a.user_id=1;
//左联合,以table1为基准,不存在的值为NULL
select u.user_id,a.static_passwd
from table1 u left outer join table2 a
on a.user_id=u.user_id;
//右联合,以table2为基准,不存在的值为NULL
select u.user_id,a.static_passwd
from table1 u right outer join table2 a
on a.user_id=u.user_id;
//多个inner join 时,执行顺序从左到右,table1为基准表
Select t1.name,t2.age,t3.sex
From table1 t1 inner join table2 t2
On t1.id=t2.id
Inner join table3 t3
On t1.id=t3.id;
//union(两个表中共有多少人重名,除去重复部分)
Select name from table1
Union
Select name from table2;
//union all(两个表中共有多少人重名,包括重复部分)
Select name from table1
Union all
Select name from table2;
//intersect(取交集,重复部分)
Select name from table1
Intersect
Select name from table2;
//minus(存在表1中而不存在表2中的数据)
Select name from table1
Minus
Select name from table2;