none
Why two queries return different results? 2 inner joins vs intersect

    Question


  • 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.


    Thursday, April 04, 2013 10:35 AM

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
    Thursday, April 04, 2013 12:01 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.

    http://blog.sqlauthority.com/2008/08/03/sql-server-2005-difference-between-intersect-and-inner-join-intersect-vs-inner-join/


    sarat chandra sahoo

    Thursday, April 04, 2013 11:02 AM
  • 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

    Thursday, April 04, 2013 11:08 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.

    http://blog.sqlauthority.com/2008/08/03/sql-server-2005-difference-between-intersect-and-inner-join-intersect-vs-inner-join/


    sarat chandra sahoo

    I've written an update, check it, it's not what this article about
    Thursday, April 04, 2013 11:16 AM
  • 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.


    Thursday, April 04, 2013 11:19 AM
  • Why can't you share some sample data with table scripts. so that we can analyse.

    bala krishna

    Thursday, April 04, 2013 11:39 AM
  • There is a script creating the tables of this DB and filling them with data: http://shorttext.com/UoGxeT

    Thursday, April 04, 2013 11:58 AM
  • 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
    Thursday, April 04, 2013 12:01 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
    Thursday, April 04, 2013 12:03 PM
  • 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>=750

    Result 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

    Thursday, April 04, 2013 1:00 PM