Get duplicates values from values table

Create table
CREATE TABLE IF NOT EXISTS `emp1` (
`first_name` varchar(20) NOT NULL,
`last_name` varchar(20) NOT NULL
)

Insertion

INSERT INTO `emp1` (`first_name`, `last_name`) VALUES (‘roy’, ‘raj’), (‘joy’, ‘josh’);

SELECT `first_name`
FROM emp1
GROUP BY `first_name`
HAVING COUNT(*) = N

Change the HAVING condition to >=2
It outputs roy and joy are duplicates having more then one record wirh same value.

Change the HAVING condition to >1 to list non duplicate values, etc

INSERT IGNORE

Create table
CREATE TABLE IF NOT EXISTS `emp` (
`first_name` varchar(20) NOT NULL,
`last_name` varchar(20) NOT NULL,
PRIMARY KEY (`last_name`)
)

Insertion
INSERT INTO `emp` (`first_name`, `last_name`) VALUES (‘roy’, ‘raj’), (‘joy’, ‘josh’);

Useful Queries

INSERT IGNORE
If a record doesn’t have duplicate date then MySql inserts usual.
If a record have duplicate data, the IGNORE keyword instructs MySql to discard it with out generating any error message.

INSERT IGNORE INTO `emp` (`first_name`, `last_name`) VALUES (‘roy’, ‘raj’);

here insertion is failed but it won’t shows any error message.

INSERT INTO `emp` (`first_name`, `last_name`) VALUES (‘joy’, ‘raj’);

Here Mysql insertion is failed because here record is duplicate so MySql generate some error like.
#1062 – Duplicate entry ‘raj’ for key ‘PRIMARY