EXISTS condition to prevent repeated records from being inserted

Exists condition to prevent repeated records from being inserted
The subqueries introduced by exists and not exists can be used for two sets of operations: intersection and difference set. The intersection of the two sets contains all elements of the two original sets.

The difference set contains only the elements of the first set in two sets.

Mysql tutorial> create table books (
-> Bookid smallint not null primary key,
-> Booktitle varchar (60) not null,
-> Copyright year not null
->)
-> Engine = innodb;
Query OK, 0 rows affected (0.05 sec)

Mysql>
Mysql>
Mysql> insert into books values (12786, 'java', 1934 ),
-> (13331, 'mysql', 1919 ),
-> (14356, 'php ', 1966 ),
-> (15729, 'perl ', 1932 ),
-> (16284, 'oracle ', 1996 ),
-> (17695, 'pl/SQL ', 1980 ),
-> (19264, 'webpage effect ', 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,143 56 ),
-> (1008,157 29 ),
-> (1009,127 86 ),
-> (1010,176 95 ),
-> (1011,157 29 ),
-> (64, 1012,192 ),
-> (1012,193 54 ),
-> (1014,162 84 );
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 |
| 1, 1007 | j | c | o |
| 1, 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 | an |
+ -------- +
9 rows in set (0.00 sec)

Mysql> select * from books;
+ -------- + ---------------- + ----------- +
| Bookid | booktitle | copyright |
+ -------- + ---------------- + ----------- +
| 1, 12786 | java | 1934 |
| 1, 13331 | mysql | 1919 |
| 14356 | php | 1966 |
| 15729 | perl | 1932 |
| 1, 16284 | oracle | 1996 |
| 17695 | pl/SQL | 1980 |
| 1, 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 |
+ -------- + ----------- +
| 1, 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 use the exists condition to prevent repeated records from being inserted.

Example 1: insert multiple records
Suppose there is a clients table with the 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 );