Compare column in two tables

Question

Compare column in two tables

Minnu

SSCertifiable

Points: 6451
More actions
April 20, 2015 at 9:45 am

#297087

Hi Team,
i have two tables.
Table A
IdName
101Dante
102Henry
103Harold
104Arnold
Table B
NumberName
102Dante
107Gilbert
109Harold
110Arnold
106Susan
112Marian
I want the result in table 3 like below, if value exists in Table A and not exists in Table B then the record should enter in table 3 with table name in new column, and vice versa.
Table C
Col1Col2
HenryTable A
Gilbert Table B
Susan Table B
Marian Table B
using below logic to get the values from tables..
select
t1.columnA
, t2.*
from
table1 t1
join table2 t2 on t2.columnB = t1.columnA
Please help

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Your explanation is kind of all over the place. You reference columns in your sample query that don't exist in your sample data. The description is difficult to follow however I think you just need to change your join to a left join and then use ISNULL or COALESCE for the table column in your output.

Something along these lines.

select t1.columnA

, case when t2.Col2 is null then 'Table A' else 'Table B' end as Col2

from table1 t1

LEFT join table2 t2 on t2.columnB = t1.columnA

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

I was thinking....

DECLARE @tableA TABLE (id int, name varchar(20));

DECLARE @tableB TABLE (number int, name varchar(20));

INSERT @tableA VALUES

(101,'Dante'),

(102,'Henry'),

(103,'Harold'),

(104,'Arnold');

INSERT @tableB VALUES

(102,'Dante'),

(107,'Gilbert'),

(109,'Harold'),

(110,'Arnold'),

(106,'Susan'),

(112,'Marian');

WITH names AS

(

SELECT a = a.name, b = b.name

FROM @tableA a

FULL JOIN @tableB b

ON a.name = b.name

WHERE a.name IS NULL OR b.name IS NULL

)

SELECT col1 = ISNULL(a,b), col2 = CASE WHEN a IS NULL THEN 'table B' ELSE 'table A' END

FROM names;

Edit: Sean beat me to it. His solution is better.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

Was stuck trying to do without cte/subquery... Using my sample data above:

SELECT

Col1 = ISNULL(a.name,b.name),

Col2 = CASE ISNULL(a.name,'') WHEN '' THEN 'Table B' ELSE 'Table A' END

FROM @tableA a

FULL JOIN @tableB b

ON a.name = b.name

WHERE a.name IS NULL OR b.name IS NULL;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Y.B. SSChampion Points: 11642 More actions · Answer 4

Alan's answer works and is nice and clean. I figured I'd add my take since I was already looking into it. Often there are more than a few ways to skin the proverbial cat. 😉

DECLARE @tableA TABLE (id int, name varchar(20));

DECLARE @tableB TABLE (number int, name varchar(20));

INSERT INTO @tableA (id, name)

SELECT 101,'Dante'

UNION ALL

SELECT 102,'Henry'

UNION ALL

SELECT 103,'Harold'

UNION ALL

SELECT 104,'Arnold';

INSERT INTO @tableB (number, name)

SELECT 102,'Dante'

UNION ALL

SELECT 107,'Gilbert'

UNION ALL

SELECT 109,'Harold'

UNION ALL

SELECT 110,'Arnold'

UNION ALL

SELECT 106,'Susan'

UNION ALL

SELECT 112,'Marian';

SELECT [name] AS Col1, 'TableA' AS Col2 FROM @tableA

EXCEPT

SELECT [name] AS Col1, 'TableA' AS Col2 FROM @tableB

UNION ALL

SELECT [name] AS Col1, 'TableB' AS Col2 FROM @tableB

EXCEPT

SELECT [name] AS Col1, 'TableB' AS Col2 FROM @tableA

SELECT quote FROM brain WHERE original = 1
0 rows returned

Alan Burstein SSC Guru Points: 61136 More actions · Answer 5

. I figured I'd add my take since I was already looking into it. Often there are more than a few ways to skin the proverbial cat. ;-)

I think it's a good idea not to let good work go to waste. It's common that on SSC I'll be working on a solution only to find that someone beat me to it. Your solution is good because not a lot of people out there use or know about EXCEPT.

On a different note: this is one of those cases where you want to get your clustered index correct. A clustered index on ID will be pretty much worthless. making name, id the clustered index however, will remove two needless DISTINCT sorts from the query plan for yb751's solution.

--How index helps

IF OBJECT_ID('tempdb..#tableA') IS NOT NULL DROP TABLE tempdb..#tableA;

IF OBJECT_ID('tempdb..#tableB') IS NOT NULL DROP TABLE tempdb..#tableB;

