在进行数据的关联分析时,主要有以下六种数据模型:
1、左外部:left outer
2、右外部:right outer
3、完全外部:full outer
4、内部:inner
5、左反:left anti
6、右反:right anti
下面以两个数据表为例,分别说明6种关系的sql代码:
t_SaleRecord (销售历史表)
t_CurrentlySale(当月销售记录表)
sql代码
1、左外部:left outer
SELECT DISTINCT t_SaleRecord.ProductCode
FROM t_SaleRecord LEFT JOIN t_CurrentlySale ON t_SaleRecord.ProductCode = t_CurrentlySale.ProductCode
GROUP BY t_SaleRecord.ProductCode;
查询结果:
2、右外部:right outer
SELECT DISTINCT t_SaleRecord.ProductCode
FROM t_SaleRecord RIGHT JOIN t_CurrentlySale ON t_SaleRecord.ProductCode = t_CurrentlySale.ProductCode
GROUP BY t_SaleRecord.ProductCode;
查询结果:
3、完全外部:full outer
也就是两张表的并集,要求两张表的查询结果具有相同的字段个数(不要求两个表具有相同的字段数),并且对应的字段的值要出自同一个值域,即具有相同的数据类型和取值范围。
SELECT ProductCode, SalePrice, Format(SaleDate,"yyyy-m-d h:mm:ss") as SalesDate
FROM t_CurrentlySale
UNION
SELECT ProductCode, SalePrice, Format(SaleDate,"yyyy-m-d h:mm:ss") as SalesDate
FROM t_SaleRecord;
查询结果:
4、内部:inner
SELECT DISTINCT t_SaleRecord.ProductCode
FROM t_SaleRecord, t_CurrentlySale
WHERE t_SaleRecord.ProductCode = t_CurrentlySale.ProductCode;
查询结果:
5、左反:left anti
SELECT DISTINCT t_SaleRecord.ProductCode
FROM t_SaleRecord LEFT JOIN t_CurrentlySale ON t_SaleRecord.ProductCode = t_CurrentlySale.ProductCode
WHERE (((t_CurrentlySale.ProductCode) Is Null));
查询结果:
6、右反:right anti
SELECT DISTINCT t_CurrentlySale.ProductCode
FROM t_SaleRecord RIGHT JOIN t_CurrentlySale ON t_SaleRecord.ProductCode = t_CurrentlySale.ProductCode
WHERE (((t_SaleRecord.ProductCode) Is Null));
查询结果:(为空)