Is there a fundamental difference between INTERSECT and INNER JOIN?

Question

I understand, that INNER JOIN is made for referenced keys and INTERSECT is not. But afaik in some cases, both of them can do the same thing. So, is there a difference (in performance or anything) between the following two expressions? And if there is, which one is better?

Expression 1:

SELECT id FROM customers 
INNER JOIN orders ON customers.id = orders.customerID;

Expression 2:

SELECT id FROM customers
INTERSECT
SELECT customerID FROM orders

Answer 1

They are very different, even in your case.

The INNER JOIN will return duplicates, if id is duplicated in either table. INTERSECT removes duplicates. The INNER JOIN will never return NULL, but INTERSECT will return NULL.

The two are very different; INNER JOIN is an operator that generally matches on a limited set of columns and can return zero rows or more rows from either table. INTERSECT is a set-based operator that compares complete rows between two sets and can never return more rows than in the smaller table.

Answer 2

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 a
INNER JOIN #b b ON a.id = b.id

SELECT id FROM #a
INTERSECT
SELECT id FROM #b