July 31, 2012 at 11:00 pm
i have two tables
CREATE TABLE [dbo].[A](
[id] [int] NULL,
[value] [int] NULL
) ON [PRIMARY]
insert into A(id, value) values(1,1)
insert into A(id, value) values(2,2)
insert into A(id, value) values(3,3)
insert into A(id, value) values(4,4)
id value
1 1
2 2
3 3
4 4
CREATE TABLE [dbo].(
[id] [int] NULL,
[value] [int] NULL
) ON [PRIMARY]
insert into B(id, value) values(2,6)
insert into B(id, value) values(3,7)
insert into B(id, value) values(5,8)
insert into B(id, value) values(6,9)
id value
2 6
3 7
5 8
6 9
I want to get data that has in A but B and has in B but A, without union, like:
id value
1 1
4 4
5 8
6 9
pls, help me. tks
August 1, 2012 at 12:17 am
What's wrong with union?
Since TSQL offers only union, intersect and except, any solution that I can think of right now involves union.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 1, 2012 at 12:25 am
Koen Verbeeck (8/1/2012)
What's wrong with union?Since TSQL offers only union, intersect and except, any solution that I can think of right now involves union.
because performance, the tables A,B have large data so can't access twice.
August 1, 2012 at 12:34 am
If you use UNION ALL the Distinct Sort operator will be removed from the query plan and performance won't be that bad. Are the two SELECT queries just simple selects on the two tables, or are they views or derived tables?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 1, 2012 at 12:59 am
Koen Verbeeck (8/1/2012)
If you use UNION ALL the Distinct Sort operator will be removed from the query plan and performance won't be that bad. Are the two SELECT queries just simple selects on the two tables, or are they views or derived tables?
just selects and i just want to find a better solution before use the UNION 🙂
August 1, 2012 at 1:12 am
SELECT
CASE WHEN a.id IS NULL THEN b.id ELSE a.id END AS id,
CASE WHEN a.value IS NULL THEN b.value ELSE a.value END AS value
FROM (SELECT [id], [value]
FROM A) a
FULL OUTER JOIN (SELECT [id], [value]
FROM B) b ON a.id = b.id
WHERE a.id IS NULL OR b.id IS NULL;
Internally, this is still a UNION.
August 1, 2012 at 1:25 am
Allright, I managed to rewrite the query without using UNION or UNION ALL:
SELECT COALESCE(A.ID,B2.ID), COALESCE(A.Value, B2.Value)
FROM
A
LEFT OUTER JOIN
B B1
ON A.ID = B1.ID
FULL OUTER JOIN
B B2
ON B2.ID = B1.ID
WHERE B1.ID IS NULL
edit: it seems Cadavre was a bit faster with an alternative solution 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 1, 2012 at 1:34 am
Koen Verbeeck (8/1/2012)
Allright, I managed to rewrite the query without using UNION or UNION ALL:
SELECT COALESCE(A.ID,B2.ID), COALESCE(A.Value, B2.Value)
FROM
A
LEFT OUTER JOIN
B B1
ON A.ID = B1.ID
FULL OUTER JOIN
B B2
ON B2.ID = B1.ID
WHERE B1.ID IS NULL
I think you're still looking at an internal UNION there.
Also looks like your extra left join may cause performance issues.
--==Cadavre==--
Table 'A'. Scan count 2, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'B'. Scan count 2, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--==Koen==--
Table 'Worktable'. Scan count 2, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'B'. Scan count 3, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'A'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
August 1, 2012 at 1:47 am
yes, i think when use FULL JOIN then it is a UNION.
because the structure of tables(A&B) not same, i think i will be using FULL JOIN for queries.
August 1, 2012 at 1:49 am
Cadavre (8/1/2012)
I think you're still looking at an internal UNION there.
Well, the code is meant to replace UNION, so that's possible 😀
Cadavre (8/1/2012)
Also looks like your extra left join may cause performance issues.
--==Cadavre==--Table 'A'. Scan count 2, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'B'. Scan count 2, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--==Koen==--
Table 'Worktable'. Scan count 2, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'B'. Scan count 3, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'A'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
I wouldn't be surprised if it did. 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 1, 2012 at 2:10 am
nguyennd (8/1/2012)
Koen Verbeeck (8/1/2012)
What's wrong with union?Since TSQL offers only union, intersect and except, any solution that I can think of right now involves union.
because performance, the tables A,B have large data so can't access twice.
Not sure what you mean here - if you are suggesting that a UNION query accesses each referenced table twice, then this may be where you are coming unstuck.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2012 at 6:33 pm
Another possible solution is to create a temp table ans store data there. hope that helps 🙂
August 1, 2012 at 6:38 pm
Cadavre (8/1/2012)
SELECTCASE WHEN a.id IS NULL THEN b.id ELSE a.id END AS id,
CASE WHEN a.value IS NULL THEN b.value ELSE a.value END AS value
FROM (SELECT [id], [value]
FROM A) a
FULL OUTER JOIN (SELECT [id], [value]
FROM B) b ON a.id = b.id
WHERE a.id IS NULL OR b.id IS NULL;
Internally, this is still a UNION.
Cadavre,
As far as I know while it will still internally operate as a union, it should execution plan with only a single clustered/table/index scan against each table. Did you find otherwise with some test code? If you did I have a bit of testing myself to go check out for some other stuff I built.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply