INTERSECT 和 JOIN 有什么不同? [英] What's different between INTERSECT and JOIN?

问题描述

请帮帮我.

创建数据:

CREATE TABLE sub1(id int,name nvarchar(7));
CREATE TABLE sub2(id int,name nvarchar(7));
INSERT INTO sub1 VALUES(1,'one1');
INSERT INTO sub2 VALUES(1,'one1');
INSERT INTO sub1 VALUES(2,'one2');
INSERT INTO sub2 VALUES(2,'one2');
INSERT INTO sub1 VALUES(3,'one3');
INSERT INTO sub2 VALUES(4,'one4');
INSERT INTO sub1 VALUES(5,'one5');
INSERT INTO sub2 VALUES(6,'one6'); 
INSERT INTO sub1 VALUES(NULL,NULL);
INSERT INTO sub2 VALUES(NULL,NULL);

这两个查询有什么不同:

What's different between these 2 queries :

SELECT * FROM sub1 INTERSECT SELECT * FROM sub2;

SELECT sub1.id,sub1.name FROM sub1 JOIN sub2 ON sub1.id = sub2.id;

INTERSECT 和 JOIN 有什么不同?

What's different between INTERSECT and JOIN ?

推荐答案

1. INTERSECT 仅比较 2 个集合，并仅从两个集合中选取不同的等效值.重要的是要注意 NULL 标记在 INTERSECT 集合运算符中被视为等于.此外，集合应包含相同数量的可隐式转换类型的列.

1. 在Join 下，我猜你是说INNER JOIN?INNER JOIN 将返回匹配谓词将返回 TRUE 的行.IE.如果两个表中都有 NULL 标记，则不会返回这些行，因为 SQL 中的 NULL <> NULL.

此外，INTERSECT 只是比较所有属性的 SETS.它们的类型应该可以相互隐式转换.在 join 中，您可以比较任何谓词和不同类型的集合.不强制只返回匹配的行.例如，您可以在 join 中生成笛卡尔积.

Also INTERSECT is just comparing SETS on all attributes. Their types should be implicitly convertible to each other. While in join you can compare on any predicate and different types of sets. It is not mandatory to return just rows where there are matches. For example you can produce cartesian product in join.

Select * from Table1
Join Table2 on 1 = 1

这篇关于INTERSECT 和 JOIN 有什么不同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！