Advantages of INTERSECT over INNER JOIN

  • [font="Comic Sans MS"]These two T-SQL statements return the same results.

    If Microsoft deemed it necessary to add the EXCEPT command, then what are its advantages over an INNER JOIN [/font]

    -- LIST ONLY PRODUCTS THAT ARE ON A WORK ORDER

    [font="Courier New"]USE AdventureWorks2008R2;

    GO

    SELECT ProductID

    FROM Production.Product

    INTERSECT

    SELECT ProductID

    FROM Production.WorkOrder ;

    USE AdventureWorks2008R2;

    GO

    SELECT DISTINCT Production.WorkOrder.ProductID

    FROM Production.Product

    INNER JOIN Production.WorkOrder ON Production.WorkOrder.ProductID = Production.Product.ProductID[/font]

  • j-1064772 (1/13/2014)


    [font="Comic Sans MS"]These two T-SQL statements return the same results.

    If Microsoft deemed it necessary to add the EXCEPT command, then what are its advantages over an INNER JOIN [/font]

    -- LIST ONLY PRODUCTS THAT ARE ON A WORK ORDER

    [font="Courier New"]USE AdventureWorks2008R2;

    GO

    SELECT ProductID

    FROM Production.Product

    INTERSECT

    SELECT ProductID

    FROM Production.WorkOrder ;

    USE AdventureWorks2008R2;

    GO

    SELECT DISTINCT Production.WorkOrder.ProductID

    FROM Production.Product

    INNER JOIN Production.WorkOrder ON Production.WorkOrder.ProductID = Production.Product.ProductID[/font]

    INTERSECT and INNER JOIN are similar but NOT the same. In your actual question you said EXCEPT instead of INTERSECT which is what I assume you meant.

    Here is a decent explanation.

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

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • You are right, I really meant INTERSECT only.

    EXCEPT was a mistake. (Also, [font="Courier New"]SELECT ProductID FROM Product WHERE NOT ProductID IN (SELECT ProductID FROM WorkOrder)[/font] also does yield the same results as EXCEPT).

    Thanks for you suggested link.

    Regards

  • j-1064772 (1/13/2014)


    You are right, I really meant INTERSECT only.

    EXCEPT was a mistake. (Also, [font="Courier New"]SELECT ProductID FROM Product WHERE NOT ProductID IN (SELECT ProductID FROM WorkOrder)[/font] also does yield the same results as EXCEPT).

    Thanks for you suggested link.

    Regards

    I find it easier to read using EXCEPT. Also, using NOT IN will return an empty result set if the subquery contains a NULL.

    Try these two and see what I mean.

    SELECT ProductID

    FROM Product

    WHERE ProductID NOT IN

    (

    SELECT ProductID FROM WorkOrder

    UNION ALL

    SELECT NULL

    )

    SELECT ProductID

    FROM Product

    EXCEPT

    (

    SELECT ProductID FROM WorkOrder

    UNION ALL

    SELECT NULL

    )

    The differences are subtle but very important to understand.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Now THIS was the exact answer I was looking for.

    The article you referred to did not really go much beyond what I started with.

    Thanks a million for drawing my attention to the issue of a NULL - I had not seen it coming.

    Super !

  • You are quite welcome. That my explanation provided some insight. 🙂

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • If Microsoft deemed it necessary to add the INTERSECT command, then what are its advantages over an INNER JOIN

    Imagine that instead of selecting one column from each table you are selecting twenty. You CAN achieve similar results by joining on ALL the columns, but that would be a longer, harder to read query. Even not considering the NULL issue Sean described, intersect can be far more convenient and make it much clearer what the query is trying to achieve.

  • Thank you Nevyn

  • j-1064772 (1/13/2014)


    Now THIS was the exact answer I was looking for.

    The article you referred to did not really go much beyond what I started with.

    Thanks a million for drawing my attention to the issue of a NULL - I had not seen it coming.

    Super !

    INTERSECT and EXCEPT (and, IIRC, UNION and UNION ALL) all treat NULLs as NULL = NULL, as well.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • [font="Comic Sans MS"]Thank you all for your inputs, I now understand what INTERSECT and EXCEPT bring to the party.

    I am however still trying to understand why the following occurs:[/font]

    [font="Comic Sans MS"]I checked that[/font] [font="Courier New"]NOT IN[/font] [font="Comic Sans MS"]fails (no record returned because of the extra null)[/font] [font="Comic Sans MS"]as opposed to[/font] [font="Courier New"]IN[/font] [font="Comic Sans MS"]which does work.[/font]

    [font="Courier New"]WHERE ProductID IN (1, 2, 3, 4, NULL)[/font] [font="Comic Sans MS"]translates as[/font]

    [font="Courier New"]WHERE ProductID = 1 OR ProductID = 2 OR ProductID = 3 OR ProductID = 4 OR ProductID = NULL[/font]

    [font="Courier New"]WHERE ProductID NOT IN (1, 2, 3, 4, NULL)[/font][font="Comic Sans MS"] translates as [/font]

    [font="Courier New"]WHERE NOT (ProductID = 1 OR ProductID = 2 OR ProductID = 3 OR ProductID = 4 OR ProductID = NULL)[/font]

    [font="Comic Sans MS"]Using Boolean algebra the last expression should yield the same results[/font]

    [font="Courier New"]WHERE (Product != 1) AND (Product != 2) AND (Product != 3) AND (Product != 4) AND (ProductID != NULL)[/font]

    [font="Comic Sans MS"]The last part[/font] [font="Courier New"]AND (ProductID != NULL)[/font] [font="Comic Sans MS"]would explain why no record would be returned since[/font]

    [font="Courier New"]<anything> = NULL[/font] [font="Comic Sans MS"]always returns false.[/font]

    [font="Comic Sans MS"]Assuming of course that this is indeed the way SQL Server evaluates logical expressions.

    If not, then I am still at a loss as to why the [/font] [font="Courier New"]NOT IN[/font] [font="Comic Sans MS"]fails.

    By the way, I enjoyed the crystal-clear way to include a null in a results set without having to actually put one in the table as done by Mr. Lange.[/font]

  • j-1064772 (1/14/2014)


    [font="Comic Sans MS"]Thank you all for your inputs, I now understand what INTERSECT and EXCEPT bring to the party.

    I am however still trying to understand why the following occurs:[/font]

    [font="Comic Sans MS"]I checked that[/font] [font="Courier New"]NOT IN[/font] [font="Comic Sans MS"]fails (no record returned because of the extra null)[/font] [font="Comic Sans MS"]as opposed to[/font] [font="Courier New"]IN[/font] [font="Comic Sans MS"]which does work.[/font]

    [font="Courier New"]WHERE ProductID IN (1, 2, 3, 4, NULL)[/font] [font="Comic Sans MS"]translates as[/font]

    [font="Courier New"]WHERE ProductID = 1 OR ProductID = 2 OR ProductID = 3 OR ProductID = 4 OR ProductID = NULL[/font]

    [font="Courier New"]WHERE ProductID NOT IN (1, 2, 3, 4, NULL)[/font][font="Comic Sans MS"] translates as [/font]

    [font="Courier New"]WHERE NOT (ProductID = 1 OR ProductID = 2 OR ProductID = 3 OR ProductID = 4 OR ProductID = NULL)[/font]

    [font="Comic Sans MS"]Using Boolean algebra the last expression should yield the same results[/font]

    [font="Courier New"]WHERE (Product != 1) AND (Product != 2) AND (Product != 3) AND (Product != 4) AND (ProductID != NULL)[/font]

    [font="Comic Sans MS"]The last part[/font] [font="Courier New"]AND (ProductID != NULL)[/font] [font="Comic Sans MS"]would explain why no record would be returned since[/font]

    [font="Courier New"]<anything> = NULL[/font] [font="Comic Sans MS"]always returns false.[/font]

    [font="Comic Sans MS"]Assuming of course that this is indeed the way SQL Server evaluates logical expressions.

    If not, then I am still at a loss as to why the [/font] [font="Courier New"]NOT IN[/font] [font="Comic Sans MS"]fails.

    By the way, I enjoyed the crystal-clear way to include a null in a results set without having to actually put one in the table as done by Mr. Lange.[/font]

    The NOT IN is not failing, it is just not doing what you think it should. 😉

    That is the problem with NOT IN, if there is a NULL as one of the conditions it will not return any values because of the NULL.

    We can do these checks without a table at all. You can just use some values to test.

    select 'Yes'

    where 1 not in (1, null)

    BTW, I am glad you saw how you use UNION to force a NULL into a result set. Makes testing and such a LOT easier.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • [font="Comic Sans MS"]You are right about the choice of word "failing". It works in its own way, not the according to what I expected.[/font]

    [font="Comic Sans MS"]Still I am curious as to HOW a[/font][font="Courier New"] NOT IN [/font][font="Comic Sans MS"]is interpreted. Is my suggestion of the optimizer using a Boolean equivalent a valid explanation ? [/font]

  • Edit: just realized you already proposed most of the answer.

    Yes, its because the logic of a not in is to check <> AND for each value.

    In the meantime, the IN doesn't really 'work' either. It won't find the nulls, it just doesn't 'break' the other values.

    Try this:

    select 'yes'

    where null in (1,null)

  • [font="Comic Sans MS"]So the optimizer IS actually using the equivalent from Boolean algebra ?[/font]

    As I wrote before:

    [font="Courier New"]WHERE NOT (ProductID = 1 OR ProductID = 2 OR ProductID = 3 OR ProductID = 4 OR ProductID = NULL)[/font]

    [font="Comic Sans MS"]Using Boolean algebra the last expression should yield the same results[/font]

    [font="Courier New"]WHERE (Product != 1) AND (Product != 2) AND (Product != 3) AND (Product != 4) AND (ProductID != NULL)[/font]

  • Yes. How else would it do it?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply