# Thread: Today I learnt something new.

1. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445

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

2. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
is there perdormance degradation? vs bar = 937 or bar is null?

3. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
What would be the benefit of using EXCEPT and INTERSECT rather than outer and inner joins?

4. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,860
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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Originally Posted by dav1mo
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.
Originally Posted by blindman
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.
Originally Posted by MCrowley
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

6. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,860
Originally Posted by gvee
You're [almost] right there... null can't equal null
That is why I called it the "odd part".

7. Annie's Dog Walker
Join Date
Nov 2004
Location
on the wrong server
Posts
8,842
you guys are all a bunch of odd parts. Happy Friday.

8. Registered User
Join Date
Jan 2013
Posts
355

## Not really ..

Originally Posted by MCrowley
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
•