Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Today I learnt something new.

    This really wrinkled my brain at first.
    Code:
    CREATE TABLE #foo (
       bar int
    );
    
    INSERT INTO #foo (bar)
      VALUES (937)
           , (NUlL)
    ;
    
    DECLARE @hmm int = NULL;
    
    SELECT bar
    FROM   #foo
    WHERE  EXISTS (
             SELECT bar
             INTERSECT
             SELECT @hmm
           );
    
    SET @hmm = 937;
    
    SELECT bar
    FROM   #foo
    WHERE  EXISTS (
             SELECT bar
             INTERSECT
             SELECT @hmm
           );
    
    DROP TABLE #foo;
    I have never come across this technique before and thought it prime for sharing.

    George
    Home | Blog

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    is there perdormance degradation? vs bar = 937 or bar is null?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What would be the benefit of using EXCEPT and INTERSECT rather than outer and inner joins?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I am not sure, but I think the odd part is that UNION, INTERSECT, and maybe EXCEPT consider null to be equal to null.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by dav1mo View Post
    is there perdormance degradation? vs bar = 937 or bar is null?
    Very quick [unreliable] tests the other day showed a little increase in performance. Need to do more work to prove it but just haven't had the time.
    Quote Originally Posted by blindman View Post
    What would be the benefit of using EXCEPT and INTERSECT rather than outer and inner joins?
    Not sure what you're getting at Blindman? The benefit of this method is being able to pass a NULL to a parameter and return results where the result is NULL.
    Quote Originally Posted by MCrowley View Post
    I am not sure, but I think the odd part is that UNION, INTERSECT, and maybe EXCEPT consider null to be equal to null.
    You're [almost] right there... null can't equal null
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Quote Originally Posted by gvee
    You're [almost] right there... null can't equal null
    That is why I called it the "odd part".

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you guys are all a bunch of odd parts. Happy Friday.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Not really ..

    Quote Originally Posted by MCrowley View Post
    I am not sure, but I think the odd part is that UNION, INTERSECT, and maybe EXCEPT consider null to be equal to null.
    SQL has two equivalence relations; equality (=) and grouping (GROUP BY, etc). The set operations are based on grouping.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •