CODEWITHSUNDEEP
sqlsql-server-2008
Azeem
Azeem

Reputation: 2924

SQL Server Query to find different names in two tables

I have a situation here.

I have two tables:

enter image description here

I need a sql query which will print the Col names which are different in two tables.

For example, in this case the query should print the result as:

enter image description here

The reason is clear that m is present in Table-1 but not present in Table-2. Similar is the case with z which is in Table-2 but not in Table-1.

I am really stcuk here, please help.

The colum names are not case-sensitive.

Thanks.

Upvotes: 1

Views: 409

Answers (6)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

You can also use FULL OUTER JOIN operator. Visual Representation of SQL Joins

SELECT ROW_NUMBER() OVER(ORDER BY COALESCE(t1.Col1, t2.Col1)) AS id,       
       COALESCE(t1.Col1, t2.Col1) AS ResultCol
FROM Table1 t1 FULL JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t1.Col1 IS NULL OR t2.Col1 IS NULL

See example on SQLFiddle

Upvotes: 0

joezen777
joezen777

Reputation: 446

There's a feature specifically for this operation. EXCEPT and INTERCEPT.

Find which values (single column result or multi-column result) are not present in the following queries

--What's in table A that isn't in table B

SELECT col1 FROM TableA
EXCEPT
SELECT col1 FROM TableB

--What's in table B that isn't in table A

SELECT col1 FROM TableB
EXCEPT
SELECT col1 FROM TableA

Likewise, the INTERCEPT keyword tells you what is shared

--What's in table A and table B

SELECT col1 FROM TableA
INTERCEPT
SELECT col1 FROM TableB

Upvotes: 0

Sushant Srivastava
Sushant Srivastava

Reputation: 761

declare @tab1 table(id int,col1 varchar(1))
declare @tab2 table(id int,col1 varchar(1))
INSERT INTO @tab1
    ([id], [Col1])
VALUES
    (1, 'A'),
    (2, 'B'),
    (3, 'm'),
    (4, 'c')

INSERT INTO @tab2
    ([id], [Col1])
VALUES
    (1, 'A'),
    (2, 'B'),
    (3, 'C'),
    (4, 'z')





select b.id,b.col1 from
(
select a.id,a.col1,b.col1 x from @tab1 a left join @tab2 b on a.col1 = b.col1
union
select b.id,b.col1,a.col1 x from @tab1 a right join @tab2 b on a.col1 = b.col1
 ) b
where b.x is null

Upvotes: 0

Bijaya Khadka
Bijaya Khadka

Reputation: 159

I Think simplest one is this

SELECT COL1 AS ResultCol FROM TABLE1 where COL1 not in (select COL2 from TABLE2) UNION SELECT COL2 AS ResultCol FROM TABLE2 where COL2 not in (select COL1 from table1)

Upvotes: 0

user359040
user359040

Reputation:

Try:

select coalesce(t1.Col1, t2.Col1)
from [Table-1] t1
full outer join [Table-2] t2 on t1.Col1 = t2.Col1
where t1.Col1 is null or t2.Col1 is null

SQLFiddle here.

Alternatively:

select Col1 from
(select Col1 from [Table-1] union all select Col1 from [Table-2]) sq
group by Col1 having count(*) = 1

SQLFiddle here.

Upvotes: 0

Taryn
Taryn

Reputation: 247730

You could also use NOT EXISTS to get the result:

select col1
from table1 t1
where not exists (select 1
                  from table2 t2
                  where t1.col1 = t2.col1)
union all
select col1
from table2 t2
where not exists (select 1
                  from table1 t1
                  where t1.col1 = t2.col1);

See SQL Fiddle with Demo

Or even NOT IN:

select col1
from table1 t1
where col1 not in (select col1
                   from table2 t2)
union all
select col1
from table2 t2
where col1 not in (select col1
                   from table1 t1);

See SQL Fiddle with Demo

Upvotes: 1

Related Questions