Answered by:
Why two queries return different results? 2 inner joins vs intersect

Select distinct p.maker From product as p join pc on p.model=pc.model and pc.speed>=750 join laptop as l on p.model=l.model and l.speed>=750 Select p.maker From product as p join pc on p.model=pc.model and pc.speed>=750 Intersect Select distinct p.maker From product as p join laptop as l on p.model=l.model and l.speed>=750
I thought two inner joins to the third table mean the intersection of the both joins's results, seems I was wrong so what does it do then?
UPDATE: Sorry that I didn't mention it at first but the Intersect query returns 2 rows, the joins query returns 0 rows. I know that Intersect excepts duplicates, in this particular case it returns more rows not less, so that is not an issue.
 Edited by Max Nevermind Thursday, April 04, 2013 11:21 AM
Question
Answers

Basically a JOIN and INTERSECT are entriley two different operations.
Join: Compare rows of left table and right table for a match based on the joining condition and return rows if the condition(s) are TRUE
Intersect: returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand
In your first query, using JOIN, the laptop and pc are joined to the product based on the 'model'. So, if no laptop or pc share the same model, then you don't get any rows back.
In your second query, using INTERSECT, you are intersecting makers of pc with makers of laptop. There is no direct relationship with the pc model and laptop model. So you will get the makers...
Hope this helps..
Krishnakumar S
 Marked as answer by Max Nevermind Thursday, April 04, 2013 12:05 PM
All replies

Both the intersection and joins are two very different things and you can not just simply expects both will give same output.
You can refer the below link for your reference.
sarat chandra sahoo
 Proposed as answer by BalmukundMicrosoft employee, Moderator Thursday, April 04, 2013 11:06 AM
 Unproposed as answer by Max Nevermind Thursday, April 04, 2013 11:15 AM


Both the intersection and joins are two very different things and you can not just simply expects both will give same output.
You can refer the below link for your reference.
sarat chandra sahoo

Try to use UNION instead of INTERSECT.
INTERSECT: It will get matching records from both the result sets
UNION: All The records from both the result sets
bala krishna
Why? 1) Intersect returns the right answer and it's logically correct 2) The question why joins in this particular instance return less rows? It shold be equal or more.
 Edited by Max Nevermind Thursday, April 04, 2013 11:29 AM


There is a script creating the tables of this DB and filling them with data: http://shorttext.com/UoGxeT

Basically a JOIN and INTERSECT are entriley two different operations.
Join: Compare rows of left table and right table for a match based on the joining condition and return rows if the condition(s) are TRUE
Intersect: returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand
In your first query, using JOIN, the laptop and pc are joined to the product based on the 'model'. So, if no laptop or pc share the same model, then you don't get any rows back.
In your second query, using INTERSECT, you are intersecting makers of pc with makers of laptop. There is no direct relationship with the pc model and laptop model. So you will get the makers...
Hope this helps..
Krishnakumar S
 Marked as answer by Max Nevermind Thursday, April 04, 2013 12:05 PM

Your statement about joins is incorrect. Inner joins only include matching rows, so they do not always produce more rows.
Joins are always performed on two tables at a time. Joining three tables product, pc and laptop, is like first joining product with pc to produces an intermediate result, which is then joined with laptop to get the final result.
To clarify, the query
SELECT DISTINCT p.maker FROM product AS p JOIN pc ON p.model=pc.model AND pc.speed>=750 JOIN laptop AS l ON p.model=l.model AND l.speed>=750
is equivalent to
SELECT DISTINCT p.maker FROM ( SELECT * FROM product AS p JOIN pc ON p.model=pc.model AND pc.speed>=750 ) AS tmp JOIN laptop AS l ON tmp.model=l.model AND l.speed>=750
 Edited by mbj79 Thursday, April 04, 2013 12:04 PM Formatting

Use LEFT JOIN Instead of JOIN(INNER JOIN) from 3rd table (LAPTOP). then you will get the exact result set which your expecting
QUERY:
SELECT DISTINCT
P.MAKER
FROM
PRODUCT AS P
JOIN PC ON P.MODEL=PC.MODEL AND PC.SPEED>=750
LEFT JOIN LAPTOP AS L ON P.MODEL=L.MODEL AND L.SPEED>=750
SELECT
P.MAKER
FROM
PRODUCT AS P
JOIN PC ON P.MODEL=PC.MODEL AND PC.SPEED>=750
INTERSECT
SELECT DISTINCT
P.MAKER
FROM
PRODUCT AS P
JOIN LAPTOP AS L ON P.MODEL=L.MODEL AND L.SPEED>=750
Note: When you join Product with PC Table
SELECT DISTINCT
P.MAKER,P.MODEL,PC.MODEL
FROM
PRODUCT AS P
JOIN PC ON P.MODEL=PC.MODEL AND PC.SPEED>=750Result set:
MAKER MODEL MODEL B 1121 1121 A 1233 1233 That means when you join 3 rd table (laptop) these 2 model values(1121 & 1233) are will not match with Laptop table.
Hope this will help you.
CONCLUSION: Use LEFT JOIN while joining laptop table.
Thanks.
bala krishna