Tuesday, November 13, 2007

INTERCEPT In SQL Server 2005

I was writing a query and managed to mistype INTERSECT, I typed INTERCEPT and to my surprise the query ran, it returned 2 result set just as if INTERCEPT wasn't there at all
Try it yourself

CREATE TABLE testnulls (ID INT)
INSERT INTO testnulls VALUES (1)
INSERT INTO testnulls VALUES (2)
INSERT INTO testnulls VALUES (null)

CREATE TABLE testjoin (ID INT)
INSERT INTO testjoin VALUES (1)
INSERT INTO testjoin VALUES (3)


SELECT * FROM #testjoin
INTERSECT
SELECT * FROM #testnulls


SELECT * FROM #testjoin
INTERCEPT
SELECT * FROM #testnulls


Feature, Bug?

Okay, it actually doesn't matter what you type between the two statements

SELECT * FROM #testjoin
sdsdsdsdsd
SELECT * FROM #testnulls

That runs just as if you executed the query without sdsdsdsdsd
??????

WAKE UP!!! That acts as an alias, now where is my caffeine.

2 comments:

Scott Whigham said...

Ha! When you lay it out that way (in contrast with INTERSECT) it seems wrong - but if you lay it out a different way, it's obvious:

SELECT * FROM TableA intersept
SELECT * FROM TableB

See it? "intersept" is just a table alias...

SQL said...

Yes, I just though of that also and was going to modify the post ;-)

Boy do I feel dumb now...:-(