CODEWITHSUNDEEP
sql-servert-sql
Mou
Mou

Reputation: 16292

How to remove duplicate using union all

Just reading this site https://www.toptal.com/sql/interview-questions

they show this trick to remove duplicate using union all

SELECT * 
FROM mytable 
WHERE a = X 

UNION ALL 

SELECT * 
FROM mytable 
WHERE b = Y AND a != X

The above script is not clear to me. Suppose I want to fetch data from two employee table but like to remove duplicate using union all with where clause.

Suppose my employee table has structure like ID, Name and salary.

Now post a sample SQL using union all which will show distinct rows from 2 tables.

Upvotes: 3

Views: 11249

Answers (5)

Neelima Jonnavada
Neelima Jonnavada

Reputation: 1

select  from table1 where ID not in(select ID from table1) union all select  from table1;

Upvotes: 0

Anda
Anda

Reputation: 1

UNION removes duplicates. And why not use it between my table and 'nothing' with the same structure of course.

Here is the sentence:

select id, name from mytable 

  UNION

  select id, name from mytable 
  where 1=2

Upvotes: 0

user10244176
user10244176

Reputation: 21

create table test_a(id numeric);

create table test_b(id numeric);

insert into test_a(id) values
  (10),
  (20),
  (30),
  (40),
  (50);

insert into test_b(id) values
  (10),
  (30),
  (50);
 GO 
 select * from test_b
 union all
  SELECT * FROM(select * from test_a  
  except 
  select * from test_b) ABC

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 81970

IF and ONLY IF you have to use a UNION ALL otherwise I would go with Handoko Chen's solution

Select Distinct *
 From (
        Select * From Employee1
        Union All
        Select * From Employee2
      ) A

Upvotes: 3

Han
Han

Reputation: 3072

Actually, it's UNION that removes duplicates. Let's say I have 3 rows of data in a table. Then use 2 unioned queries. See the differences between union and union all.

DECLARE @Employee TABLE
(
    ID INT,
    Name VARCHAR(100),
    Salary  INT
)

INSERT @Employee VALUES
(1, 'Alice', 50000),
(2, 'Bob', 40000),
(3, 'Charlie', 60000)

SELECT ID, Name, Salary FROM @Employee WHERE Salary >= 50000
UNION
SELECT ID, Name, Salary FROM @Employee WHERE Salary >= 60000

SELECT ID, Name, Salary FROM @Employee WHERE Salary >= 50000
UNION ALL
SELECT ID, Name, Salary FROM @Employee WHERE Salary >= 60000

Result:

+----+---------+--------+
| ID | Name    | Salary |
+----+---------+--------+
| 1  | Alice   | 50000  |
| 3  | Charlie | 60000  |
+----+---------+--------+

+----+---------+--------+
| ID | Name    | Salary |
+----+---------+--------+
| 1  | Alice   | 50000  |
| 3  | Charlie | 60000  |
| 3  | Charlie | 60000  |
+----+---------+--------+

Upvotes: 3

Related Questions