How to remove duplicate using union all

Question

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.

Answer 1

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

Answer 2

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  |
+----+---------+--------+