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)

SQL Scripts, SQL Server, SQL Union clause
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
Exit mobile version