Get difference of unique and duplicate set - SQL

Question

I have two similar data sets (table, view, CTE), one of which contains unique rows (guaranteed by DISTINCT or GROUP BY), the second contains duplicates (no primary key constraint involved).

How can I get the difference of two data sets so that I only get the duplicates of the second set in MySql 8?

Say I have a table called Animals, which stores NAME and SPECIES.

 +---------+--------------+------+-----+---------+-------+
 | Field   | Type         | Null | Key | Default | Extra |
 +---------+--------------+------+-----+---------+-------+
 | ID      | int(11)      | NO   | PRI | NULL    |       |
 | NAME    | varchar(255) | YES  |     | NULL    |       |
 | SPECIES | varchar(255) | YES  |     | NULL    |       |
 +---------+--------------+------+-----+---------+-------+

 ANIMALS
 +----+---------+-------------+
 | ID | NAME    | SPECIES     |
 +----+---------+-------------+
 |  1 | Lion    | Carnivorous |
 |  2 | Giraffe | Herbivores  |
 |  3 | Zebra   | Herbivores  |
 |  4 | Trutle  | Herbivores  |
 |  5 | Tiger   | Carnivorous |
 |  6 | Bear    | Carnivorous |
 +----+---------+-------------+

With that in place, I define the view DUPLICATED.

 CREATE VIEW DUPLICATED AS
     SELECT * FROM ANIMALS
     UNION ALL
     SELECT * FROM ANIMALS WHERE SPECIES = "Carnivorous";

(Duplicates every Carnivorous in the set)

 DUPLICATED
 +---------+-------------+-----+
 | NAME    | SPECIES     | CNT |
 +---------+-------------+-----+
 | Lion    | Carnivorous |   2 |
 | Tiger   | Carnivorous |   2 |
 | Bear    | Carnivorous |   2 |
 | Giraffe | Herbivores  |   1 |
 | Zebra   | Herbivores  |   1 |
 | Trutle  | Herbivores  |   1 |
 +---------+-------------+-----+

Now I want to get the difference of SELECT * FROM ANIMALS and DUPLICATED or vice versa, essential getting all Carnivorous from ANIMALS.

Answer 1

Basically you can group by whatever combination of fields that guarantee the uniqueness of a record in your result. you haven't provided your queries or your table's schema, so i will try to demonstrate this using a general example. you can get my drift and apply it to your query.

SELECT field1, field2, field3 COUNT(*)
FROM MyTable
GROUP BY field1, field2, field3
HAVING COUNT(*) > 1