What's different between INTERSECT and JOIN?

What's different between INTERSECT and JOIN?

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

 

https://www.quora.com/What-is-the-difference-between-inner-join-and-intersect

There are 3 differences

Intersect is an operator and Inner join is a type of join.

Intersect can return matching null values but inner join can't.

Intersect doesn't return any duplicate values but inner join returns duplicate values if it's present in the tables.

Try the following, for example:

CREATE TABLE a (id INT);

CREATE TABLE b (id INT);

INSERT INTO a VALUES (1), (NULL), (2);

INSERT INTO b VALUES (1), (NULL), (3), (1);

SELECT a.id

FROM

a INNER JOIN b

ON a.id

= b.id

;

output

1

1

 

SELECT id FROM a

INTERSECT

SELECT id FROM b;

Output

Null

1

 

作者:Chuck Lu    GitHub