SQL SERVER – Select Unique Data From a Column Exist in Two Different Table

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)