连接查询分类
1.自连接查询，对同一个表进行连接操作
2.内连接查询，【又分为：自然连接（就是等值连接去除重复的行，或者说select 后面选择要表示的列，而不是使用"*",列出所有列）、等值连接（就是where后使用"="来做逻辑判断）、不等值连接三种】【内连接时，返回的结果集仅是符合查询条件和连接条件的行。】
3.外连接查询，【又分为：左外连接、右外连接、全外连接三种】【采用外连接时，它返回到结果集合不仅包含符合连接条件的行，而且还包括的指定外连接中表的所有数据行。】
4.交叉连接查询，也作无条件查询。【返回表的笛卡尔集】
5.联合查询【返回的不是交集而是并集】
示例表
t1
id name sales date
1 los 1500 2011-01-05 00:00:00.000
2 san 250 2011-01-07 00:00:00.0003 los 320 2011-01-08 00:00:00.0004 boston 800 2011-01-08 00:00:00.000t2
id sales date
1 250 2011-01-07 00:00:00.000
2 535 2011-01-10 00:00:00.0003 320 2011-01-11 00:00:00.0004 750 2011-01-12 00:00:00.000多表连接
1，自连接查询
语法：【select 表1.字段名1，表2.字段名2，... from 表1，表2 where 连接条件】
select * from t1 ,t2 where t1.sales=t2.sales
id name sales date id sales date
2 san 250 2011-01-07 00:00:00.000 1 250 2011-01-07 00:00:00.0003 los 320 2011-01-08 00:00:00.000 3 320 2011-01-11 00:00:00.0002.内连接查询
语法：【select 表1.字段名1，表2.字段名2，... FROM 表1 join_type 表2 [ON (连接条件)]】
inner join
等值连接
select * from t1 inner join t2 on t1.sales=t2.sales
id name sales date id sales date
2 san 250 2011-01-07 00:00:00.000 1 250 2011-01-07 00:00:00.0003 los 320 2011-01-08 00:00:00.000 3 320 2011-01-11 00:00:00.000非等值连接
select * from t1 inner join t2 on t1.sales!=t2.sales
id name sales date id sales date
1 los 1500 2011-01-05 00:00:00.000 1 250 2011-01-07 00:00:00.0003 los 320 2011-01-08 00:00:00.000 1 250 2011-01-07 00:00:00.0004 boston 800 2011-01-08 00:00:00.000 1 250 2011-01-07 00:00:00.0001 los 1500 2011-01-05 00:00:00.000 2 535 2011-01-10 00:00:00.0002 san 250 2011-01-07 00:00:00.000 2 535 2011-01-10 00:00:00.0003 los 320 2011-01-08 00:00:00.000 2 535 2011-01-10 00:00:00.0004 boston 800 2011-01-08 00:00:00.000 2 535 2011-01-10 00:00:00.0001 los 1500 2011-01-05 00:00:00.000 3 320 2011-01-11 00:00:00.0002 san 250 2011-01-07 00:00:00.000 3 320 2011-01-11 00:00:00.0004 boston 800 2011-01-08 00:00:00.000 3 320 2011-01-11 00:00:00.0001 los 1500 2011-01-05 00:00:00.000 4 750 2011-01-12 00:00:00.0002 san 250 2011-01-07 00:00:00.000 4 750 2011-01-12 00:00:00.0003 los 320 2011-01-08 00:00:00.000 4 750 2011-01-12 00:00:00.0004 boston 800 2011-01-08 00:00:00.000 4 750 2011-01-12 00:00:00.000自然连接
select t1.sales,t1.date from t1 inner join t2 on t1.sales=t2.sales
sales date
250 2011-01-07 00:00:00.000320 2011-01-08 00:00:00.000left join
select * from t1 left join t2 on t1.sales=t2.sales
id name sales date id sales date
1 los 1500 2011-01-05 00:00:00.000 NULL NULL NULL2 san 250 2011-01-07 00:00:00.000 1 250 2011-01-07 00:00:00.0003 los 320 2011-01-08 00:00:00.000 3 320 2011-01-11 00:00:00.0004 boston 800 2011-01-08 00:00:00.000 NULL NULL NULLright join
select * from t1 right join t2 on t1.sales=t2.sales
id name sales date id sales date
2 san 250 2011-01-07 00:00:00.000 1 250 2011-01-07 00:00:00.000NULL NULL NULL NULL 2 535 2011-01-10 00:00:00.0003 los 320 2011-01-08 00:00:00.000 3 320 2011-01-11 00:00:00.000NULL NULL NULL NULL 4 750 2011-01-12 00:00:00.000full outer join
select * from t1 full outer join t2 on t1.sales=t2.sales
1 los 1500 2011-01-05 00:00:00.000 NULL NULL NULL
2 san 250 2011-01-07 00:00:00.000 1 250 2011-01-07 00:00:00.0003 los 320 2011-01-08 00:00:00.000 3 320 2011-01-11 00:00:00.0004 boston 800 2011-01-08 00:00:00.000 NULL NULL NULLNULL NULL NULL NULL 2 535 2011-01-10 00:00:00.000NULL NULL NULL NULL 4 750 2011-01-12 00:00:00.000cross join
select * from t1 cross join t2
id name sales date id sales date
1 los 1500 2011-01-05 00:00:00.000 1 250 2011-01-07 00:00:00.0002 san 250 2011-01-07 00:00:00.000 1 250 2011-01-07 00:00:00.0003 los 320 2011-01-08 00:00:00.000 1 250 2011-01-07 00:00:00.0004 boston 800 2011-01-08 00:00:00.000 1 250 2011-01-07 00:00:00.0001 los 1500 2011-01-05 00:00:00.000 2 535 2011-01-10 00:00:00.0002 san 250 2011-01-07 00:00:00.000 2 535 2011-01-10 00:00:00.0003 los 320 2011-01-08 00:00:00.000 2 535 2011-01-10 00:00:00.0004 boston 800 2011-01-08 00:00:00.000 2 535 2011-01-10 00:00:00.0001 los 1500 2011-01-05 00:00:00.000 3 320 2011-01-11 00:00:00.0002 san 250 2011-01-07 00:00:00.000 3 320 2011-01-11 00:00:00.0003 los 320 2011-01-08 00:00:00.000 3 320 2011-01-11 00:00:00.0004 boston 800 2011-01-08 00:00:00.000 3 320 2011-01-11 00:00:00.0001 los 1500 2011-01-05 00:00:00.000 4 750 2011-01-12 00:00:00.0002 san 250 2011-01-07 00:00:00.000 4 750 2011-01-12 00:00:00.0003 los 320 2011-01-08 00:00:00.000 4 750 2011-01-12 00:00:00.0004 boston 800 2011-01-08 00:00:00.000 4 750 2011-01-12 00:00:00.000
union 指令表示将多个表合并显示，并不是连接显示。union只是把结果集并集起来，而不是交集出来。
union的限制是多个表的列必须是相同的种类
union 并起来的结果集自动执行distincd，去除重复的列。
union all 则把重复列保留，完整显示多个结果集的并集
下面看实例
执行：select date from t1 union all select date from t2
date
2011-01-05 00:00:00.0002011-01-07 00:00:00.0002011-01-08 00:00:00.0002011-01-08 00:00:00.0002011-01-07 00:00:00.0002011-01-10 00:00:00.0002011-01-11 00:00:00.0002011-01-12 00:00:00.000可以看出完整的现实了8个行
执行：select date from t1 union select date from t2
date
2011-01-05 00:00:00.0002011-01-07 00:00:00.0002011-01-08 00:00:00.0002011-01-10 00:00:00.0002011-01-11 00:00:00.0002011-01-12 00:00:00.000只显示6个行，有重复日期的7，8月份都只保留了一个唯一值
如果执行：select date,sales from t1 union select date,sales from t2
date sales
2011-01-05 00:00:00.000 15002011-01-07 00:00:00.000 2502011-01-08 00:00:00.000 3202011-01-08 00:00:00.000 8002011-01-10 00:00:00.000 5352011-01-11 00:00:00.000 3202011-01-12 00:00:00.000 750可以看出7月份，sales为250的数据有重复，被去除了，只保留了一个
如果执行：select date,sales from t1 union all select date,sales from t2
date sales
2011-01-05 00:00:00.000 1500
2011-01-07 00:00:00.000 2502011-01-08 00:00:00.000 3202011-01-08 00:00:00.000 8002011-01-07 00:00:00.000 2502011-01-10 00:00:00.000 5352011-01-11 00:00:00.000 3202011-01-12 00:00:00.000 750