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 coalesce(t1.Id,t2.id) as id from t1 t1
full join t2 t2 on t1.id = t2.id
where t1.id is null or t2.ID is null
SELECT ID
FROM (SELECT * FROM t1
UNION ALL
SELECT * FROM t2
)X
GROUP BY ID
HAVING COUNT(*) <=1
SELECT
ISNULL(T1.ID, T2.ID) AS ID
FROM T1
FULL JOIN T2
ON
T1.ID = T2.ID
WHERE
T1.ID IS NULL OR
T2.ID IS NULL;
Won’t a union all of t1 and t2 with an outer distinct select work?.. select distinct value from (select t1… Union all t2)??