sql server运算符_了解SQL Server中集合理论与集合运算符之间的相互作用

sql server运算符

In this article, we will describe the relation between the Set Theory and SQL Server Set Operations

在本文中，我们将描述集合理论与SQL Server集合操作之间的关系。

集合论导论 (Introduction to the Set Theory)

A set is an exactly defined collection of objects. Objects in this collection are called members or elements of the set. The significant point about the set is that the members should be defined exactly and clearly. Definitions whose members are not defined explicitly, it does not identify a set. For example, months of the winter season specifies a set, but some months of the year do not specify a set. Sets are frequently named with capital letters. The members of the sets can be written between the curly braces and separated by the commas. This representation type called Tabular or Roster Form. In the following, the X set represents months of the winter season:

集合是对象的精确定义的集合。 此集合中的对象称为集合的成员或元素。 关于集合的重要意义在于，应准确，清晰地定义成员。 没有明确定义其成员的定义不标识集合。 例如，冬季的月份指定一个集合，但是一年中的某些月份不指定一个集合。 集通常用大写字母命名。 集合的成员可以写在花括号之间，并用逗号分隔。 这种表示类型称为表格或名册 形式 。 以下， X集代表冬季的月份：

X = {December, January, February}

X = {{12月，1月，2月}

Also, the sets can be represented by the Venn diagrams:

同样，这些集合可以由维恩图表示：

集合论中的通用和空集概念 (Universal and Empty set concept in the Set Theory )

The sets should provide two essential properties:

这些集合应提供两个基本属性：

• A set member can exist once in a set
  集合成员可以在集合中存在一次
• The order of the members of the set is not important
  集合中成员的顺序并不重要

The Universal set is a particular type of the set and the theory assumes that Universal set covers all sets members in the universe and it represented the U letter. On the other hand, if a set does not contain any member, this type of set is named as an Empty set and it is denoted by ∅ or { } sign.

通用集是该集的一种特殊类型，并且该理论假设通用集覆盖了宇宙中的所有集成员，并且它表示U字母。 另一方面，如果集合不包含任何成员，则将此集合类型命名为Empty集 ，并用∅或{}符号表示。

When we apply four operations to the numbers, we are able to obtain new numbers. In a similar context, we can apply some manipulations on sets. In the next sections, we will learn the essential ones and we will also explore the similarities to the SQL Server Set Operations.

当我们对数字应用四个运算时，便能够获得新的数字。 在类似的上下文中，我们可以对集合进行一些操作。 在下一小节中，我们将学习基本知识，还将探索与SQL Server设置操作的相似之处。

集联合 (Union of sets )

According to the Set Theory, we can union two sets so that we can obtain a new set that contains all members of the unioned sets. In the below example, we will define A and B sets and the members of these sets will be fruits:

根据设定的理论 ，我们可以联合两套这样我们就可以得到一组新的包含被联合组的所有成员。 在下面的示例中，我们将定义A和B集，这些集的成员将是水果：

A = {Apple, Orange, Strawbery, Lemon, Avocado}

A = {苹果，橙子，草莓，柠檬，鳄梨}

B = {Lemon, Avocado, Grapefruit, Apricot}

B = {柠檬，鳄梨，葡萄柚，杏子}

The union of the A and B sets will look as follow and it is denoted by A U B:

A和B集的并集如下所示 它由AUB表示：

A U B = {Apple, Orange, Strawbery, Lemon, Avocado, Grapefruit, Apricot}

AUB = {苹果，橙，草莓，柠檬，鳄梨，葡萄柚，杏}

The following Venn diagram illustrates A union B:

以下维恩图说明了联合B ：

At this point, we must emphasize one issue, the common set members are shown only once.

在这一点上，我们必须强调一个问题，公共集合成员仅显示一次。

In the SQL Server, the UNION operator concatenates the resultsets of the two tables and it eliminates the duplicated rows from the resultset. In fact, the UNION operator in SQL Server acts as like as the union operation in the Set Theory. Suppose that, A and B sets are represented by tables in SQL Server. At first, we will create these tables and then add the expressions that similar to A and B set members:

在SQL Server中， UNION运算符将两个表的结果集连接起来，并从结果集中消除重复的行。 实际上，SQL Server中的UNION运算符的作用类似于Set T heory中的联合操作。 假设A和B集由SQL Server中的表表示。 首先，我们将创建这些表，然后添加类似于A和B集成员的表达式：

CREATE TABLE TABLE_A (FruitName VARCHAR(100))
INSERT INTO TABLE_A VALUES ('Apple'),('Orange'),('Strawbery'),('Lemon'),('Avocado')
CREATE TABLE TABLE_B (FruitName VARCHAR(100))
INSERT INTO TABLE_B VALUES ('Lemon'),('Avocado'),('Grapefruit'),('Apricot')

When we union these tables the resultset will be similar to A union B result:

当我们合并这些表时，结果集将类似于A联合B结果：

SELECT * FROM TABLE_A
UNION 
SELECT * FROM TABLE_B

When we change the order of the tables in the query, the result set will not change:

当我们更改查询中表的顺序时，结果集将不会更改：

SELECT * FROM TABLE_B
UNION 
SELECT * FROM TABLE_A

集的交集 (The Intersection of sets )

The intersection of the two sets generates a set that contains the common members of the intersected sets. Now, we will observe the intersection of the A and B sets. The A intersection B is denoted by A ∩ B:

这两个集合的交集生成一个集合，其中包含相交集合的公共成员。 现在，我们将观察A和B集的交集。 A交点B由A∩B表示：

A ∩ B = {Lemon, Avocado}

A∩B = {柠檬，鳄梨}

The result will be Lemon and Avocado; these two members are common members for the A and B sets:

结果将是柠檬和鳄梨； 这两个成员是A和B集的常见成员：

A ∩ B is equal to B ∩ A:

A∩B等于B∩A ：

B ∩ A = {Lemon, Avocado}

B∩A = {柠檬，鳄梨}

The yellow shaded area illustrates the intersection of the A and B sets in the Venn diagram:

黄色阴影区域说明了维恩图中 A和B集的交集：

In SQL Server, the INTERSECT operator implements the intersection logic of the Set Theory to tables. Now, we will find the intersection of the TABLE_A and TABLE_B with help of the following query:

在SQL Server中， INTERSECT运算符将集合理论的交集逻辑实现到表。 现在，我们将在以下查询的帮助下找到TABLE_A和TABLE_B的交集：

SELECT * FROM TABLE_A
INTERSECT
SELECT * FROM TABLE_B

The resultset will not change when we change the order of the tables:

当我们更改表的顺序时，结果集将不会更改：

SELECT * FROM TABLE_B
INTERSECT
SELECT * FROM TABLE_A

套差 (Difference of sets )

The members that are in a set and are not exist in the other are called a set difference in the Set Theory. The result of the A difference B set will be as follows and it is denoted A / B:

集合论中不存在的成员在集合论中称为集合差异。 A差异B集的结果如下，记为A / B ：

A \ B = {Apple, Orange, Strawberry}

A \ B = {苹果，橙，草莓}

The blue shaded area illustrates the result of the A difference B set in the Venn diagram:

蓝色阴影区域表示在维恩图中设置A差B的结果：

A / B is not equal to B / A

A / B不等于B / A

B / A = {Graperfruit , Apricot }

B / A = {葡萄柚，杏子}

The purple shaded area illustrates B difference A in the Venn diagram:

紫色阴影区表示维恩图中的B差A ：

In SQL Server, with the help of the EXCEPT operator, we can obtain the difference between the two tables:

在SQL Server中，借助EXCEPT运算符，我们可以获得两个表之间的差异：

SELECT * FROM TABLE_A
EXCEPT
SELECT * FROM TABLE_B

When we change the order of the table, the result set will be changed:

当我们更改表的顺序时，结果集将被更改：

SELECT * FROM TABLE_B
EXCEPT
SELECT * FROM TABLE_A

结论 (Conclusion)

In this article, while the Set Theory is an abstract concept, we have seen it became a concrete reality in relational database theory.

在这篇文章中，而集 理论是一个抽象的概念，我们已经看到它成为关系数据库理论的一个具体的现实。

“Without mathematics, there’s nothing you can do. Everything around you is mathematics. Everything around you is numbers” – Shakuntala Devi

“ 如果没有数学，您将无能为力。 周围的一切都是数学。 周围的一切都是数字 ” – Shakuntala Devi

翻译自: https://www.sqlshack.com/understanding-the-interaction-between-set-theory-and-set-operators-in-sql-server/

sql server运算符