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.

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

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)

, ,
Previous Post
SQL SERVER – Puzzle – Write a Shortest Code to Produce Zero
Next Post
SQL SERVER – Puzzle – Shortest Code to Produce the Number 1000000000 (One Billion)

Related Posts

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

    Reply
  • SELECT ID
    FROM (SELECT * FROM t1
    UNION ALL
    SELECT * FROM t2
    )X
    GROUP BY ID
    HAVING COUNT(*) <=1

    Reply
  • KIRANKUMAR SADHANALA
    August 22, 2018 2:58 pm

    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;

    Reply
  • Won’t a union all of t1 and t2 with an outer distinct select work?.. select distinct value from (select t1… Union all t2)??

    Reply

Leave a Reply

Menu