What's different between INTERSECT and JOIN?

Question

Create data :

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;

What's different between INTERSECT and JOIN?

Answer 1

1. INTERSECT just compares 2 sets and picks only distinct equivalent values from both sets. It's important to note that NULL marks are considered as equals in INTERSECT set operator. Also sets should contain equal number of columns with implicitly convertible types.

2. Under Join i guess you mean INNER JOIN? INNER JOIN will return you rows where matching predicate will return TRUE. I.E. if there are NULL marks in both tables those rows will not be returned because NULL <> NULL in SQL.

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