SQL Server 2005的并和或的应用
关系型数据库的并(UNION)可以将多个查询语句的结果合并成一个结果。这是开发应用人员所看到的现象的表述。
并是关系代数中的 UNION ,在实际工作应用中 SQL Server 2005的存储过程中用于对数据表的查询:
Code
SELECT ArticleID, ArticleTypeID, MainTitle, SubTitle, ArticleDesc, ArticleContent, Url, Author, Original, AddDate
FROM Article.tb_ArticleDetails
WHERE ArticleTypeID = 1
UNION
SELECT ArticleID, ArticleTypeID, MainTitle, SubTitle, ArticleDesc, ArticleContent, Url, Author, Original, AddDate
FROM Article.tb_ArticleDetails
WHERE ArticleTypeID = 2
UNION
SELECT ArticleID, ArticleTypeID, MainTitle, SubTitle, ArticleDesc, ArticleContent, Url, Author, Original, AddDate
FROM Article.tb_ArticleDetails
WHERE ArticleTypeID = 3
SELECT ArticleID, ArticleTypeID, MainTitle, SubTitle, ArticleDesc, ArticleContent, Url, Author, Original, AddDate
FROM Article.tb_ArticleDetails
WHERE ArticleTypeID = 1
UNION
SELECT ArticleID, ArticleTypeID, MainTitle, SubTitle, ArticleDesc, ArticleContent, Url, Author, Original, AddDate
FROM Article.tb_ArticleDetails
WHERE ArticleTypeID = 2
UNION
SELECT ArticleID, ArticleTypeID, MainTitle, SubTitle, ArticleDesc, ArticleContent, Url, Author, Original, AddDate
FROM Article.tb_ArticleDetails
WHERE ArticleTypeID = 3
上面的代码中 Article.tb_ArticleDetails 是:架构名.数据表名。
在 WHERE 语句中用 OR 结果等价:
Code
SELECT ArticleID, ArticleTypeID, MainTitle, SubTitle, ArticleDesc, ArticleContent, Url, Author, Original, AddDate
FROM Article.tb_ArticleDetails
WHERE ArticleTypeID = 1 OR ArticleTypeID = 2 OR ArticleTypeID = 3
SELECT ArticleID, ArticleTypeID, MainTitle, SubTitle, ArticleDesc, ArticleContent, Url, Author, Original, AddDate
FROM Article.tb_ArticleDetails
WHERE ArticleTypeID = 1 OR ArticleTypeID = 2 OR ArticleTypeID = 3
UNION 是取交积,而除它之外的 UNION ALL 是取并积,没有等价的 WHERE ... OR ...。