Relational Intersect Operator - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Relational Intersect Operator

Author

Topic

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-26 : 06:17:14

It's just for fun!
I'd like to collect all method for implement relational intersect operator in SQL Server 2005.
I read an article from sqlservercentral.com but the article does not payment on it completely.
Instead of here are 8 methods! Please complete the collection.


DECLARE @A TABLE
(
	ID Int Primary Key,
	[Value] NVarChar(25) NOT NULL
)

DECLARE @B TABLE
(
	ID Int Primary Key,
	[Value] NVarChar(25) NOT NULL
)

INSERT INTO @A VALUES (1,'v1')
INSERT INTO @A VALUES (2,'v2')
INSERT INTO @A VALUES (3,'v3')
INSERT INTO @A VALUES (4,'v4')

INSERT INTO @B VALUES (1,'v1')
INSERT INTO @B VALUES (2,'v2')
INSERT INTO @B VALUES (5,'v5')

--------- INTERSECT ---------
SELECT * FROM @A
INTERSECT
SELECT * FROM @B

--------- Two EXCEPT ---------
SELECT * FROM @A
EXCEPT
(
	SELECT * FROM @A
	EXCEPT
	SELECT * FROM @B
)

--------- INNER JOIN ----------
SELECT a.* FROM @A a
INNER JOIN @B b ON a.id=b.id

---------- EXISTS -------------
SELECT * FROM @A a
WHERE  EXISTS (SELECT * FROM @B b
		WHERE a.id=b.id)

------------ IN --------------
SELECT * FROM @A a
WHERE a.id IN (SELECT b.id FROM @B b)

-------- RIGHT JOIN -----------
SELECT a.*
FROM @A a
RIGHT OUTER JOIN @B b ON a.id=b.id
WHERE a.id IS NOT NULL

-- OR

SELECT a.*
FROM @A a
RIGHT OUTER JOIN @B b ON a.id=b.id
EXCEPT SELECT NULL, NULL

--------  = ANY -----------
SELECT * FROM @A a
WHERE a.id =ANY (SELECT b.id FROM @B b)


--------  UNION ALL -----------
select id
from
(
		select id from @a		
		group by id
		union all
		select id from @b		
		group by id
)d
group by d.id
having count(id)=2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-26 : 06:24:50

Have fun!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129157

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources