EXISTS a conditional sentence to prevent duplicate records from being inserted

exists a conditional sentence to prevent duplicate records from being inserted
Subqueries introduced by exists and not exists can be used for operations of two sets of principles: intersection and Difference sets. The intersection of two sets contains all elements that belong to two original collections at the same time.

The difference set contains elements that belong only to the first collection in two collections

MySQL Tutorials > CREATE TABLE Books (
-> BookID smallint NOT null primary key,
-> booktitle varchar () NOT NULL,
-> copyright year is not null
->)
-> Engine=innodb;
Query OK, 0 rows affected (0.05 sec)

Mysql>
Mysql>
Mysql> INSERT into the books values (12786, ' Java ', 1934),
-> (13331, ' MySQL ', 1919),
-> (14356, ' PHP Tutorial ', 1966),
-> (15729, ' Perl ', 1932),
-> (16284, ' Oracle ', 1996),
-> (17695, ' Pl/sql ', 1980),
-> (19264, ' Web Effects ', 1992),
-> (19354, ' www.java2s.com ', 1993);
Query OK, 8 rows affected (0.03 sec)
Records:8 duplicates:0 warnings:0

Mysql>
Mysql>
Mysql> CREATE TABLE authors (
-> Authid smallint NOT null primary key,
-> authfn varchar (20),
-> authmn varchar (20),
-> authln varchar (20)
->)
-> Engine=innodb;
Query OK, 0 rows affected (0.09 sec)

Mysql>
Mysql>
Mysql> INSERT into authors values (1006, ' h ', ' s. ', ' t '),
-> (1007, ' J ', ' C ', ' O '),
-> (1008, ' B ', null, ' E '),
-> (1009, ' R ', ' m ', ' R '),
-> (1010, ' J ', ' K ', ' t '),
-> (1011, ' j ', ' G. ', ' n '),
-> (1012, ' a ', null, ' P '),
-> (1013, ' a ', null, ' W '),
-> (1014, ' n ', null, ' a ');
Query OK, 9 rows affected (0.03 sec)
Records:9 duplicates:0 warnings:0

Mysql>
Mysql>
Mysql> CREATE TABLE Authorbook (
-> Authid smallint NOT NULL,
-> BookID smallint NOT NULL,
-> primary KEY (Authid, BookID),
-> foreign KEY (Authid) references authors (Authid),
-> foreign KEY (BookID) References books (BookID)
->)
-> Engine=innodb;
Query OK, 0 rows affected (0.09 sec)

Mysql>
Mysql>
mysql> INSERT into Authorbook values (1006, 14356),
-> (1008, 15729),
-> (1009, 12786),
-> (1010, 17695),
-> (1011, 15729),
-> (1012, 19264),
-> (1012, 19354),
-> (1014, 16284);
Query OK, 8 rows affected (0.03 sec)
Records:8 duplicates:0 warnings:0

Mysql>
Mysql>
Mysql> SELECT * from authors;
+--------+--------+--------+--------+
| Authid | AUTHFN | Authmn | AUTHLN |
+--------+--------+--------+--------+
| 1006 | H | S. | T |
| 1007 | J | C | o |
| 1008 | B | null | e |
| 1009 | R | m | R |
| 1010 | J | K | T |
| 1011 | J | G. | n |
| 1012 | A | null | P |
| 1013 | A | null | W |
| 1014 | n | null | A |
+--------+--------+--------+--------+
9 Rows in Set (0.00 sec)

Mysql> select * from books;
+--------+----------------+-----------+
| BookID | BookTitle | Copyright |
+--------+----------------+-----------+
| 12786 |      java | 1934 |
| 13331 |      MySQL | 1919 |
| 14356 |      php | 1966 |
| 15729 |      Perl | 1932 |
| 16284 |      Oracle | 1996 |
| 17695 |      Pl/sql | 1980 |
| 19264 |      JavaScript | 1992 |
| 19354 |      www.java2s.com | 1993 |
+--------+----------------+-----------+
8 rows in Set (0.00 sec)

Mysql> select * from Authorbook;
+--------+--------+
| Authid | BookID |
+--------+--------+
|  1009 | 12786 |
|  1006 | 14356 |
|  1008 | 15729 |
|  1011 | 15729 |
|  1014 | 16284 |
|  1010 | 17695 |
|  1012 | 19264 |
|  1012 | 19354 |
+--------+--------+
8 rows in Set (0.00 sec)

Mysql>
Mysql>
Mysql> Select BookID, BookTitle
-> from books as B
-> where NOT EXISTS
-> (
-> Select BookID
-> from Authorbook as AB
-> where B.bookid=ab.bookid
->)
-> ORDER by BookTitle;
+--------+-----------+
| BookID | BookTitle |
+--------+-----------+
| 13331 | MySQL |
+--------+-----------+
1 row in Set (0.00 sec)

Mysql>
mysql> drop table Authorbook;
Query OK, 0 rows affected (0.03 sec)

mysql> drop table books;
Query OK, 0 rows affected (0.05 sec)

mysql> drop table authors;
Query OK, 0 rows affected (0.05 sec)

You can prevent duplicate records from being inserted by using exists conditional sentences.

Example one: inserting more than one record
Suppose you have a clients table with a primary key of client_id, you can use the following statement:

Code

INSERT INTO clients
(client_id, Client_name, Client_type)
Select supplier_id, Supplier_name, ' advertising '
From suppliers
Where NOT EXISTS (SELECT * from clients
where clients.client_id = suppliers.supplier_id);