GO

-- (5) let's try this against a clustered index, not a heap.

CREATE TABLE #tableA(id int, name varchar(20) NOT NULL, CONSTRAINT pk_a primary key(id));

CREATE TABLE #tableB(id int, name varchar(20) NOT NULL, CONSTRAINT pk_b primary key(id));

INSERT #tableA VALUES

(101,'Dante'),

(102,'Henry'),

(103,'Harold'),

(104,'Arnold');

INSERT #tableB VALUES

(102,'Dante'),

(107,'Gilbert'),

(109,'Harold'),

(110,'Arnold'),

(106,'Susan'),

(112,'Marian');

--yb751's solution

SELECT [name] AS Col1, 'TableA' AS Col2 FROM #tableA

EXCEPT

SELECT [name] AS Col1, 'TableA' AS Col2 FROM #tableB

UNION ALL

SELECT [name] AS Col1, 'TableB' AS Col2 FROM #tableB

EXCEPT

SELECT [name] AS Col1, 'TableB' AS Col2 FROM #tableA;

-- alan's solution

SELECT

Col1 = ISNULL(a.name,b.name),

Col2 = CASE ISNULL(a.name,'') WHEN '' THEN 'Table B' ELSE 'Table A' END

FROM #tableA a

FULL JOIN #tableB b

ON a.name = b.name

WHERE a.name IS NULL OR b.name IS NULL;

GO

-- Primary key on a meaningless surrogate key is bad...

-- Let's create a more meaningful clustered index

ALTER TABLE #tableA DROP CONSTRAINT pk_a;

ALTER TABLE #tableB DROP CONSTRAINT pk_b;

GO

ALTER TABLE #tableA ADD CONSTRAINT pk_a PRIMARY KEY(name, id);

ALTER TABLE #tableB ADD CONSTRAINT pk_b PRIMARY KEY(name, id);

GO

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

-- MUCH BETTER!

SELECT [name] AS Col1, 'TableA' AS Col2 FROM #tableA

EXCEPT

SELECT [name] AS Col1, 'TableA' AS Col2 FROM #tableB

UNION ALL

SELECT [name] AS Col1, 'TableB' AS Col2 FROM #tableB

EXCEPT

SELECT [name] AS Col1, 'TableB' AS Col2 FROM #tableA;

-- ALSO MUCH BETTER

SELECT

Col1 = ISNULL(a.name,b.name),

Col2 = CASE ISNULL(a.name,'') WHEN '' THEN 'Table B' ELSE 'Table A' END

FROM #tableA a

FULL JOIN #tableB b

ON a.name = b.name

WHERE a.name IS NULL OR b.name IS NULL;

GO

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Dwain Camps SSC Guru Points: 86908 More actions · Answer 6

Interesting solutions there gentleman, but regardless of how you set up your PRIMARY KEY/INDEXing, might this not be just a tad simpler?

SELECT name,

=MAX(

)

FROM

(

SELECT name,

='TableA'

FROM #tableA

UNION ALL

SELECT name, 'TableB'

FROM #tableB

) a

GROUP BY name

HAVING COUNT(*) = 1;

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Minnu SSCertifiable Points: 6451 More actions · Answer 7

Hi Team,

The solution provided by are working fine..

but there is a change in my result.

my request is

if a record_1 is in Table_A and Table_B, then

output : record_id_1 : Table_A & Table_B

If record_2 is only in table B then

output: record_id_2 : Table_B

If record_3 is only in table_A then

output:record_id_2 : Table_A

like above i want for both the tables.

Please help.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 8

Minnu (4/21/2015)
Hi Team,
The solution provided by are working fine..
but there is a change in my result.
my request is
if a record_1 is in Table_A and Table_B, then
output : record_id_1 : Table_A & Table_B
If record_2 is only in table B then
output: record_id_2 : Table_B
If record_3 is only in table_A then
output:record_id_2 : Table_A
like above i want for both the tables.
Please help.

With a little tinkering you could make slight changes to any of the 3 solutions above to get that... For example, using my solution you would just do this:

SELECT

Col1 = ISNULL(a.name,b.name),

Col2 =

CASE

WHEN ISNULL(a.name,'') = '' THEN 'Table B'

WHEN ISNULL(b.name,'') = '' THEN 'Table A'

ELSE 'Table A & Table B'

END

FROM #tableA a

FULL JOIN #tableB b

ON a.name = b.name;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 9

