SQL join 用法全集 inner join、full out join、left join、right join

一、两表交集，内连接 INNER JOIN

select a.column_name , b.column_name
from table1 a
INNER JOIN table2 b
ON
a.column_name = b.column_name

二、左连接 LEFT JOIN

select a.column_name , b.column_name
from table1 a
LEFT JOIN table2 b
ON a.column_name=b.column_name

三、右连接 RIGHT JOIN

select a.column_name , b.column_name
from table1 a
RIGHT JOIN table2 b
ON a.column_name=b.column_name

四、包含全集 FULL OUTER JOIN语法

select a.column_name , b.column_name
from table1 a
FULL OUTER JOIN table2 b
ON a.column_name=b.column_name

五、左表中右表不包含部份

select a.column_name , b.column_name
from table1 a
LEFT JOIN table2 b
ON a.column_name = b.column_name
where b.column_name is null

六、右表中左表不包含部份

select a.column_name , b.column_name
from table1 a
RIGHT JOIN table2 b
ON a.column_name = b.column_name
where a.column_name is null

七、左右表非交集部份

select * from Table_a
full outer join Table_b
on Table_a.id_a = Table_b.id_b
where Table_a.id_a is null or Table_b.id_b is null

八、笛卡尔积

-- 笛卡尔积
select * from Table_a ,Table_b

笛卡尔积应用例子：
同时显示总计、分组小计、和明细数据的统计查询
使用 笛卡尔积 防止 不同数据类型合并失败，省略进行数据转换的代码。
dbo.s_count 表是只有一个整数型字段（s_count.计数）和一行记录s_count.计数记录为 1 的表。

select   '总计' , sum(iid) , 0 as 计数
from dbo.s_fielstype 


UNION

select   s_fielstype.cvfptype + '小计' , sum(iid) , COUNT(s_count.计数) as 计数
from dbo.s_fielstype , dbo.s_count
group by s_fielstype.cvfptype

UNION

select   s_fielstype.csqltype , iid , s_count.计数 as 计数
from dbo.s_fielstype , dbo.s_count