26

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
SuperStormer
  • 4,997
  • 5
  • 25
  • 35
Jere
  • 1,196
  • 1
  • 9
  • 31
  • 2
    Hi. Keys are irrelevant to join & intersect/except. Any 2 tables can be joined & intersect/except require certain column type agreement. Learn their definitions. PS See the downvote arrow mouseover text. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. – philipxy Aug 10 '18 at 00:25
  • INTERSECT works like the mathematical [intersection](https://en.wikipedia.org/wiki/Intersection_(set_theory)) from set-theory. – Paul-Sebastian Manole Nov 25 '22 at 08:10
  • @Paul-SebastianManole SQL tables are not sets. – philipxy Nov 27 '22 at 05:15
  • @philipxy, Relational databases and SQL were originally based in part upon the mathematical concept of set theory. A table can be thought of as a mathematical set, where each element of the set is a row. SQL is a set-oriented language. – Paul-Sebastian Manole Dec 11 '22 at 15:17
  • @Paul-SebastianManole SQL is not faithfully relational, and its tables hold bags of rows, not sets of rows, ie they can have multiple copies of the same element. Bag operations are reminiscent of but different from set operations--necessarily, since bags are not sets. (This happens to lead to two variants--all & distinct--of each SQL set-reminiscent operator--union, intersect & except/minus. Neither of which are set operators--necessarily, since bags are not sets.) "Like" does not explain & without details of how it is "like" & unlike is misleading & unhelpful. Helpful is a correct definition. – philipxy Dec 11 '22 at 21:58
  • @philipxy, A bag is just a multiset, another type of set. If you want, yes, you could say that SQL tables are just lists of rows, nothing more, but SQL **is** a **set-oriented** language. The rows being unique or not, that's just a constraint that **can be** expressed in SQL/RDBMS'es. Let's not argue about terminology and think objectively. SQL is based upon set theory, and is an extension of it. Depending on what constraints have been defined, it can perform exactly like the math theory says or offer more freedom. – Paul-Sebastian Manole Dec 18 '22 at 23:11
  • A multiset/bag is not a set. This issue is not terminology, it is what things are, and they are not what you say. I am done. – philipxy Dec 19 '22 at 07:01

2 Answers2

55

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
12

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
OfirD
  • 9,442
  • 5
  • 47
  • 90
APH
  • 4,109
  • 1
  • 25
  • 36