Union 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)

Union Operator

Author

Topic

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-25 : 18:16:05

Hi,
I want to have union of two tables like this without using UNION operator directly and without using my following query!
Please post other method (query).


--My Query
SELECT  ID=ISNULL(a.ID,b.ID),
	  [Value]=ISNULL(a.[value],b.[value])
FROM @A a
FULL OUTER JOIN @B b ON a.id=b.id

/*
ID          Value
----------- -------------------------
1           v1
2           v2
3           v3
4           v4
5           v5
*/
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')

SELECT * FROM @A
/*
ID          Value
----------- --------
1           v1
2           v2
3           v3
4           v4
*/

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

SELECT * FROM @B
/*
ID          Value
----------- --------
1           v1
2           v2
5           v5
*/

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-25 : 19:04:55

Why not use a union?

An infinite universe is the ultimate cartesian product.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-25 : 19:09:09

I do not like UNION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-26 : 05:38:14

I believe ms65g has no problem.
It is just for fun

No, you're never too old to Yak'n'Roll if you're too young to die.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-26 : 09:56:23

Well if you're really against unions you could always create a temp table and dump all your records into it and then use a single select against the temp table.

An infinite universe is the ultimate cartesian product.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-08-26 : 13:40:09

You can always hammer that nail in with a crowbar, too.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-26 : 14:03:56

FYI - your full outer join method is not functionally equivalent to UNION. if your two tables have different [value]s for the same [ID] then Full outer join and UNION will get different results.

Be One with the Optimizer
TG

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-26 : 14:57:18

quote:
Originally posted by TG

FYI - your full outer join method is not functionally equivalent to UNION. if your two tables have different [value]s for the same [ID] then Full outer join and UNION will get different results.

Be One with the Optimizer
TG


SELECT  ID=ISNULL(a.ID,b.ID),
	    [Value]=ISNULL(a.[value],b.[value])
FROM @A a
FULL OUTER JOIN @B b ON a.id=b.id 
AND a.[value]=b.[value]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-26 : 15:27:31

Ok, this would be equivalent only if all non ID columns are NOT NULL. So in your example it works - but if you change [value] to be NULLable in both tables and change say ID=2 values in both tables to NULL then they are no longer equivalent statements.

Be One with the Optimizer
TG

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-26 : 16:14:20

quote:
Originally posted by TG

Ok, this would be equivalent only if all non ID columns are NOT NULL. So in your example it works - but if you change [value] to be NULLable in both tables and change say ID=2 values in both tables to NULL then they are no longer equivalent statements.

Be One with the Optimizer
TG



SELECT  x=ISNULL(a.x,b.x),
	    y=ISNULL(a.y,b.y)
FROM (select x=2, y=null union select x=null, y=null) as a
FULL OUTER JOIN (select x=2, y=null  union select x=2, y=2) as b ON 
(CASE WHEN a.x IS NULL THEN 1 ELSE a.x END)=(CASE WHEN b.x IS NULL THEN 1 ELSE b.x END) 
AND (CASE WHEN a.y IS NULL THEN 1 ELSE a.y END)=(CASE WHEN b.y IS NULL then 1 else b.y end)

----    <=>  ------

(
select x=2, y=null union
select x=null, y=null
) 
union
(
select x=2, y=null union
select x=2, y=2 
)

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-26 : 16:27:17

Instead of 1 in the case statement we must put a constant out of the column data range.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-26 : 16:32:08

Perhaps I misunderstood this little exercise. I thought you were trying to come up with a generic equivalent to a UNION statement. That JOIN criteria may in fact work for those specific tables with that specific data. But that would be impractical to code - especially for tables with lots of columns.

Be One with the Optimizer
TG

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-26 : 16:49:36

You are right completely.
I am sorry for getting your time.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-26 : 16:56:09

quote:
Originally posted by ms65g

You are right completely.
I am sorry for getting your time.

No worries! These are the kind of things that make all of us better developers

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-27 : 02:06:56

quote:
Originally posted by ms65g

I do not like UNION

Is there a specific reason?

Madhivanan

Failing to plan is Planning to fail

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-27 : 02:44:28

quote:
Originally posted by madhivanan

quote:
Originally posted by ms65g

I do not like UNION

Is there a specific reason?

Madhivanan

Failing to plan is Planning to fail

Not at all. I know the value of union operator in SQL!
Only I was looking for a method to simulation it, look like this link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=131772.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources