If you have not understood what I am going to talk in this blog post by just reading the title, I think you are a normal human like me. Just the other day I received an email from a blog reader with subject line as “Select Unique Data From a Column Exist in Two Different Table”.
After exchanging multiple emails with him, I finally understood his business needs.
For example, he had two tables t1 with data (1,2,3) and t2 with data (1,4,5). In his resultset he wanted to remove the value 1 as it exists in both the tables and wanted to keep the final resultset as 2,3,4,5.
Let me explain you what he really needed with an image.
Now the task was to write a code which will remove the duplicates value from both the table and present the unique values in a single column. Initially I tried to write this query with some complex logic in a single SELECT statement. However, as I had another meeting coming up, I quickly wrote a code where I have combined both the table’s unique data with UNION.
Let me show you my solution.
First, we will create two sample tables.
CREATE TABLE t1 (ID INT); INSERT INTO t1 SELECT 1 UNION SELECT 2 UNION SELECT 3 GO CREATE TABLE t2 (ID INT); INSERT INTO t2 SELECT 1 UNION SELECT 4 UNION SELECT 5 GO SELECT * FROM t1; SELECT * FROM t2 GO
Next, let us see my quick and dirty solution (no way this is optimal solution).
SELECT t1.ID FROM t1 LEFT JOIN t2 ON t1.ID = t2.ID WHERE t2.ID IS NULL UNION SELECT t2.ID FROM t2 LEFT JOIN t1 ON t1.ID = t2.ID WHERE t1.ID IS NULL GO
When I sent the result back to him, he was pretty happy. However, I was personally not happy with my query. I believe there will be an optimal way to write this query which will give us better performance.
Can you think of the a simpler way to write this query?
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
46 Comments. Leave new
select * from t1
union
select * from t2
except
select * from t1
intersect
select * from t2
We can write like this.
SELECT id FROM
(
SELECT id
FROM t1
UNION ALL
SELECT id
FROM t2
) t
GROUP BY ID HAVING COUNT(1) =1
SELECT id FROM
(
SELECT id
FROM t1
UNION ALL
SELECT id
FROM t2
) t
GROUP BY ID HAVING COUNT(1) =1
You don’t need to do any joins by default the union removes duplicates
select id from t1
union
select id from t2
if you want all records from both tables then you use union all
This also probably has the least overhead….
Get all records from both tables then exclude the ones that are in both tables
SELECT id from
(
select id from t1
union all
select id from t2
) Q1
where id not in
(select t1.id from t1
inner join t2
on t1.id = t2.id)
One small change to the last query
SELECT distinct id from
(
select id from t1
union all
select id from t2
) Q1
where id not in
(select t1.id from t1
inner join t2
on t1.id = t2.id)
GO
You need to add distinct. The query runs a lot faster for a larger dataset
Solution 1:
(SELECT ID FROM t1 UNION SELECT ID FROM t2)
EXCEPT
(SELECT ID FROM t1 INTERSECT SELECT ID FROM t2)
GO
Solution 2:
SELECT ID FROM
(SELECT ID, 1 AS i
FROM t1
union
SELECT ID, – 1
FROM t2) T
GROUP BY ID
HAVING sum(i)!=0
GO
SELECT DISTINCT CASE
WHEN t1.ID > t2.ID
THEN t1.ID
WHEN t2.ID > t1.ID
THEN t2.id
END AS id
FROM t1
CROSS JOIN t2
WHERE CASE
WHEN t1.ID > t2.ID
THEN t1.ID
WHEN t2.ID > t1.ID
THEN t2.id
END IS NOT NULL
(
SELECT id FROM t1
EXCEPT
SELECT id FROM t2
)
UNION
(
SELECT id FROM t2
EXCEPT
SELECT id FROM t1
)
Another solution that should work but not sure it offer better performance :
SELECT
ID
from
(
SELECT
t0.ID AS IDt0
,t1.ID AS IDt1
FROM
t0
FULL OUTER JOIN
t1 ON t0.ID = t1.ID
WHERE
t0.ID IS NULL
OR
t1.ID IS NULL
) AS t0
UNPIVOT
(
ID
FOR Piv in (IDt0, IDt1)
) unpiv;
select isnull(t1.id,t2.id) as id
from
#t1 as t1
full join #t2 as t2
ON t1.ID = t2.ID
where
t1.id is null
or t2.ID is null
Hi, Pinal,
This is my suggestion:
SELECT ISNULL(t1.ID, t2.ID) FROM t1
FULL JOIN t2 ON t1.id = t2.id
WHERE t1.ID IS NULL OR t2.ID IS NULL
Greetings.
select isnull(t1.id,t2.id)
from t1 full join t2 on t1.id=t2.id
Below query outputs desired result:-
SELECT ID FROM (
SELECT ID
FROM t1
EXCEPT
SELECT ID
FROM t2
) AS t5
union ALL
SELECt ID FROM (
SELECT ID
FROM t2
EXCEPT
SELECT ID
FROM t1) as t4
SELECT t1.ID
FROM t1
WHERE NOT EXISTS(SELECT 1 FROM t2 WHERE t1.ID=t2.ID)
UNION
SELECT t2.ID
FROM t2
WHERE NOT EXISTS(SELECT 1 FROM t1 WHERE t1.ID=t2.ID)
with tabla1 as
( select 1 as valor union select 2 union select 3)
,
tabla2 as
( select 1 as valor union select 4 union select 5)
(
select valor from tabla1
union
select valor from tabla2
)
except
(
select tabla1.valor from tabla1
join tabla2
on tabla1.valor = tabla2.valor
)
This seems to work too – getting union set, then removing intersection.
select t1.ID from t1
UNION
select t2.ID from t2
EXCEPT
(select t1.ID from t1
INTERSECT
select t2.ID from t2);
(select code from #table1 union select code from #table2 )
except
(select code from #table1 intersect select code from #table2 )
select t1.id from t1
where t1.id not in (select * from t2)
union
select t2.id from t2
where t2.id not in (select * from t1)
Comparing query cost this is faster
Select coalesce(t1.ID,t2.id) as ID
FROM t1 full outer join t2 ON t1.ID = t2.ID
where t1.ID IS NULL or t2.ID IS NULL