Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on: 30-Dec-2019
43 Views
Advertisements