Union of Three tables without any duplicates minus one table in Mysql

Question

Table:2018

No  Email

1   Lilly@gmail.com

2   brens@gmail.com

3   susan@gmail.com

4   resh@gmail.com

Table:2017

No   Email

1    chitta@gmail.com

2    resh@gmail.com

3    brens@gmail.com

4    minu@gmail.com

Table:2016

No   Email

1     brens@gmail.com

2     chitta@gmail.com

3     lisa@gmail.com

4     monay@gmail.com

5     many@gmail.com

Table:2019

No   Email

1    brens@gmail.com

2    chitta@gmail.com

3    rinu@gmail.com

4    emma@gmail.com

I need to perform Union of tables 2018,2017,2016 without any duplicate emails minus table 2019 ,Result should look like

RESULT

No  Email

1    Lilly@gmail.com

2    susan@gmail.com

3    resh@gmail.com

4    minu@gmail.com

5   lisa@gmail.com

6   monay@gmail.com

7   many@gmail.com

Minus operation is not available in Mysql.

select a.*from(select *from y2018 union select *from y2017 where not exists(select *from y2018 where y2018.email=y2017.email ) union select *from y2016 where not exists(select *from y2018 where y2018.email=y2016.email ))a LEFT OUTER JOIN y2019 b on a.email=b.email  where b.email is null ;

This gives the result but does not eliminate the duplicates in (2017 union 2016)

some one please help me

Possible duplicate of [Alternative to except in MySQL](https://stackoverflow.com/questions/46872015/alternative-to-except-in-mysql) — PM 77-1, Jul 09 '19 at 16:00
@RaymondNijland (2018 UNION 2017 UNION 2016) resh@gmail.com is in table 2017 — Neena Susan, Jul 09 '19 at 16:27
That still does not explain it but i got the point when i readed *"I need to perform Union of tables 2018,2017,2016 without any duplicate emails minus table 2019"* — Raymond Nijland, Jul 09 '19 at 16:31
i hope you are also aware that the No column in the expected results does not relate to your real data annymore? And needs to be generated? — Raymond Nijland, Jul 09 '19 at 16:32
@RaymondNijland yes No column needs to be generated . I am pretty new to this i tried different queries but failing — Neena Susan, Jul 09 '19 at 16:35
*"I am pretty new to this i tried different queries but failing"* Check mine answer.. — Raymond Nijland, Jul 09 '19 at 16:41

Raymond Nijland · Answer 1 · 2019-07-09T16:46:52.847

0

I need to perform Union of tables 2018,2017,2016 without any duplicate emails minus table 2019

The most easy to simulate/emulate minus/expect is using NOT IN()

Query

SELECT 
   (@ROW_NUMBER := @ROW_NUMBER + 1) AS 'No' 
 , unique_email.Email
FROM (

  SELECT 
    DISTINCT 
      years_merged.Email  
  FROM (

    SELECT 
     Email
    FROM 
     y2019
    UNION ALL
    SELECT 
     Email
    FROM 
     y2018
    UNION ALL
    SELECT 
     Email
    FROM
     y2017   
    UNION ALL
    SELECT 
     Email
    FROM 
     y2016     

  ) AS years_merged
  WHERE 
   years_merged.Email NOT IN(SELECT y2019.Email FROM y2019 ) 
  ORDER BY
   years_merged.Email ASC
) AS unique_email
CROSS JOIN (SELECT @ROW_NUMBER := 0) AS init
ORDER BY 
  @ROW_NUMBER ASC

Result

| No  | Email           |
| --- | --------------- |
| 1   | Lilly@gmail.com |
| 2   | lisa@gmail.com  |
| 3   | many@gmail.com  |
| 4   | minu@gmail.com  |
| 5   | monay@gmail.com |
| 6   | resh@gmail.com  |
| 7   | susan@gmail.com |

Yes the order is different but this is the best you can do as SQL standards defines SQL tables to be sorted orderless..

see demo

But a simulate/emulate minus/expect using ... LEFT JOIN ... ON ... WHERE .. IS NULL might optimize better vs a NOT IN()

See demo

edited Jul 09 '19 at 16:46

answered Jul 09 '19 at 16:40

Raymond Nijland

11,488
2
22
34

Thanks very much , i really appreciate your help.. Just one more thing what if another columns are present in the table,something like name and address and need to display all the fields – Neena Susan Jul 09 '19 at 16:51
*"Just one more thing what if another columns are present in the table,something like name and address and need to display all the fields"* @NeenaSusan that will involve a other writing method one of the "problems" is that MySQL does not support `DISTINCT ON(email), *` (PostgreSQL) syntax otherwise it would be very simple but you can [simulate/emulate (post of mine)](https://stackoverflow.com/questions/53868870/how-to-use-distinct-on-with-mysql-using-activerecord/53869691#53869691) it.. – Raymond Nijland Jul 09 '19 at 17:22
is there anyway to use 'group by' in the same query instead of distinct – Neena Susan Jul 09 '19 at 17:34
*"is there anyway to use 'group by' in the same query instead of distinct "* Yes but `GROUP BY` is not really designed to unduplicate and ideally you should only use `GROUP BY` when you are going to use [Aggregate (GROUP BY) Function](https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html) also in the query – Raymond Nijland Jul 09 '19 at 17:39
i understood. Could you please show me the other way – Neena Susan Jul 09 '19 at 17:43

score 0 · Accepted Answer · answered Jul 10 '19 at 14:41

I find a better method, creating a view and save a portion of the query in it. It helps to reduce the processing time as well.

create view vm as select *from y2018 union select *from y2017 where not exists(select *from y2018 where y2018.email=y2017.email);

SELECT  a.*FROM(SELECT * FROM vm union select *from y2016 where not exists(select *from vm where vm.email=y2016.email))a LEFT JOIN y2019 b ON a.email=b.email where b.email is null;

Here 'vm' is the view

Union of Three tables without any duplicates minus one table in Mysql

2 Answers2