Menu
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
934
CROSS JOIN is the full cartesian product of the two sides of a JOIN. INNER JOIN is a reduction of the cartesian product—we specify a predicate and get a result where the predicate matches.
Natural join is a very different operation from intersect . It seems in both queries you are only selecting the columns that exist, with the same name, in both tables. (Otherwise comparing natural join with intersect wouldn't make sense to begin with.) That is something you should make clear in your problem statement.
It depends on the output you expect. A cross join matches all rows in one table to all rows in another table. An inner join matches on a field or fields. If you have one table with 10 rows and another with 10 rows then the two joins will behave differently.
INTERSECT operator in SQL Server 2005 is used to retrieve the common records from both the left and the right query of the Intersect Operator. INTERSECT operator returns almost same results as INNER JOIN clause many times.
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; one is an operator that generally matches on a limited set of columns and can return zero rows or more rows in either table.
When INNER JOIN is used it gives us duplicate records, but that is not in the case of INTERSECT operator. Example 4: Using INNER JOIN with Distinct. The resultset in this example does not contain any duplicate records as DISTINCT clause is used in SELECT statement.
If there are multiple rows in b that match the id, a semi-join still produces only one row from a. Whereas an inner join will produce one row for each match, so you might get what seem to be duplicate rows from a. So these queries do different things and aren't really comparable.
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.
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; one is an operator that generally matches on a limited set of columns and can return zero rows or more rows in either table. The other is a set-based operator that compares complete rows between two sets and can never return more rows than in the smaller table.
150
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