Alan.B (4/21/2015)
Minnu (4/21/2015)
Hi Team,
The solution provided by are working fine..
but there is a change in my result.
my request is
if a record_1 is in Table_A and Table_B, then
output : record_id_1 : Table_A & Table_B
If record_2 is only in table B then
output: record_id_2 : Table_B
If record_3 is only in table_A then
output:record_id_2 : Table_A
like above i want for both the tables.
Please help.
With a little tinkering you could make slight changes to any of the 3 solutions above to get that... For example, using my solution you would just do this:
SELECT
Col1 = ISNULL(a.name,b.name),
Col2 =
CASE
WHEN ISNULL(a.name,'') = '' THEN 'Table B'
WHEN ISNULL(b.name,'') = '' THEN 'Table A'
ELSE 'Table A & Table B'
END
FROM #tableA a
FULL JOIN #tableB b
ON a.name = b.name;

Why the "WHEN ISNULL(a.name,'') = '' THEN "? Why not "WHEN a.name is null THEN "?

Don Simpson

I'm not sure about Heisenberg.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 10

DonlSimpson (4/22/2015)
Alan.B (4/21/2015)
Minnu (4/21/2015)
Hi Team,
The solution provided by are working fine..
but there is a change in my result.
my request is
if a record_1 is in Table_A and Table_B, then
output : record_id_1 : Table_A & Table_B
If record_2 is only in table B then
output: record_id_2 : Table_B
If record_3 is only in table_A then
output:record_id_2 : Table_A
like above i want for both the tables.
Please help.
With a little tinkering you could make slight changes to any of the 3 solutions above to get that... For example, using my solution you would just do this:
SELECT
Col1 = ISNULL(a.name,b.name),
Col2 =
CASE
WHEN ISNULL(a.name,'') = '' THEN 'Table B'
WHEN ISNULL(b.name,'') = '' THEN 'Table A'
ELSE 'Table A & Table B'
END
FROM #tableA a
FULL JOIN #tableB b
ON a.name = b.name;
Why the "WHEN ISNULL(a.name,'') = '' THEN "? Why not "WHEN a.name is null THEN "?

I had to edit my answer because I misunderstood your question.

Either way works; I was trying different things when I was developing my solution.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61136 More actions · Answer 11

dwain.c (4/20/2015)
Interesting solutions there gentleman, but regardless of how you set up your PRIMARY KEY/INDEXing, might this not be just a tad simpler?
SELECT name,
=MAX(
)
FROM
(
SELECT name,
='TableA'
FROM #tableA
UNION ALL
SELECT name, 'TableB'
FROM #tableB
) a
GROUP BY name
HAVING COUNT(*) = 1;

I did not have time to put together a 1,000,000 row test but this guy:

SELECT

Col1 = ISNULL(a.name,b.name),

CASE ISNULL(a.name,'') WHEN '' THEN 'Table B' ELSE 'Table A' END

FROM #tableA a

FULL JOIN #tableB b

ON a.name = b.name

WHERE a.name IS NULL OR b.name IS NULL;

Is as or more simple than this guy:

SELECT name,

=MAX(

)

FROM

(

SELECT name,

='TableA'

FROM #tableA

UNION ALL

SELECT name, 'TableB'

FROM #tableB

) a

GROUP BY name

HAVING COUNT(*) = 1;

When the primary key is on ID yours requires a sort operator

When the primary key is on (name, id) we get almost identical query plans. Yours being faster apparently (according to the optimizer) due to the less expense Merge Join algorithm.

That's my observation.

Edit: added note about the Merge Join algorithm.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 12

Alan.B (4/22/2015)
I did not have time to put together a 1,000,000 row test but this guy:
SELECT
Col1 = ISNULL(a.name,b.name),
CASE ISNULL(a.name,'') WHEN '' THEN 'Table B' ELSE 'Table A' END
FROM #tableA a
FULL JOIN #tableB b
ON a.name = b.name
WHERE a.name IS NULL OR b.name IS NULL;
Is as or more simple than this guy:
SELECT name,
=MAX(
)
FROM
(
SELECT name,
='TableA'
FROM #tableA
UNION ALL
SELECT name, 'TableB'
FROM #tableB
) a
GROUP BY name
HAVING COUNT(*) = 1;

NULLs make the difference. Consider

DECLARE @tableA TABLE (id int, name varchar(20));

DECLARE @tableB TABLE (number int, name varchar(20));

INSERT @tableA VALUES

(101,'Dante'),

(102,'Henry'),

(103,'Harold'),

(104,'Arnold'),

(111, NULL);

INSERT @tableB VALUES

(102,'Dante'),

(107,'Gilbert'),

(109,'Harold'),

(110,'Arnold'),

(106,'Susan'),

(112,'Marian'),

(999, NULL);

It's up to OP to decide which of two guys is correct (or may be just change DDL ), but certainly results are different.