Query the database for the values not in the MySQL table?

For this, you can use UNION ALL along with WHERE NOT EXISTS and implement NOT IN to ignore the values already in the table. Use SELECT with UNION ALL to add values not already in the table.

Let us first create a table −

mysql> create table DemoTable1918    (    Value int NOT NULL AUTO_INCREMENT PRIMARY KEY    ); Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1918 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1918 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1918 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1918 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1918 values(); Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1918;

This will produce the following output −

+-------+ | Value | +-------+ |     1 | |     2 | |     3 | |     4 | |     5 | +-------+ 5 rows in set (0.00 sec)

Here is the query to SELECT values not in the table with UNION ALL −

mysql> select tbl.Value    from ( select 6 as Value union all select 7 union all select 8 ) tbl    where not exists ( select 1 from DemoTable1918 tbl1    where tbl1.Value=tbl.Value);

This will produce the following output −

+-------+ | Value | +-------+ |     6 | |     7 | |     8 | +-------+ 3 rows in set (0.00 sec)